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 ELSE with WHERE, AND, OR

What would be the correct way of writing a sql select state with where
clause while also using IF ELSE. I am using T-SQL and I cannot get it
to work. I probably have the syntax wrong.

I want to be able to have different where/and/or clauses in the sql
dependant on what value was passed into the @.SearchTerm parameter in
this stored procedure.

Can I use CASE statements in the WHERE section? Or is that strickly for
SELECT statements?

Code as follows:

================================================== ==============

CREATE PROCEDURE spTicketReport
(
@.SearchTerm varchar(100) = NULL
)
AS
BEGIN
SELECT TOP 100 PERCENT Tickets.TicketID, Tickets.TicketNumber AS
TicketNumber, Haulers.Name AS Hauler, Leases.LeaseID AS LeaseID,
Leases.LeaseName AS Lease, Shippers.Name AS
Shipper, Tickets.FeeTox, Tickets.FeeWashout, Tickets.FeeDisposal,
Tickets.Yards, Tickets.Barrels,
Tickets.FluidSolidRatio, DATEPART(yyyy, Tickets.DateAdded) AS [Year]
FROM Tickets INNER JOIN
Leases ON Tickets.LeaseID = Leases.LeaseID INNER
JOIN
Haulers ON Tickets.HaulerID = Haulers.HaulerID
INNER JOIN
Shippers ON Tickets.ShipperID =
Shippers.ShipperID
WHERE TicketNumber LIKE '%' + @.SearchTerm + '%' OR Haulers.Name LIKE
'%' + @.SearchTerm + '%' OR Shippers.Name LIKE '%' + @.SearchTerm + '%'
OR Leases.LeaseName LIKE '%' + @.SearchTerm + '%'
ORDER BY TicketNumber, Shipper, Hauler

================================================== ==============

Thanks in advance!

Jason Cochran
Rethink Technologies, L.L.C.
www.rethinkllc.com(jcochran@.rethinkllc.com) writes:
> What would be the correct way of writing a sql select state with where
> clause while also using IF ELSE. I am using T-SQL and I cannot get it
> to work. I probably have the syntax wrong.
> I want to be able to have different where/and/or clauses in the sql
> dependant on what value was passed into the @.SearchTerm parameter in
> this stored procedure.
> Can I use CASE statements in the WHERE section? Or is that strickly for
> SELECT statements?

You cannot use CASE statements, because there are none in T-SQL. But
you can use CASE expressions in a WHERE clause:

WHERE CASE WHEN @.SearchTerm LIKE <a ticket number>
THEN TicketNumber LIKE '%' + @.SearchTerm + '%'
WHEN @.SearchTerm LIKE <a haluers name>
THEN Haulers.Name
ELSE Leases.LeaseName
END LIKE '%' + @.SearchTerm + '%'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is a good piece of information, however it can be achieved by the
other way as well...
By using parantheses and boolean operators (AND, OR, NOT) properly.

Something like this:

WHERE ( @.SearchTerm LIKE <a ticket number> ANDTicketNumber LIKE '%'
+ @.SearchTerm + '%' )
OR
( @.SearchTerm LIKE <a haluers name> ANDHaulers.Name LIKE '%' +
@.SearchTerm + '%' )
OR
( Leases.LeaseName LIKE '%' + @.SearchTerm + '%' )|||Hi Erland,
Very informative answer , but from performance point of view we should
not be using Like

Most DBMSs will use an index for a LIKE pattern if it starts with a
real character but will avoid an index for a LIKE pattern that starts
with a wildcard (either % or _). The only DBMSs that never use indexes
for LIKE are Pick and mSQL (on TEXT fields). For example, if the search
condition is:

... WHERE column1 LIKE 'C_F%'

DBMSs will resolve it by finding all index keys that start with C and
then filtering those that contain F in the third position. In other
words, you don't need to transform this search condition:
Here '%' is being used at the beginning so I think using charindex will
do fine job (Please correct it if wrong)

Wherecharindex
( @.SearchTerm,
(
CASE
WHEN charindex(@.SearchTerm, a ticket number )>0 THEN TicketNumber

WHEN charindex(@.SearchTerm,a haluers name) > 0 THEN Haulers.Name

ELSE Leases.LeaseName
END
)
)>0

With warm regards
Jatinder|||I appreciate everyones help on this.

What if I wanted to add another parameter named @.AccountID. AccountID
is used to track who created the ticket. @.AccountID would be set to
NULL just like @.SearchTerm is. I wanted to be able to check to see if
either was passed in. Sometimes both will be; other times it will be
either/or.

============ PSEUDO CODE ===================

WHERE TicketID != 0

IF NOT @.SearchTerm IS NULL THEN
AND (TicketNumber LIKE '%' + @.SearchTerm + '%' OR Haulers.Name
LIKE '%' + @.SearchTerm + '%' OR Shippers.Name LIKE '%' + @.SearchTerm +
'%' OR Leases.LeaseName LIKE '%' + @.SearchTerm + '%' )
END IF
IF NOT @.AccountID IS NULL THEN
AND AccountID = @.AccountID
END IF

ORDER BY TicketNumber, Shipper, Hauler

============ END PSEUDO CODE ===================|||(jcochran@.rethinkllc.com) writes:
> I appreciate everyones help on this.
> What if I wanted to add another parameter named @.AccountID. AccountID
> is used to track who created the ticket. @.AccountID would be set to
> NULL just like @.SearchTerm is. I wanted to be able to check to see if
> either was passed in. Sometimes both will be; other times it will be
> either/or.
>...
> IF NOT @.AccountID IS NULL THEN
> AND AccountID = @.AccountID
> END IF

AND (AccountID = @.AccountID OR @.AccountID IS NULL)

However, beware that if you want any index on AccuontID to be use, you
better split this up and have two different SELECT statements.

For a much longer discussion on a problem which you have not really
reached, but seem to be on your way to, I have an article on my web
site that you can save for a rainy day:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Looking at that article you mentioned; under if statements, the code
below is mentioned. It just seems like a very nasty way of doing
things. I could do it this way BUT I just think there should be a much
cleaner way of doing it. If I had to change/remove/add a column in the
select statement, I would have 3 other places to do it in. The WHERE
statement should be the only thing that is different. I shouldnt have
to have the same select statement 3 times.

IF @.orderid IS NOT NULL
BEGIN
SELECT ...
WHERE o.OrderID = @.orderid
AND od.OrderID = @.orderid
AND (od.UnitPrice >= @.minprice OR @.minprice IS NULL)
AND (od.UnitPrice <= @.maxprice OR @.maxprice IS NULL)
AND (od.ProductID = @.prodid OR @.prodid IS NULL)
AND (p.ProductName LIKE @.prodname + '%' OR @.prodname IS NULL)
ORDER BY o.OrderID
END
ELSE IF @.custid IS NOT NULL
BEGIN
SELECT ...
WHERE (o.OrderDate >= @.fromdate OR @.fromdate IS NULL)
AND (o.OrderDate <= @.todate OR @.todate IS NULL)
AND (od.UnitPrice >= @.minprice OR @.minprice IS NULL)
AND (od.UnitPrice <= @.maxprice OR @.maxprice IS NULL)
AND c.CustomerID = @.custid
AND o.CustomerID = @.custid
AND (od.ProductID = @.prodid OR @.prodid IS NULL)
AND (p.ProductName LIKE @.prodname + '%' OR @.prodname IS NULL)
ORDER BY o.OrderID
END
ELSE
BEGIN
SELECT ...
WHERE (o.OrderDate >= @.fromdate OR @.fromdate IS NULL)
AND (o.OrderDate <= @.todate OR @.todate IS NULL)
AND (od.UnitPrice >= @.minprice OR @.minprice IS NULL)
AND (od.UnitPrice <= @.maxprice OR @.maxprice IS NULL)
AND (c.CompanyName LIKE @.custname + '%' OR @.custname IS NULL)
AND (c.City = @.city OR @.city IS NULL)
AND (c.Region = @.region OR @.region IS NULL)
AND (c.Country = @.country OR @.country IS NULL)
AND (od.ProductID = @.prodid OR @.prodid IS NULL)
AND (p.ProductName LIKE @.prodname + '%' OR @.prodname IS NULL)
ORDER BY o.OrderID
END|||jcochran@.rethinkllc.com (jcochran@.rethinkllc.com) writes:
> Looking at that article you mentioned; under if statements, the code
> below is mentioned. It just seems like a very nasty way of doing
> things.

This is indeed not a method that scales well in terms of maintenance
when you have many different conditions, and I also note this in the
article.

> I could do it this way BUT I just think there should be a much
> cleaner way of doing it. If I had to change/remove/add a column in the
> select statement, I would have 3 other places to do it in. The WHERE
> statement should be the only thing that is different. I shouldnt have
> to have the same select statement 3 times.

Well, it depends with you mean with cleaner. You can do all in one
single static SQL statement, and from the perspective of maintenance
and functionality you would be fine. However, SQL programming is also
a lot about performance. If your table has 100 million rows, you don't
want a table scan to happen on an interactive query.

For this reason, one sometimes has to duplicate code in a way that
conflicts with the best practices you've learnt when working with
traditional languages.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

IDENTITY Problems Updating ZipCode Table

I am having problems updating my zip code table that contains zip, city, state, long, lat, ect..

I have the latest CSV file, I tried to use the import wizard in SQL Server 2000 Enterprise Manager.
I set the ID field as <ignore> and specified the appropriate columns for the rest of the data matching from CSV to already designed and working zip code table. Also I checked the box that said "Delete Rows in Destination Table" as well as "Enable Identity Insert" was checked

I ran the wizard, and now I have empty table and it will not insert any records because the error said that the identity column can not accept NULL.

What do I do? I am not updating the identify column so Is it telling me it can't insert NULL into ID?

Any suggestions...

Thanks,
LitoI set the ID field as <ignore>
...
as well as "Enable Identity Insert" was checked
...
error said that the identity column can not accept NULL.

You are inserting NULL into the ID field, because you have "<ignore>" selected for the ID column, and "Enable Identity Insert" checked. You need to uncheck "Enable Identity Insert" and this should work.|||For the 5th Time I repeated the process and this time i did not check "Enable Identity Insert" and it worked.

Sory to bother you, I was just getting frustrated with this stupid problem|||That's Ok... I think the reason that most of us hang out here is to give folks a hand (and occaisionally make the others say "Doh, why didn't I think of that!"). As long as life is good now, that is all that counts!

-PatP

identity

i am using sql server mobile

I have an insert statement with select @.@.identity:
commandString = "insert into Location (Address,Suburb,State) values('627 beach rd', 'rose bay', 'NSW');SELECT @.@.IDENTITY"

I execute the insert statement as below:
command.CommandText = commandString;
id = command.ExecuteNonQuery();

I get the following error:
Error: There was an error parsing the query. [ Token line number = 1,Token line offset = 137,Token in error = SELECT ]
Source: SQL Server 2005 Mobile Edition ADO.NET Data Provider

this statement works ok in sql mobile query analyzer

does anybody know where i am going wrong

do i need an output parameter

thanks
adamTry ...

SELECT SCOPE_IDENTITY() AS LocationID

...instead...I haven't worked with SQL Mobile, but you should rarely use @.@.IDENTITY. @.@.IDENTITY will give you the last identity generated in the database by any session. If there's a trigger or other user session that generates an IDENTITY for another table or the same table, you will get the wrong value. SCOPE_IDENTITY() gives you the last IDENTITY value generated by your session. (see BOL for more details about the difference.)

Also, look into the use of a simi-colon between the statements. I've seen that throw wierd errors. You might replace that with a CRLF instead. You can determine whether the semi-colon or the @.@.identity is causing the issue by replacing the @.@.identity with a one...(ie...SELECT 1 AS LocationID)

You might also need the (AS LocationID) in the statement for ADO to assign a column name to the return value, but not sure...

Lastly, as a matter of good form, you might consider placing this whole thing into a simple stored proc.

CREATE PROCEDURE dbo.LocationInsert (
@.Address VARCHAR(255),
@.Suburb VARCHAR(255),
@.State VARCHAR(255) )

AS

BEGIN

INSERT INTO dbo.Location (
Address,
Suburb,
State )
VALUES (
@.Address,
@.Suburb,
@.State )

SELECT SCOPE_IDENTITY() AS LocationID

END

Then, simply call the stored proc with the following....

commandString = "EXEC dbo.LocationInsert @.Address='627 beach rd', @.Suburb='rose bay', @.State='NSW'"

Good luck...

g2|||Setting aside issue of SCOPE_IDENTITY vs. @.@.IDENTITY, the problem you are seeing is a limitation of SQL Mobile.

This is a piece of SQL Server 2005 Mobile Books Online (http://msdn2.microsoft.com/en-us/library/ms173053(en-us,SQL.90).aspx)

Queries that can typically be run on SQL Server Mobile can also run on Microsoft SQL Server; however, many of the features of Transact-SQL are absent from SQL Server Mobile, and only a single SQL statement can be executed in a command.

Stored procedures are not allowed either (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=104249).

To Greg: semicolons are required on Mobile to separate statements, so CRLF won't help in this case (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=92581).

Regards,
Boris.