If Is Null in Select Statement

Greetings,
I am getting the following error
"Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IF'."
My SQL statement is:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
dbo.qry1.col5 END
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
Note: qry1 and qry2 are the same query but am join different columns to the
same column in the table
Thanks for the help
KeithSELECT ..., COALESCE(qry1.col5, qry2.col3)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||IF IS NULL doesnt exist in SQL Server. Use ISNULL(Columtocheck, ElseValue),
OR COALESCE(Columntocheck[,ColumnTocheck], ElseValue). If you wanna put an
IF / CAse Expression in your query refer to the BOL and to the syntax of
CASE, example:
CASE Somecolumn
WHEN NULL THEN 'SomeValue'[Or a cloumn]
WHEN ...
...
ELSE 'Something'
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Keith" <Keith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6CBEB225-5880-4A26-A132-AD7F960FAD58@.microsoft.com...
> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to
> the
> same column in the table
> Thanks for the help
> Keith|||If is a Transact SQL Control flow statement, and cannot be used inside of a
SQL Statement. What you want is the SQL Case Expression. (look it up in
Books OnLIne)
as Folows:
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
Case When dbo.qry1.col5 Is Null
Then dbo.qry2.col3
Else dbo.qry1.col5 End
FROM dbo.tbl1
LEFT OUTER JOIN dbo.qry2
ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN dbo.qry1
ON dbo.tbl1.col4 = dbo.qry1.col5
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith|||Hi Keith,
The query can be re-written as
SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
ISNULL(dbo.qry1.col5, dbo.qry2.col3)
FROM dbo.tbl1 LEFT OUTER JOIN
dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
LEFT OUTER JOIN
dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
best Regards,
Chandra
---
"Keith" wrote:

> Greetings,
> I am getting the following error
> "Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'IF'."
> My SQL statement is:
> SELECT dbo.tbl1.col1, dbo.tbl1.col2, dbo.tbl1.col3, dbo.tbl1.col4,
> IF IS NULL(dbo.qry1.col5) THEN BEGIN dbo.qry2.col3 END ELSE BEGIN
> dbo.qry1.col5 END
> FROM dbo.tbl1 LEFT OUTER JOIN
> dbo.qry2 ON dbo.tbl1.col4 = dbo.qr1.col3
> LEFT OUTER JOIN
> dbo.qry1 ON dbo.tbl1.col4 = dbo.qry1.col5
> Note: qry1 and qry2 are the same query but am join different columns to th
e
> same column in the table
> Thanks for the help
> Keith

If Exists Schema Fails

Could someone explain why

IF SCHEMA_ID('TestSchema') is nul CREATE SCHEMA TestSchema

Fails with Incorrect syntax near the keyword 'SCHEMA'.

Where as running the following statements

CREATE SCHEMA TestSchema

and

IF SCHEMA_ID('TestSchema') is null SELECT 1

succeeds without a problem.

Thanks

_GJK

Ok. I found the solution from

http://groups.google.com/group/microsoft.public.sqlserver.newusers/browse_thread/thread/69a4e9c45d045120/77c693f5043fbddc?lnk=st&q=schema_id&rnum=1#77c693f5043fbddc

|||

Actually the real root of your problem is that CREATE SCHEMA must be the first statement in a batch. Using dynamic SQL (as suggested in the link above) is a workaround as the dynamic statement itself is considered a new batch.

-Raul Garcia

SDE/T

SQL Server Engine