Administration XML Developer Forums

« Back to Administration XML Questions

query device name and devicepool name

Combination View Flat View Tree View
Threads [ Previous | Next ]
query device name and devicepool name
Answer
8/18/11 6:59 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
Flag

RE: query device name and devicepool name
Answer
8/18/11 7:15 PM as a reply to winston chu.
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
Flag

RE: query device name and devicepool name
Answer
8/18/11 7:17 PM as a reply to Steven Lass.
Why yes it does. Such a simple solution. Thanks a lot!

Winston
Flag

Re: New Message from winston chu in Administration XML (AXL) - Administrati
Answer
8/18/11 10:41 PM as a reply to winston chu.
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
Flag