Column (d) not found in any table in the query

Helen Pippard · #1 (**permalink**) 12-09-11, 08:09

Hello,

I am new to informix and require some help to write a query with an inline view

When I write following syntex I get the following error message:
Column (d) not found in any table in the query

Is anytone able to help me fix this problem, and then where would I put another inline view?

Select a.clrec_identity,
and a.clmov_movedate =
(Select MAX(clmov_movedate) pen_date
FROM clinrec_movements d
WHERE d.clmov_movedate < a.clmov_movedate
and d.clrec_identity = 1 )

Any advice?

Thanks
Helen

r937 · #2 (**permalink**) 12-09-11, 08:36

Code:

SELECT a.clrec_identity
     , a.clmov_movedate
     , a.clmov_loctype
     , a.clmov_loccode
     , a.clmov_type
     , c.pmi_link
     , ( SELECT MAX(clmov_movedate) 
           FROM clinrec_movements 
          WHERE clmov_movedate < a.clmov_movedate
            AND clrec_identity = 1 ) pen_date
  FROM clinrec_movements a
INNER
  JOIN clinrec_movenumber b
    ON b.clrec_identity = a.clrec_identity
   AND b.clmov_index    = a.clmov_index
INNER
  JOIN clinical_records c
    ON c.clrec_identity = a.clrec_identity
 WHERE a.clrec_identity = 1

Helen Pippard · #3 (**permalink**) 12-09-11, 08:42

Hello

Thank you very much for your response. I really appreciate it

I have tried the code I and I am getting a syntex error, and cannot see why

Also whilst you are still there, If I were to add another sub query would I put a comma in the following place:

SELECT a.clrec_identity
, , ( SELECT MAX(clmov_movedate)
FROM clinrec_movements
WHERE clmov_movedate < a.clmov_movedate
AND clrec_identity = 100027 ) pen_date
, ( SELECT.........................................) MAXOf

Thanks in advance for any further advice