Assistance using ExecuteSQLQuery

Version 1
    This document was generated from CDN thread

    Created by: Paulo Silva on 29-09-2011 09:58:35 AM
    Hello,
     
    Using an ExecuteSQLQuery call with the following query:
     

    "SELECT d.name, eu.telephoneNumber FROM Device d INNER JOIN EndUser eu ON eu.pkid = d.fkenduser WHERE UPPER(eu.userid) = '" + userName.ToUpper() + "'"
     
    In addition to the device name and telephone number, i would like to know if the device is a hard phone or a IP Communicator.
     
    Does anybody know how to do that?
     

    Thanks in advance,
     
    Paulo Silva

    Subject: RE: Assistance using ExecuteSQLQuery
    Replied by: Steven Lass on 29-09-2011 10:24:25 AM
    Join with the typeproduct table using device.tkproduct.
    -steve

    Subject: RE: Assistance using ExecuteSQLQuery
    Replied by: Keith Lunn on 29-09-2011 10:27:50 AM
    The "device" table field you are interested in is "device.tkProduct".
    Also device.tkclass = '1' will select only devices that are class = phones. (I include this as first in the SQL WHERE clause)
    Example code snippet showing values of device.tkproduct that you can sleect in a SQL:
    var supportedPhones = [];  //typeProduct.enum values
      supportedPhones[supportedPhones.length] = '30023';  // 7902
      supportedPhones[supportedPhones.length] = '35';     // 7960
      supportedPhones[supportedPhones.length] = '36';     // 7940
      supportedPhones[supportedPhones.length] = '30018';  // 7970
      supportedPhones[supportedPhones.length] = '30044';  // 7965
      supportedPhones[supportedPhones.length] = '30002';  // 7920
      supportedPhones[supportedPhones.length] = '20003';  // 7905
      supportedPhones[supportedPhones.length] = '30022';  // 7912
      supportedPhones[supportedPhones.length] = '30044';  // 7961
      supportedPhones[supportedPhones.length] = '303';  // 7962
      supportedPhones[supportedPhones.length] = '32';     // 7935 - conference station
      supportedPhones[supportedPhones.length] = '30050';  // 7936 - conference station
      supportedPhones[supportedPhones.length] = '330';  // 7937 - newer conference station
      supportedPhones[supportedPhones.length] = '30041';  // IP Communicator (CIPC)
      supportedPhones[supportedPhones.length] = '25';  // CTI Ports

     
    All the various enum values are found in the typeproduct table as device.tkproduct is an enum field that is a key linked to table "typeproduct".