Hello All, I'm new to Informix (less than a day old) but not so new to SQL. I'm having trouble translating my existing SQL knowledge. I need to write a query which has a nested SELECT, and a nested FROM. It's against a cisco callmanager 8 database (IBM Informix Dynamic Server Version 11.50.UC5XA) so any additional hints are more than welcome.
This is what I have so far:
(select T1.alertingname from tsource T1 where T1.pkid=tsource.pkid and T1.tkdnusage='1') primaryalertingname,
(select T1.alertingname from tsource T1 where T1.pkid=tsource.pkid and T1.tkdnusage='2') agentalertingname
inner join device on Device.pkid = devicenumplanmap.fkdevice
inner join numplan on Numplan.pkid = devicenumplanmap.fknumplan
inner join endusernumplanmap on endusernumplanmap.fknumplan = devicenumplanmap.fknumplan
inner join enduser on enduser.pkid = endusernumplanmap.fkenduser
where endusernumplanmap.tkdnusage in('1','2') order by enduser.lastname, enduser.firstname)) tsource
I don't get any syntax error, but I get "The specified table (tsource) is not in the database." If I remove the nested select and simply do select alertingname, I get results.
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
1 reply Latest Post - 2012-10-20T21:53:25Z by SystemAdmin
Pinned topic Nested Select Nested From - specified table is not in the database error
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-10-20T21:53:25Z at 2012-10-20T21:53:25Z by SystemAdmin
SystemAdmin 110000D4XK1143 PostsACCEPTED ANSWER
Re: Nested Select Nested From - specified table is not in the database error2012-10-20T21:53:25Z in response to SystemAdminHi,
you might try the CASE expression instead of subqueries in the projection list.
Here is an example:
CASE tsource.call_code WHEN 'D' THEN tsource.lname END primary_name,
CASE tsource.call_code WHEN 'I' THEN tsource.lname END agent_name,
from table (multiset
a.customer_num, a.fname, a.lname,
from customer a, cust_calls b
where a.customer_num = b.customer_num
and b.call_code in ('D','I'))) tsource
I don't know if your query should work. Did not find a clear statement in the documentation.