Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query - Case Statement with Null use Max

Posted on 2014-10-13
2
Medium Priority
?
196 Views
Last Modified: 2014-10-13
I have 2 tables, "Payable" and "Payable_Line" which contains detail information.  I want a query to use max if one of the field in my query is "Null".  However, I can't get it to work correctly.

Query output result:
Payble and Payable_line
SELECT P.VOUCHER_ID, P.INVOICE_ID, P.TOTAL_AMT,
              CASE WHEN PL.PO_ID IS NULL THEN MAX(PL.PO_ID) ELSE PL.PO_ID END PO_ID
FROM PAYABLE P LEFT OUTER JOIN PAYABLE_LINE PL
              ON P.VOUCHER_ID = PL.VOUCHER_ID
GROUP BY P.VOUCHER_ID, P.INVOICE_ID, P.TOTAL_AMT, PL.PO_ID

Am I doing something wrong?  Basically wanting to input my max value in the "Null" field for PO_ID.
0
Comment
Question by:holemania
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40377557
If you want both rows returned, then try an analytic instead of aggregate

SELECT p.voucher_id,
       p.invoice_id,
       p.total_amt,
       CASE
           WHEN pl.po_id IS NULL
           THEN
               MAX(pl.po_id) OVER (PARTITION BY p.voucher_id, p.invoice_id, p.total_amt)
           ELSE
               pl.po_id
       END
           po_id
  FROM payable p LEFT OUTER JOIN payable_line pl ON p.voucher_id = pl.voucher_id
0
 

Author Closing Comment

by:holemania
ID: 40377588
Awesome.  That's what I was looking for.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question