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

    Unanswered: How do I reference a subquery again?

    Hello

    Is anyone able to help me with regards to referencing a subquery again?

    My syntex is:

    SELECT a.clrec_identity
    , a.clmov_movedate
    , (SELECT MAX(clmov_movedate)
    FROM clinrec_movements
    WHERE clmov_type In ('BO')
    AND clmov_movedate < a.clmov_movedate
    AND clrec_identity = ABC) Last_Date_Booked_Out
    FROM clinrec_movements a
    WHERE a.clrec_identity = ABC

    Now I want to count how many clmov_movedates there are between
    a.clmov_movedate and Last_Date_Booked_Out but am unsure how I can reference a subquery again? Would it be in the where clause?

    I.e. WHERE a.clrec_identity = ABC
    and a.clmov_movedate =
    (select COUNT(clmov_movedate)
    FROM clrec_movements
    WHERE clrec_movedate BETWEEN a.clmov_movedate and Last_Date_Booked_Out) tracking_numbers?

    Any help is greatly appreciated

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT *
      FROM ( SELECT a.clrec_identity
                  , a.clmov_movedate
                  , ( SELECT MAX(clmov_movedate)
                        FROM clinrec_movements
                       WHERE clmov_type In ('BO')
                         AND clmov_movedate < a.clmov_movedate
                         AND clrec_identity = ABC ) Last_Date_Booked_Out 
               FROM clinrec_movements a
              WHERE a.clrec_identity = ABC ) x
     WHERE clmov_movedate =
           ( SELECT COUNT(clmov_movedate) 
               FROM clrec_movements
              WHERE clrec_movedate BETWEEN clmov_movedate 
                                       AND Last_Date_Booked_Out )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2011
    Posts
    82
    Hello

    Thanks once again for your help

    I have run the syntex and although it does not error, it does not show any values in the results.

    I am getting the following error: Could not do a physical-order read to fetch next row. sqlerrm(clinrec_movements)




    Do you know how I would also get the Count part to show as a column in the results also, as I am unsure where to name the count part of the query


    Thanks
    Helen

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi Helen,


    you are probably hitting against a row or page lock.
    To check this, run onstat -k and , if you know how to read it, try to identify
    where the lock is.

    Easier even: dbaccess sysmaster
    select * from syslocks where tabname = "your table"

    You should understand what is happening then

    Hope this helps

    Eric

Posting Permissions

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