Problem with EXISTS clause with subquery

[asplus]

Please see the following SQL:

SELECT CASE WHEN EXISTS (select * from cohead, aropen where cohead_type = 'C' and aropen_ordernumber = cohead_number and aropen_doctype = 'I') THEN 'R'
ELSE 'N'
END AS type
FROM aropen
WHERE ( (aropen_cust_id=<? value("cust_id") ?>)
AND (aropen_open)
AND ((aropen_amount - aropen_paid) > 0) )
ORDER BY aropen_docdate;



I get 'R' in type all the time. I should get some 'N' and 'R' results, not all 'R's.



Thanks,



--
Bernard Le Jour
AS Plus Informatique Inc.

 

[feherke]

Hi

Due to your style of not prefixing field names with table names, is hard to understand what is written there. But to me seems there is no relation between the inner [tt]select[/tt] and the outer one. So the inner [tt]select[/tt] would return the same complete tuple list every time.

Feherke.

http://rootshell.be/~feherke/