SQL Query Help (If Selecting??)

[jsabo]

Hey guys,

brand new to SQL and have a question. In the select statement I wanted to select either PO_NOTTOEXCEED_VALUE if the PO_SUBTYPE is 'BO' or just select PCOM_VALUE_THISREV for all other PO_SUBTYPEs. You can see my SQL below:


SELECT PO_HEADERS.PO_SEQNO,
PO_HEADERS.PO_NUMBER,
PO_AWARD,
POCL_VTYV_VALUE,
(IF PO_SUBTYPE = 'BO'
THEN PO_NOTTOEXCEED_VALUE, PCOM_VALUE_THISREV),
PCOM_NEGOTIATED_SAVING,
PCOM_REVNO
FROM PO_CLASSES, PO_HEADERS, PROJECT_COMMITMENTS
WHERE POCL_PO_SEQNO = PO_HEADERS.PO_SEQNO
AND PCOM_DATASEQNO = PO_HEADERS.PO_SEQNO
AND POCL_VTYV_VALUE = 'OBE'
AND PO_AWARD >= ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -12)
AND PO_AWARD < ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1)
AND PCOM_REVNO = 0
AND PO_SUBTYPE <> 'REL'
GROUP BY PO_HEADERS.PO_SEQNO,
PO_HEADERS.PO_NUMBER,
PO_RELEASE_NUMBER,
PO_AWARD,
POCL_VTYV_VALUE,
PCOM_VALUE_THISREV,
PCOM_NEGOTIATED_SAVING,
PCOM_REVNO;

The select statement is where my concern lies. Any ideas? Thanks.

[Kyle234]

Which flavour of SQL is this? MSSQL, Access, Oracle, MySQL etc

EDIT: Actually I thing that TRUNC is Oracle, I'll have a look

[Kyle234]

I think you just need to use a Case statement properly, something like:



SELECT po_headers.po_seqno,
po_headers.po_number,
po_award,
pocl_vtyv_value,
CASE
WHEN po_subtype = 'BO' THEN po_nottoexceed_value
ELSE pcom_value_thisrev
END,
pcom_negotiated_saving,
pcom_revno
FROM po_classes
inner join po_headers
ON pcom_dataseqno = po_headers.po_seqno
inner join project_commitments
ON pocl_po_seqno = po_headers.po_seqno
WHERE pocl_vtyv_value = 'OBE'
AND po_award >= Add_months (Trunc (SYSDATE, 'MM'), -12)
AND po_award < Add_months (Trunc (SYSDATE, 'MM'), 1)
AND pcom_revno = 0
AND po_subtype <> 'REL'
GROUP BY po_headers.po_seqno,
po_headers.po_number,
po_release_number,
po_award,
pocl_vtyv_value,
pcom_value_thisrev,
pcom_negotiated_saving,
pcom_revno;

Also, note the Join syntax, it's generally better to write SQL like that rather than joins in the WHERE clause (I had to guess which fields belonged to which tables)

This code formatting is horrendous - I've pasted here: http://pastebin.com/FZRVDgFc