Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    82

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

    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
    Last edited by Helen Pippard; 12-09-11 at 11:02.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2011
    Posts
    82
    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
    Last edited by Helen Pippard; 12-09-11 at 11:02.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Helen Pippard View Post
    I have tried the code I and I am getting a syntex error, and cannot see why
    neither can i

    my version of Microsoft® CrystalBall© is down at the moment

    Quote Originally Posted by Helen Pippard View Post
    Also whilst you are still there, If I were to add another sub query would I put a comma in the following place:
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2011
    Posts
    82
    Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •