473,326 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Subquery in case statement

Hi all, first, let me preface this by saying that I am very new to sql
server, coming from oracle.

Here is my problem: I would like to have a case statement (similar to
decode in oracle) that will test a query for a null value. Here is my
statement:

SELECT
CASE
(SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) WHEN NULL
THEN 0
ELSE
(SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) END

It functions correctly if there is a value for MAX(SEQ) + 1, otherwise
it returns null. It's as if the test for null fails. I hope that
makes sense.

This will ultimately be in an insert statement; I have taken it to the
bare minimum to better understand where the problem is.

Please let me know if I am doing something dumb. Does this sort of
thing have to in a stored procedure?

Anyway, thanks for any help. have a great day!
Ryan

Jul 23 '05 #1
6 20034
The statement may be rewritten as:

SELECT ISNULL((SELECT MAX(SEQ) + 1 FROM [TRANSACTION]),0)

However, I have a feeling that you are about to do something wrong: are
you planning to insert that result back in the same table? If yes, you
may want to take a look at IDENTITY columns in Books Online (I think
that they are something like sequences in Oracle, but I've never worked
with Oracle).

Razvan

Jul 23 '05 #2
Hello Razvan, thanks for the reply. What I am trying to do is a little
silly, but because of preexisting bad database design, may be the
easiest method. I have what would be two independent sequences in the
same table. What i mean is I only want to insert the next value if
certain conditions are met. I looked the identity stuff up and it
doesn't seem like they can be customized in that manner.

For the time being, the syntax you provided will work perfectly :)

Thanks again!
Ryan

Razvan Socol wrote:
The statement may be rewritten as:

SELECT ISNULL((SELECT MAX(SEQ) + 1 FROM [TRANSACTION]),0)

However, I have a feeling that you are about to do something wrong: are you planning to insert that result back in the same table? If yes, you may want to take a look at IDENTITY columns in Books Online (I think
that they are something like sequences in Oracle, but I've never worked with Oracle).

Razvan


Jul 23 '05 #3
(ry*********@gmail.com) writes:
Hi all, first, let me preface this by saying that I am very new to sql
server, coming from oracle.

Here is my problem: I would like to have a case statement (similar to
decode in oracle) that will test a query for a null value. Here is my
statement:

SELECT
CASE
(SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) WHEN NULL
THEN 0
ELSE
(SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) END


I doubt that "IF x = NULL" yields true on Oracle. In SQL when you to test
with NULL, you should use IS NULL, thus:

SELECT CASE WHEN
(SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) IS NULL
THEN 0
ELSE (SELECT MAX(SEQ) + 1 FROM [TRANSACTION])
END
Of course, as Razvan showed you it's more compact to use isnull for
this. Even better is to use coalesce(), as this is ANSI standard. (I
would have expect CASE and coalesce() to be available on Oracle, but
what do I know.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
Hi Erland, in Oracle, I would use a decode statement. It's a really
easy way to perform . . . case statements i.e. decode(col, null, do
something, do something else). Just a fun fact :) I'll look at the
coalesce function; I'm sure that would work as well.

I really appreciate both of your comments. I thought oracle and ms sql
would be similar enough in syntax . . . not close enough apparently.
Thanks again!

Ryan

Jul 23 '05 #5
(ry*********@gmail.com) writes:
Hi Erland, in Oracle, I would use a decode statement. It's a really
easy way to perform . . . case statements i.e. decode(col, null, do
something, do something else). Just a fun fact :) I'll look at the
coalesce function; I'm sure that would work as well.

I really appreciate both of your comments. I thought oracle and ms sql
would be similar enough in syntax . . . not close enough apparently.


Did you think SQL was portable? You've been listening too much to
that Celko-man! :-)

Anyway, decode is apparently Oracle-specific. CASE is actually ANSI-SQL,
as is coalessce(). (But not isnull().
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
ry*********@gmail.com wrote:
Hi all, first, let me preface this by saying that I am very new to sql
server, coming from oracle.

Here is my problem: I would like to have a case statement (similar to
decode in oracle) that will test a query for a null value. Here is my
statement:

SELECT
CASE
(SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) WHEN NULL
THEN 0
ELSE
(SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) END

It functions correctly if there is a value for MAX(SEQ) + 1, otherwise
it returns null. It's as if the test for null fails. I hope that
makes sense.

1. newer versions of Oracle also support CASE-Expression (it's not a
statement, btw - CASE-Statement is part of SQL/PSM in the same bucket as
IF THEN ELSE)
2. SQL Server behaves properly here. The reason is that "simple case"
uses equality, but NULL = NULL is UNKNOWN, which is NOT TRUE. Thus the
system will fall into the ELSE.
If you want to write this right (and still use CASE) you need to use a
"searched case":
CASE WHEN (SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) IS NULL
THEN 0
ELSE (SELECT MAX(SEQ) + 1
FROM [TRANSACTION])
END

Cheers
Serge

PS: I presume SQL Server supports IS NULL or a similar predicate.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Jul 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Tom | last post by:
I would like to know if an .asp case statement can contain HTML elements. I am building an application that I would like to have dynamic choices. The dynamic part would be built in the a case...
1
by: mirth | last post by:
I would like to update a decimal column in a temporary table based on a set of Glcodes from another table. I search for a set of codes and then want to sum the value for each row matching the...
4
by: Chad Richardson | last post by:
I've always been mistified why you can't use a column alias in the group by clause (i.e. you have to re-iterate the entire expression in the group by clause after having already done it once in the...
21
by: Andy | last post by:
Can someone tell me if the following Switch...Case construct is valid? I'm wanting to check for multiple values in the Case statement without explicitly listing each values. So for example, will...
6
by: deanfamily11 | last post by:
I've set up a case statement to have my program determine where on the Cartesian plane a point the user enters is located. I keep getting the C2051 error when I compile. Any help? #include...
12
by: rAinDeEr | last post by:
Hi, I have a table with 2 columns ** CREATE TABLE test (emp_num DECIMAL(7) NOT NULL,emp_name CHAR(10) NOT NULL) and i have inserted a number of records. ** Now, I want to insert a new...
1
by: microsoft.public.dotnet.languages.vb | last post by:
Hi All, I wanted to know whether this is possible to use multiple variables to use in the select case statement such as follows: select case dWarrExpDateMonth, dRetailDateMonth case...
13
by: Satya | last post by:
Hi everyone, This is the first time iam posting excuse me if iam making any mistake. My question is iam using a switch case statement in which i have around 100 case statements to compare. so...
1
Uncle Dickie
by: Uncle Dickie | last post by:
Hi All, I have the following bit of code which looks at a parts list and then checks on incoming and outgoing movements in the next 60 days SELECT pa.PartNumber ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.