Home » SQL & PL/SQL » SQL & PL/SQL » Case Statement
Case Statement [message #197247] Tue, 10 October 2006 14:02 Go to next message
vbattle
Messages: 6
Registered: October 2006
Junior Member
I have two case statements in a select statement. I am trying to eliminate the records that have an ENCUM_TYPE of 'N/A'. How would I modify my query to eliminate those records?

See the query below:

SELECT DISTINCT ack.check_number check_number,
                ack.check_date check_date,
                RPAD (NVL (aia.invoice_num, ' '), 25, ' ') invoice_number,
                RPAD (NVL (gcck.concatenated_segments, ' '), 26, ' ')
                                                               account_string,
                (CASE
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') > :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') > :audityr
                     THEN 'N/A'
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') <= :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') <= :audityr
                     THEN 'Obligation'
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') > :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') <= :audityr
                     THEN 'Commitment'
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') <= :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') IS NULL
                     THEN 'Obligation'
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') > :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') IS NULL
                     THEN 'N/A'
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') IS NULL
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') <= :audityr
                     THEN 'Commitment'
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') IS NULL
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') > :audityr
                     THEN 'N/A'
                 END
                ) encum_type,
                (CASE
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') > :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') > :audityr
                     THEN 'N/A'
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') <= :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') <= :audityr
                     THEN RPAD (NVL (pha.segment1, ' '), 10, ' ')
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') > :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') <= :audityr
                     THEN prha.segment1
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') <= :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') IS NULL
                     THEN RPAD (NVL (pha.segment1, ' '), 10, ' ')
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') > :audityr
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') IS NULL
                     THEN 'N/A'
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') IS NULL
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') <= :audityr
                     THEN prha.segment1
                   WHEN TO_CHAR (pda.gl_encumbered_date, 'YYYY') IS NULL
                 AND   TO_CHAR (prda.gl_encumbered_date, 'YYYY') > :audityr
                     THEN 'N/A'
                 END
                ) encum_num,
                RPAD (NVL (pha.segment1, ' '), 10, ' ') po_number,
                pda.gl_encumbered_date po_encumbered_date,
                prha.segment1 req_number,
                prda.gl_encumbered_date req_encumbered_date,
                LPAD (NVL (TO_CHAR (aia.invoice_amount), ' '), 15, ' ')
                                                         invoice_total_amount,
                RPAD (NVL (TO_CHAR (aia.invoice_date, 'DD-MON-YYYY'), ' '),
                      11,
                      ' '
                     ) invoice_date,
                aia.invoice_id invoice_id
FROM            po_requisition_headers_all prha,
                po_requisition_lines_all prla,
                po_req_distributions_all prda,
                po_line_locations_all plla,
                po_lines_all pla,
                po_headers_all pha,
                po_distributions_all pda,
                ap_invoice_distributions_all aid,
                ap_invoices_all aia,
                gl_code_combinations_kfv gcck,
                ap_invoice_payments_v ack
WHERE           aia.invoice_type_lookup_code = 'STANDARD'
AND             aia.amount_paid = aia.invoice_amount
AND             prla.requisition_header_id = prha.requisition_header_id
AND             prla.requisition_line_id = prda.requisition_line_id
AND             plla.line_location_id = prla.line_location_id
AND             pla.po_line_id = plla.po_line_id
AND             pha.po_header_id = pla.po_header_id
AND             plla.line_location_id = pda.line_location_id
AND             aid.po_distribution_id = pda.po_distribution_id
AND             aia.invoice_id = aid.invoice_id
AND             aia.invoice_id = ack.invoice_id
AND             aia.cancelled_amount IS NULL
AND             gcck.code_combination_id = aid.dist_code_combination_id
AND             check_date BETWEEN '01-JAN-06' AND '31-JAN-06'


[mod-ed]applied code tags.

[Updated on: Wed, 11 October 2006 00:35] by Moderator

Report message to a moderator

Re: Case Statement [message #197257 is a reply to message #197247] Tue, 10 October 2006 14:56 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, I didn't read the whole query - I lost my sight viewing it. However, saying that you'd like to "eliminate the records that have an ENCUM_TYPE of 'N/A'", why don't you simply add this condition into the WHERE clause?

WHERE ecnum_type <> 'N/A'
AND ...
Re: Case Statement [message #197258 is a reply to message #197257] Tue, 10 October 2006 15:02 Go to previous messageGo to next message
vbattle
Messages: 6
Registered: October 2006
Junior Member
I did try that, but it didn't recognize encum_type as a valid identifier.

I managed to get the query to work by adding the following:

and ((to_char(pda.gl_encumbered_date, 'YYYY') <= :Audityr and to_char(prda.gl_encumbered_date, 'YYYY') <= :audityr) OR
(to_char(pda.gl_encumbered_date, 'YYYY') > :Audityr and to_char(prda.gl_encumbered_date, 'YYYY') <= :audityr) OR
(to_char(pda.gl_encumbered_date, 'YYYY') <= :Audityr and to_char(prda.gl_encumbered_date, 'YYYY') is null) OR
(to_char(pda.gl_encumbered_date, 'YYYY') is null and to_char(prda.gl_encumbered_date, 'YYYY') <= :audityr ))


However, I was looking for something more efficient.
Re: Case Statement [message #197261 is a reply to message #197258] Tue, 10 October 2006 15:22 Go to previous message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you say so ...

I'm going to bed, but can't figure out what does "gl_encumbered_date" have to do with "ecnum_type <> 'N/A'". Never mind me.
Previous Topic: Hierarchical Data
Next Topic: Decode problem
Goto Forum:
  


Current Time: Mon Dec 05 12:39:06 CST 2016

Total time taken to generate the page: 0.05251 seconds