query device name and devicepool name

Version 1

    Subject: RE: query device name and devicepool name
    Replied by: Steven Lass on 18-08-2011 02:15:09 PM
    Does the SQL statement work correctly if you run it from the CLI, ie:

    admin:run sql select devicepool.name,device.name from numplan,devicenumplanmap,devicepool,device where devicenumplanmap.fkdevice=device.pkid and devicenumplanmap.fknumplan=numplan.pkid and numplan.dnorpattern='48269' and device.fkdevicepool=devicepool.pkid

    Does it work correctly if you rename the columns?

    select devicepool.name as dp_name, device_name as dev_name ...

    -steve
    This document was generated from CDN thread

    Created by: winston chu on 18-08-2011 01:58:09 PM
    I'm attempting to query a devie name and devicepool name based on the dn number. Since I have device.name last in the list, all the name field in the result are the device.name, and devicepool.name does not come back in the result at all.  CUCM version is 8.5, and this worked in 6.x.
     
    select devicepool.name,device.name
    from numplan,devicenumplanmap,devicepool,device
    where devicenumplanmap.fkdevice=device.pkid
    and devicenumplanmap.fknumplan=numplan.pkid
    and numplan.dnorpattern='48269'
    and device.fkdevicepool=devicepool.pkid
     
     
     
    RESULT
     
    <return><row><description>Winston Chu</description><name>SEPE80462EA46A
    2</name><name>SEPE80462EA46A2</name><name>SEPE80462EA46A2</name></row></return>
     
    One of the <name> tags should be the device pool.  If I change it to "select device.name, devicepool.name" then both name tags are the devicepool.
     
    Thanks for any input,
    Winston

    Subject: RE: query device name and devicepool name
    Replied by: winston chu on 18-08-2011 02:17:00 PM
    Why yes it does.  Such a simple solution.  Thanks a lot!

    Winston

    Subject: Re: New Message from winston chu in Administration XML (AXL) - Administrati
    Replied by: Sascha Monteiro on 18-08-2011 05:41:46 PM
    try setting an alias for the column name like this;

    select devicepool.name as devicepoolname,device.name as devicename
    from numplan,devicenumplanmap,devicepool,device
    where devicenumplanmap.fkdevice=device.pkid
    and devicenumplanmap..fknumplan=numplan.pkid
    and numplan.dnorpattern='48269'
    and device.fkdevicepool=devicepool.pkid