July 7, 2014 at 3:57 pm
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 7, 2014 at 4:27 pm
First, you posted this in a SQL Server 7/2000 forum, so big question is what version are you using?
Second, with just a code snippet it is difficult to know what it is you are actually trying to accomplish. If you could post the DDL (CREATE TABLE) statement for the table involved, some sample data (INSERT INTO statements, again SAMPLE data and some should meet the criteria for your query and some should be excluded), and the expected results based on the sample data.
Be sure to test the code you post in an empty database to be sure it all runs correctly and provides a test environment we can work with to help solve your problem.
July 8, 2014 at 7:46 am
This was also posted in the 2008 forum. http://www.sqlservercentral.com/Forums/Topic1590347-1292-1.aspx
Please don't cross post. It only causes fragmentation in your answers.
_______________________________________________________________
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:59 am
Sean, saw that and asking to use this thread for replies. I am also asking that this thread be moved to a SQL Server 2008 forum.
July 8, 2014 at 8:11 am
Lynn Pettis (7/8/2014)
Sean, saw that and asking to use this thread for replies. I am also asking that this thread be moved to a SQL Server 2008 forum.
heh the other one is already in a 2008 forum so I figured we would roll with that one.
OP - do you see the confusion created? 🙂
_______________________________________________________________
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 8:18 am
Already using this one though, and I didn't see the other until I got into work this afternoon. I'd seen this one early this morning. All times Afghanistan time, of course.
It is currently (posting time) 18:49 in Afghanistan.
July 8, 2014 at 8:23 am
And we asked for the same info in both threads as well.
But, with as little to go on we need the extra info.
July 8, 2014 at 8:35 am
Sorry, I reposted in SQL 2008. I didn't notice that I was in SQL 2000 when I posted originally. I'm on SQL 2008 R2.
July 8, 2014 at 8:45 am
Lavery (7/8/2014)
Sorry, I reposted in SQL 2008. I didn't notice that I was in SQL 2000 when I posted originally. I'm on SQL 2008 R2.
Already determined that. Now, how about the details we need to be able to help you.
July 9, 2014 at 9:09 am
Ouch. I saw the other post first (which has some good replies), and replied there. Anyway, the title of the other post suggest that the poster wants the query to return rows with NULLs in that date column.
Duplicating my reply from there, 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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply