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.
     
    i.e.
     
    <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
    Hi,
     
    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,typeProduct.name,D.description,Location.name,N.DNOrPattern,DN.Display,CallingSearchSpace.name,CallingSearchSpace0.name, CallingSearchSpace1.name 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,
     
    //Dan

    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 Device.name as devicename,Device.description,TypeModel.name as typeModelName,PhoneTemplate.name as PhoneTemplateName,SoftkeyTemplate.name 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 Device.name like '%1204%'