Topic
1 reply Latest Post - ‏2012-10-20T21:53:25Z by SystemAdmin
SystemAdmin
SystemAdmin
1143 Posts
ACCEPTED ANSWER

Pinned topic Nested Select Nested From - specified table is not in the database error

‏2012-10-18T19:03:44Z |
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
(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
from table(multiset
select enduser.pkid,endusernumplanmap.tkdnusage,numplan.dnorpattern,enduser.firstname,enduser.lastname,numplan.alertingname
from devicenumplanmap
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.
Updated on 2012-10-20T21:53:25Z at 2012-10-20T21:53:25Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1143 Posts
    ACCEPTED ANSWER

    Re: Nested Select Nested From - specified table is not in the database error

    ‏2012-10-20T21:53:25Z  in response to SystemAdmin
    Hi,

    you might try the CASE expression instead of subqueries in the projection list.

    Here is an example:

    select
    tsource.customer_num,
    CASE tsource.call_code WHEN 'D' THEN tsource.lname END primary_name,
    CASE tsource.call_code WHEN 'I' THEN tsource.lname END agent_name,
    tsource.call_code

    from table (multiset
    (select
    a.customer_num, a.fname, a.lname,
    b.call_dtime, b.call_code
    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.

    Kind regards,

    Marion