Solved

SQL Query - Case Statement with Null use Max

Posted on 2014-10-13
2
182 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 73

Accepted Solution

by:
sdstuber earned 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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