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 inside of CASE

Can anyone help me with condition inside CASE. Below is my code and I get
syntax error at IF line. Thanks.
SELECT TOP 100 PERCENT
dbo.WorkerDeductions.EmployeeNumber,
dbo.WorkerDeductions.DedCode,
dbo.WorkerDeductions.DedAmt,
dbo.WorkerDeductions.DeductionBalance,
dbo.WorkerDeductions.DedPercent,
dbo.PayInfoNHS.EarnGross,
dbo.PayInfoNHS.CheckID,
DedCalc = CASE
WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
BEGIN
dbo.WorkerDeductions.DedAmt
END
ELSE dbo.WorkerDeductions.DeductionBalance
WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
ELSE dbo.WorkerDeductions.DedAmt
END
FROM dbo.WorkerDeductions INNER JOIN
dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
dbo.PayInfoNHS.EmployeeNumber INNER JOIN
dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
dbo.DeductionCodeLookup.DedCode
WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
102))
ORDER BY dbo.WorkerDeductions.EmployeeNumberYou can't use IF in a query; IF is for flow, not statement-level control.
Try a nested CASE:
CASE
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
ELSE
..
END
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ODfTYAyLFHA.2384@.tk2msftngp13.phx.gbl...
> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>|||David C,
You can nest a CASE expression inside another, but you can not use IF inside
a CASE.
AMB
"David C" wrote:

> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>
>|||That worked. Thank you.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

IDIOT NEEDS HELP comparing a variable against a list

I have a variable called @.ORComm which has been selected using a cursor from each line on an order.

DECLARE TC2 CURSOR FOR
SELECT [Commodity],[Total] FROM [CSITSS].[dbo].[Ordrate] WHERE [OrderNumber]= @.OrdNum AND [Companydiv] = 'GLPC-TRANS'
OPEN TC2
FETCH NEXT FROM TC2 INTO @.ORComm, @.ORTotal

I need to compare the resulting @.ORComm against a list of valid commodity types selectable by

SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0

What's the easiest way to do this?SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0 and CommodityClass=@.ORComm

??|||I feel stupid I didn't think of that. I was thinking of storing the selected values in an array but that is much simpler.