Trying to get Null values while also using MAX

  • I'm trying to write a view that pulls a column with Null values, but also uses the MAX function to get the most recent LastReceiveDate. I've tried using IsNull with the MAX, and it doesn't give me any errors, but it also doesn't work. I've done a tons of reading on the topic. But either the posts are way over my head, or they haven't been helpful. Could someone take a look at this code and let me know if you think it should work or not?

    WHERE (dbo.PurchaseOrders.ShipToLocation_ID = 24) AND (dbo.PODetails.LastReceiveDate =

    (SELECT MAX(ISNULL (LastReceiveDate, '1900-01-01 00:00:00.000')) AS expr1

    FROM dbo.PODetails AS PODetails_1

    WHERE (PO_ID = dbo.PurchaseOrders.PO_ID)))

    Thanks,

    Michelle

  • Lavery (7/8/2014)


    I'm trying to write a view that pulls a column with Null values, but also uses the MAX function to get the most recent LastReceiveDate. I've tried using IsNull with the MAX, and it doesn't give me any errors, but it also doesn't work. I've done a tons of reading on the topic. But either the posts are way over my head, or they haven't been helpful. Could someone take a look at this code and let me know if you think it should work or not?

    WHERE (dbo.PurchaseOrders.ShipToLocation_ID = 24) AND (dbo.PODetails.LastReceiveDate =

    (SELECT MAX(ISNULL (LastReceiveDate, '1900-01-01 00:00:00.000')) AS expr1

    FROM dbo.PODetails AS PODetails_1

    WHERE (PO_ID = dbo.PurchaseOrders.PO_ID)))

    Thanks,

    Michelle

    Hi and welcome to the forums. In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Double posted request for assistance. You already have this posted in a SQL 7/2000 forum (where, by the way I asked what version of SQL Server you are actually using and this post answers, I think, that question).

    Please post replies here, http://www.sqlservercentral.com/Forums/Topic1590138-169-1.aspx#bm1590363, as we are already using that thread to assist.

    Please do not double post questions as it fragments any responses you may get and frustrate those trying to assist.

  • Sorry, for the reposted. I didn't notice I was in SQL 2000 when I posted. I'm on SQL 2008 R2. I don't have a CREATE statement. I'm writing a view in a test environment and all I have is the following:

    SELECT

    dbo.PurchaseOrders.DateC AS PO_Date, dbo.PurchaseOrders.PO_Number, dbo.Vendors.VendorName, dbo.PODetails.LastReceiveDate,

    dbo.PurchaseOrders.POTotal, dbo.PurchaseOrders.ReceivedTotal, dbo.Bills.InvoiceTotal, dbo.Bills.InvoiceNum, dbo.Bills.DateC AS Inv_Date, dbo.PODetails.AccCode,

    dbo.PurchaseOrderStatus.POStatusDesc

    FROM dbo.PurchaseOrders LEFT OUTER JOIN

    dbo.PODetails ON dbo.PurchaseOrders.PO_ID = dbo.PODetails.PO_ID INNER JOIN

    dbo.Vendors ON dbo.PurchaseOrders.Vendor_ID = dbo.Vendors.Vendor_ID INNER JOIN

    dbo.PurchaseOrderStatus ON dbo.PurchaseOrders.POStatus_ID = dbo.PurchaseOrderStatus.POStatus_ID INNER JOIN

    dbo.Locations ON dbo.PurchaseOrders.ShipToLocation_ID = dbo.Locations.Location_ID LEFT OUTER JOIN

    dbo.Bills ON dbo.PurchaseOrders.PO_ID = dbo.Bills.PO_ID

    WHERE (dbo.PurchaseOrders.ShipToLocation_ID = 24) AND (dbo.PODetails.LastReceiveDate =

    (SELECT MAX(ISNULL (LastReceiveDate, '1900-01-01 00:00:00.000')) AS expr1

    FROM dbo.PODetails AS PODetails_1

    WHERE (PO_ID = dbo.PurchaseOrders.PO_ID)))

    ORDER BY dbo.PurchaseOrders.PO_Number

    Hope that helps you understand what I'm trying to do. thanks.

  • I reformatted your code and eliminated the 3 part naming in the select list, ON, WHERE, and ORDER BY clauses. You should avoid this as it is deprecated and my be removed from a future version of SQL Server. You should use 2 part naming conventions and table aliases n the FROM clause.

    SELECT

    po.DateC AS PO_Date,

    po.PO_Number,

    ven.VendorName,

    pod.LastReceiveDate,

    po.POTotal,

    po.ReceivedTotal,

    bil.InvoiceTotal,

    bil.InvoiceNum,

    bil.DateC AS Inv_Date,

    pod.AccCode,

    pos.POStatusDesc

    FROM

    dbo.PurchaseOrders po

    LEFT OUTER JOIN dbo.PODetails pod

    ON po.PO_ID = pod.PO_ID

    INNER JOIN dbo.Vendors ven

    ON po.Vendor_ID = ven.Vendor_ID

    INNER JOIN dbo.PurchaseOrderStatus pos

    ON po.POStatus_ID = pos.POStatus_ID

    INNER JOIN dbo.Locations loc

    ON po.ShipToLocation_ID = loc.Location_ID

    LEFT OUTER JOIN dbo.Bills bil

    ON po.PO_ID = bil.PO_ID

    WHERE

    (po.ShipToLocation_ID = 24) AND

    (pod.LastReceiveDate = (SELECT

    MAX(ISNULL (pod1.LastReceiveDate, '1900-01-01 00:00:00.000')) AS expr1

    FROM

    dbo.PODetails AS pod1

    WHERE

    (pod1.PO_ID = po.PO_ID)))

    ORDER BY

    po.PO_Number;

    Unfortunately, I really can't do much more without the DDL (CREATE TABLE statement) for the tables involved, some sample data that is representative of your problem domain, and the expected results based on the sample data.

    We ask for this since we can't see what you see.

    Both Sean and I have the same article in our signature blocks that will help you post the information we need as long as you take the time to read it and follow the instructions it provides.

  • I might be wrong, but this is a shot in the dark. Aggregate functions (including MAX) exclude null values. If you're using MAX() and replacing it with '1900-01-01' the most probable option is that you'll get the value only when all columns are null. Even then, your subquery will result on an NULL value if you don't have any rows that comply with the WHERE clause. That's why, I assume that you need to change the ISNULL function to surround the subquery. Something like this:

    SELECT

    po.DateC AS PO_Date,

    po.PO_Number,

    ven.VendorName,

    pod.LastReceiveDate,

    po.POTotal,

    po.ReceivedTotal,

    bil.InvoiceTotal,

    bil.InvoiceNum,

    bil.DateC AS Inv_Date,

    pod.AccCode,

    pos.POStatusDesc

    FROM dbo.PurchaseOrders po

    LEFT OUTER JOIN dbo.PODetails pod ON po.PO_ID = pod.PO_ID

    INNER JOIN dbo.Vendors ven ON po.Vendor_ID = ven.Vendor_ID

    INNER JOIN dbo.PurchaseOrderStatus pos ON po.POStatus_ID = pos.POStatus_ID

    INNER JOIN dbo.Locations loc ON po.ShipToLocation_ID = loc.Location_ID

    LEFT OUTER JOIN dbo.Bills bil ON po.PO_ID = bil.PO_ID

    WHERE po.ShipToLocation_ID = 24

    AND pod.LastReceiveDate = ISNULL ((SELECT MAX(pod1.LastReceiveDate) AS expr1

    FROM dbo.PODetails AS pod1

    WHERE pod1.PO_ID = po.PO_ID), '1900-01-01 00:00:00.000')

    ORDER BY

    po.PO_Number;

    I hope the explanation makes sense.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (7/8/2014)


    I might be wrong, but this is a shot in the dark. Aggregate functions (including MAX) exclude null values. If you're using MAX() and replacing it with '1900-01-01' the most probable option is that you'll get the value only when all columns are null. Even then, your subquery will result on an NULL value if you don't have any rows that comply with the WHERE clause. That's why, I assume that you need to change the ISNULL function to surround the subquery. Something like this:

    SELECT

    po.DateC AS PO_Date,

    po.PO_Number,

    ven.VendorName,

    pod.LastReceiveDate,

    po.POTotal,

    po.ReceivedTotal,

    bil.InvoiceTotal,

    bil.InvoiceNum,

    bil.DateC AS Inv_Date,

    pod.AccCode,

    pos.POStatusDesc

    FROM dbo.PurchaseOrders po

    LEFT OUTER JOIN dbo.PODetails pod ON po.PO_ID = pod.PO_ID

    INNER JOIN dbo.Vendors ven ON po.Vendor_ID = ven.Vendor_ID

    INNER JOIN dbo.PurchaseOrderStatus pos ON po.POStatus_ID = pos.POStatus_ID

    INNER JOIN dbo.Locations loc ON po.ShipToLocation_ID = loc.Location_ID

    LEFT OUTER JOIN dbo.Bills bil ON po.PO_ID = bil.PO_ID

    WHERE po.ShipToLocation_ID = 24

    AND pod.LastReceiveDate = ISNULL ((SELECT MAX(pod1.LastReceiveDate) AS expr1

    FROM dbo.PODetails AS pod1

    WHERE pod1.PO_ID = po.PO_ID), '1900-01-01 00:00:00.000')

    ORDER BY

    po.PO_Number;

    I hope the explanation makes sense.

    Looking at this, it is a logical shot in the dark. I hope it works.

  • Looking at the title (not the text) of the original post gives a clue. "Trying to get null values" sounds like they want to retrieve rows with NULL dates as well as the MAX. Here's a stripped-down version:

    CREATE TABLE po

    (ID int not null, location int, poDate date)

    INSERT po (ID, location, poDate)

    VALUES (2, 24, '2014-07-08'),

    (2, 24, null)

    DECLARE @poID int = 2;

    -- Existing logic

    SELECT * FROM po WHERE id = @poID AND location = 24 AND

    poDate = (SELECT MAX(ISNULL (poDate, '1900-01-01')) FROM po WHERE id = @poID)

    -- Proposed logic

    SELECT * FROM po WHERE id = @poID AND location = 24 AND

    (poDate IS NULL OR poDate = ISNULL((SELECT MAX (poDate) FROM po WHERE id = @poID), '1900-01-01'))

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply