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

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

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