RE: Axl query - Multiple "GetDeviceProfile" request

Version 1
    This document was generated from CDN thread

    Created by: David Staudt on 08-03-2010 07:00:22 PM
    This is a situation where <executeSQLQuery> comes in handy.
    You can use the MS SQL Enterpries Manager on the UCM console to inspect the schema and contents of the CallManager database to build your query.  I believe the tables you'll want to look at will be: device, typeclass - possibly joining others as needed depending on what fields you want returned.
    <SQL>select name, description [other fields] from device where tkclass=254 and name like '1001*</SQL>
    tkclass=254 indicates the device is a device profile (see the TypeClass table.)

    Subject: RE: Axl query - Multiple "GetDeviceProfile" request
    Replied by: Dan-Anders Hook on 10-03-2010 06:16:08 PM
    I think you need to use "AS" to separate the equally named fields. Here is a sample query which worked for me (if my memory serves me correctly)
    Select D.Name,,D.description,,N.DNOrPattern,DN.Display,,, from device D,Numplan N, DeviceNumPlanMap DN,typeProduct,Location,CallingSearchSpace,CallingSearchSpace As CallingSearchSpace0,CallingSearchSpace As CallingSearchSpace1 WHERE (((D.tkProduct IN (Select enum from TypeProduct where Name LIKE 'Cisco 7940%' ESCAPE '\')))  )  and (tkProduct NOT IN (21,22) AND tkClass = 1) And D.PKID = DN.fkDevice And DN.fkNumplan = N.PKID  And  D.tkProduct *= typeProduct.enum And D.fkLocation *= Location.pkid And N.fkCallingSearchSpace_SharedLineAppear *= CallingSearchSpace.pkid And  N.fkCallingSearchSpace_CFA *= CallingSearchSpace0.pkid And  N.fkCallingSearchSpace_CFNA *= CallingSearchSpace1.pkid ORDER BY D.Name;
    Kind regards,

    Subject: RE: Axl query - Multiple "GetDeviceProfile" request
    Replied by: David Staudt on 10-03-2010 08:47:43 PM
    Right...use 'as' to alias each field that could be a duplicate.  I believe this is a limitation of the jdbc drive being used by AXL.  I.e.:

    SELECT as devicename,Device.description, as typeModelName, as PhoneTemplateName, as softeyTemplateName FROM Device
    join PhoneTemplate on Device.fkPhoneTemplate = PhoneTemplate.pkid
    join SoftkeyTemplate on Device.fkSoftkeyTemplate = SoftkeyTemplate.pkid
    join TypeModel on Device.tkModel = TypeModel.Enum
    where Device.tkClass = '254'  and like '%1204%'