Thread: BUG #3967: EXISTS clause on subquery

The following bug has been logged online:

Bug reference:      3967
Logged by:          Bernard Le Jour
Email address:      blejour@gmail.com
PostgreSQL version: 8.2.3
Operating system:   i386-apple-darwin8.9.1
Description:        EXISTS clause on subquery
Details:

Please see the following SQL:

SELECT CASE WHEN (aropen_doctype='I') THEN 'Invc.'
            WHEN (aropen_doctype='D') THEN 'D/M'
            WHEN (aropen_doctype='C') THEN 'C/M'
            WHEN (aropen_doctype='R') THEN 'C/D'
            ELSE 'Misc.'
       END AS doctype,
       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,
       AROPEN_PONUMBER,
       aropen_docnumber, formatDate(aropen_docdate) AS f_docdate,
       CASE WHEN (aropen_doctype='I') THEN formatDate(aropen_duedate)
            ELSE ''
       END AS f_duedate,
       CASE WHEN (aropen_doctype IN ('I', 'D')) THEN
formatMoney(aropen_amount)
            WHEN (aropen_doctype IN ('C', 'R')) THEN
formatMoney(aropen_amount * -1)
            ELSE formatMoney(aropen_amount)
       END AS f_amount,
       CASE WHEN (aropen_doctype IN ('I', 'D')) THEN
formatMoney(aropen_paid)
            WHEN (aropen_doctype IN ('C', 'R')) THEN formatMoney(aropen_paid
* -1)
            ELSE formatMoney(aropen_paid)
       END AS f_applied,
       CASE WHEN (aropen_doctype IN ('I', 'D')) THEN
formatMoney(aropen_amount - aropen_paid)
            WHEN (aropen_doctype IN ('C', 'R')) THEN
formatMoney((aropen_amount - aropen_paid) * -1)
            ELSE formatMoney(aropen_amount - aropen_paid)
       END AS f_balance,
       CASE WHEN (aropen_doctype IN ('I', 'D')) THEN (aropen_amount -
aropen_paid)
            WHEN (aropen_doctype IN ('C', 'R')) THEN ((aropen_amount -
aropen_paid) * -1)
            ELSE (aropen_amount - aropen_paid)
       END AS balance
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.