Bad Case of the < - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bad Case of the <

Author

Topic

Biscuithead
Starting Member

30 Posts

Posted - 2007-02-13 : 12:58:19

Heres my code:


SELECT EventID, EventName, BegDate, EndDate, Summary, Location, Link, 
Spacer = CASE EndDate 
WHEN EndDate <> NULL OR EndDate <> '' 
THEN ' - '
END, 
CASE WHEN Booth IS NULL OR Booth = '' 
THEN 'TBD' ELSE Booth END AS Booth 
FROM EventSchedule WHERE BegDate > Getdate() ORDER BY BegDate

Heres my error:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '<'.

Heres my frustration level:
EXTREME

Can anyone offer any suggestions as to why I get an error?
Im trying to check to see if EndDate is NULL or '' if it isnt Id like to have the Spacer column display ' - '
TIA,
Stue

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-13 : 13:11:34

[code]SELECT EventID,
EventName,
BegDate,
EndDate,
Summary,
Location,
Link,
CASE
WHEN ISDATE(EndDate) = 1 THEN ' - '
ELSE ''
END AS Spacer,
CASE
WHEN ISNULL(Booth, '') <> '' THEN 'TBD'
ELSE Booth
END AS Booth
FROM EventSchedule
WHERE BegDate > GETDATE()
ORDER BY BegDate[/code]

Peter Larsson
Helsingborg, Sweden

Biscuithead
Starting Member

30 Posts

Posted - 2007-02-13 : 13:31:26

Thanks Peter! That did the trick.
But Im still confused as to why my code didnt work???
I used this link in order to formulate my code: [url]http://www.craigsmullins.com/ssu_0899.htm[/url]
Is this example incorrect?
Thanks again!
Stue

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-13 : 13:44:36

It depends on you SET ANSI_NULLS setting.

quote:
Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL. Also, a comparison of two expressions that have both evaluated to null values yields TRUE. With ANSI_NULLS set OFF, the following SELECT statement returns all the rows in the Customer table for which Region is a null value:

Peter Larsson
Helsingborg, Sweden

Biscuithead
Starting Member

30 Posts

Posted - 2007-02-13 : 14:38:00

Ah, I understand now. Thanks again for your help and the edification!
Stue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources