The following bug has been logged online: Bug reference: 3967 Logged by: Bernard Le Jour Email address: [EMAIL PROTECTED] 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. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match