SQL Query for getting Directory Number

AR7 · ‎08-22-2018

I am working on a SQL query to list the device name, description, model, class type ,product, pool name and the associated DN if any using the following query:

select a.name as device, a.description, b.name as pool, typemodel.name as model,p.name as product,
         c.name as class,n.dnorpattern as DN from device as a,numplan as n left join devicepool 
         as b on a.fkdevicepool = b.pkid inner join typemodel on a.tkmodel=typemodel.enum inner join 
         typeproduct as p on p.tkmodel = a.tkmodel inner join typeclass as c on c.enum = a.tkclass inner join
         devicenumplanmap as dnpm on dnpm.fkdevice = a.pkid where c.name="Phone"
         and dnpm.fknumplan = n.pkid and a.tkclass = 1

But i am not able to get the result. It's showing the error

An ON clause has an invalid table reference

dcburleigh · ‎09-14-2018

The problem is that you put the'numplan' table in the 'from' clause; maybe this is specific to Informix.

You have to move the numplan join to a separate clause, and the devicenumplan clause

select a.name as device, a.description, b.name as pool, typemodel.name as model,p.name as product,
         c.name as class,n.dnorpattern as DN
         from device as a

         left join devicepool as b
             on a.fkdevicepool = b.pkid

         inner join typemodel
            on a.tkmodel=typemodel.enum

         inner join typeproduct as p
             on p.tkmodel = a.tkmodel

         inner join typeclass as c
            on c.enum = a.tkclass

         inner join devicenumplanmap as dnpm
            on dnpm.fkdevice = a.pkid

         join numplan as n
            on dnpm.fknumplan = n.pkid

        where c.name="Phone"
         and a.tkclass = 1