February 5, 2013 at 7:31 am
Good morning! 🙂
I'm having a little issue with a CASE statement with a date condition. So basically all I need to do is have anything between today and 30 days back to please "NEW" and anything older than 30days display "Older than 30 days" but I'm getting a syntax error on line 2 "An expression of non-boolean type specified in a context where a condition is expected".
Can someone please provide a hint as to what I'm doing wrong here?
AND IMF.ImportDate > (Select DATEADD (day,-60,getdate()))
AND CASE WHEN ImportDays=(Select DATEADD (day,-30,getdate())) THEN 'NEW' ELSE 'Older than 30 days' END
WHERE RESUB.ProducerGroupID IS NULL and IFH.ErrorDetail IS NOT NULL
February 5, 2013 at 7:42 am
I don't see the benefit of the subqueries here. Also your "New" filter should use a >= rather than just an =.
Does
AND IMF.ImportDate > DATEADD (day,-60,getdate())
AND CASE WHEN ImportDays>=DATEADD (day,-30,getdate()) THEN 'NEW' ELSE 'Older than 30 days' END
WHERE RESUB.ProducerGroupID IS NULL and IFH.ErrorDetail IS NOT NULL
do the job?
Just noticed that the above appears to be part of a WHERE clause in which case you can't do an assignment. Can you post the whole query and perhaps some DDL?
February 5, 2013 at 8:52 am
So basically I'm wanting to do something like this:
CASE WHEN ImportDays=(Select DATEADD (day,-30,getdate()))THEN 'NEW' ELSE 'Older than 30 days' END AS ImportDate
February 5, 2013 at 8:52 am
delete
February 5, 2013 at 9:34 am
Just add this as a column in your select
CASE WHEN ImportDays <= DATEADD(day,-30,getdate()) THEN 'NEW' ELSE 'Older than 30 days' END AS ImportDate
And be careful with NOLOCK it has a number of unexpected results like missing/duplicate data.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
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/
February 5, 2013 at 11:21 am
Thanks Sean. I am adding the column as "AS ImportDate". This is actually a stored proc that I am putting together that will import into an xls. The trouble that I'm having is where to place this line in the over all code. It seems that no matter where I place it I get syntax errors. Unless it does not recognize the AS ImportDate.
February 5, 2013 at 12:15 pm
igloo21 (2/5/2013)
Thanks Sean. I am adding the column as "AS ImportDate". This is actually a stored proc that I am putting together that will import into an xls. The trouble that I'm having is where to place this line in the over all code. It seems that no matter where I place it I get syntax errors. Unless it does not recognize the AS ImportDate.
Well just saying syntax errors doesn't do much to help explain the problem. However I suspect it is because you trying to refer to a column that was aliased as ImportDays. I didn't look very closely originally because your code is so unformatted. You also are trying to add a new column that has already been named. Your query will work but it is a very bad practice to have multiple columns with the same name. If you have code that needs to refer by column name which one does it get? 😛
How about this?
CASE WHEN IMF.ImportDate <= DATEADD(day,-30,getdate()) THEN 'NEW' ELSE 'Older than 30 days' END AS ImportedTimeFrame
Just so you can see what formatting can do for your sql here is your code run through an online formatter. I used poorsql.com but there are plenty of others. The code from this is WAY easier to read.
SELECT IMF.FileID
,IMF.[FileName]
,CONVERT(VARCHAR, IMF.ImportDate, 101) AS ImportDate
,CONVERT(VARCHAR, IMF.ImportDate, 101) AS ImportDays
,IFH.ImportID
,IFH.VendorGroupID
,IFH.DealerNumber
,IFH.VendorName
,IFH.VendorNumber
,IFH.LastName
,IFH.Term
,IFH.VIN
,IFH.RecordType
,IFH.VendorID
,IFH.LoANDate
,ISNULL(IFH.CancelDate, '') AS CancelDate
,IFH.XycorCode AS ProductCode
,IFH.ErrorDetail
,CASE
WHEN IMF.ImportDate <= DATEADD(day, - 30, getdate())
THEN 'NEW'
ELSE 'Older than 30 days'
END AS ImportedTimeFrame
FROM dbo.tblImportFileHistory IFH WITH (NOLOCK)
LEFT JOIN --exclude successfully resubmitted
(
SELECT DISTINCT IFH1.VendorGroupID
,IFH1.VendorNumber
,IFH1.RecordType
FROM dbo.tblImportFileHistory IFH1 WITH (NOLOCK)
LEFT JOIN dbo.tblImportFileHistory IFH2 WITH (NOLOCK) ON IFH1.VendorGroupID = IFH2.VendorGroupID
AND IFH1.VendorNumber = IFH2.VendorNumber
AND IFH1.RecordType = IFH2.RecordType
WHERE IFH1.ErrorDetail IS NOT NULL
AND IFH2.ErrorDetail IS NULL
) AS RESUB ON IFH.VendorGroupID = RESUB.VendorGroupID
AND IFH.VendorNumber = RESUB.VendorNumber
AND IFH.RecordType = RESUB.RecordType
LEFT JOIN (
SELECT PR.VendorGroupID
,MP.VendorNumber
,MP.VIN_Nbr
,MP.VendorID
FROM dbo.tblMasterVendor MP WITH (NOLOCK)
INNER JOIN dbo.tblValidProducts VP WITH (NOLOCK) ON MP.ValidProductID = VP.ValidProductID
INNER JOIN dbo.tblVendors PR WITH (NOLOCK) ON VP.VendorID = PR.VendorID
WHERE MP.IssueStatus = 'A'
) AS EXCISS ON IFH.VendorNumber = EXCISS.VendorNumber
AND IFH.VIN = EXCISS.VIN_Nbr
AND IFH.VendorGroupID = EXCISS.VendorGroupID
AND IFH.RecordType = 'I'
LEFT JOIN (
SELECT PR.VendorGroupID
,MP.VendorNumber
,MP.VIN_Nbr
,MP.VendorID
FROM dbo.tblMasterVendor MP WITH (NOLOCK)
INNER JOIN dbo.tblRefunds RF WITH (NOLOCK) ON MP.VendorID = RF.VendorID
INNER JOIN dbo.tblValidProducts VP WITH (NOLOCK) ON MP.ValidProductID = VP.ValidProductID
INNER JOIN dbo.tblVendors PR WITH (NOLOCK) ON VP.VendorID = PR.VendorID
) AS EXCCAN ON IFH.VendorNumber = EXCCAN.VendorNumber
AND IFH.VIN = EXCCAN.VIN_Nbr
AND IFH.VendorGroupID = EXCCAN.VendorGroupID
AND IFH.RecordType = 'C'
LEFT JOIN dbo.tblImportedFiles IMF WITH (NOLOCK) ON IFH.FileID = IMF.FileID
INNER JOIN dbo.tblImportSpec TIS WITH (NOLOCK) ON TIS.VendorGroupID = CASE
WHEN @VendorGroupID = - 1
THEN TIS.VendorGroupID
ELSE @VendorGroupID
END
AND LEFT(IMF.[FileName], 4) = TIS.[FileName]
AND TIS.Active = 1
AND TIS.VendorGroupID NOT IN (
1
,52
,30
,8
,47
,42
,39
,24
,55
)
AND IMF.ImportDate > (
SELECT DATEADD(day, - 60, getdate())
)
WHERE RESUB.VendorGroupID IS NULL
AND IFH.ErrorDetail IS NOT NULL
AND EXCISS.VendorID IS NULL
AND EXCCAN.VendorID IS NULL
AND ISNUMERIC(IFH.VendorGroupID) = 1
AND IFH.RejectReportExclude = 0
ORDER BY IMF.[FileName]
,IFH.ImportID
,IFH.VendorNumber;
_______________________________________________________________
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/
February 7, 2013 at 11:49 am
This worked wonderfully! The only thing I had to chenge was switching it from less than to greater than. A big thanks for your assistance!
February 7, 2013 at 12:00 pm
igloo21 (2/7/2013)
This worked wonderfully! The only thing I had to chenge was switching it from less than to greater than. A big thanks for your assistance!
You're welcome. Glad that worked for you.
_______________________________________________________________
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/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply