July 8, 2014 at 7:14 am
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
July 8, 2014 at 7:44 am
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/
July 8, 2014 at 7:58 am
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.
July 8, 2014 at 8:45 am
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.
July 8, 2014 at 8:57 am
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.
July 8, 2014 at 9:48 am
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.
July 8, 2014 at 10:12 am
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.
July 9, 2014 at 8:55 am
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