CASE statement with IN/OR

Hello,

I'm trying to write a query with case statement.

the condition is

when project_ref =393 then select qtn_ref in (7070000,7060000))
and when project_ref =391 and select q.qtn_ref=8700000

I need this condition in 'WHERE' statement.

I can use 2 SELECT queries using 'IF ELSE', but I woud like to find out if there's any way to use CASE so I can write 1 query.

Tring to do something like this but it doesn't work.

SELECT *

FROM table

WHERE qtn_ref = case when project_ref =393 then 7070000 or 7060000
when project_ref =391 then 8700000

Anyone can help, please?

Hi,

I just modified your Query. i dont have an SQL Installation so im not able to test the Query.

Plz Try this i hope i would solve your problem.

SELECT * FROM table
WHERE (qtn_ref = case when project_ref =393 then 7070000
when project_ref =391 then 8700000) OR
(qtn_ref = case when project_ref =393 then 7060000
when project_ref =391 then 8700000)

|||

SELECT*FROM yourTable

WHERE qtn_ref=(casewhen project_ref=393 then 7070000

when project_ref=391 then 8700000 end)OR

qtn_ref=(casewhen project_ref=393 then 7060000

when project_ref=391 then 8700000 end)

|||thanks guys. it works.|||Plese check one of them as answers!

CASE Statement

Hello All,

I have a condition for which I am trying to write a case statement as follows..

SELECT @.Segment_Field = 'Acct_Status_' + CASE @.Public_record_Type
WHEN NULL THEN ' '
WHEN 'BP' THEN 'BP' + ((CASE @.Bankruptcy_Type WHEN NULL THEN ' ' ELSE '1' END) OR (CASE @.Acct_Status WHEN NULL THEN '' ELSE '2' END))
END

But this is not working.The condition is actually tht I have to set the value for the variable @.Segment_Field acc to the value of variables @.Bankruptcy_Type and @.Acct_Status. It should first check for @.Bankruptcy_Type and if it is not null then the value should be Acct_Status_BP1.Then it should check for @.Acct_Status and if it is not sull then the vlaue should be set to Acct_Status_BP2.

Can somebody please help..

Thanks

The problem is the null comparison. NULL <> NULL. You are trying to compare NULL = NULL, both being UNKNOWN values then it is unknown if they match. SQL Server comparisons are based on TRUE only, all others do not match.

declare @.value varchar(10)
--set @.value = 'Fred'
set @.value = NULL

select case @.value
when 'Fred' then 'It''s Fred'
when 'Barney' then 'It''s Barney'
when NULL then 'It''s NULL'
else 'None of the above' end as whatIsIt

Just rewrite to a searched case expression:

declare @.value varchar(10)
--set @.value = 'Barney'
set @.value = NULL

select case when @.value = 'Fred' then 'It''s Fred'
when @.value = 'Barney' then 'It''s Barney'
when @.value is NULL then 'It''s NULL'
else 'None of the above' end as whatIsIt

Note also that technically CASE is an expression, not a statement. Not that you used the CASE expression wrong, but it helps to remember that it evaluates to a scalar, not control of flow statements like in many other languages.

|||

Thanks Louis.

But my prob is that I have to check for value of two different variables and not one as you specified @.Value..I can definitley check for two different values of one variable but assigning value to a variable on basis of two different variables is what I am not able to do..

|||

Is this what you want? You can put as complex a condition in a WHEN clause as you need, even including subqueries...

SELECT @.Segment_Field = 'Acct_Status_' +

CASE when @.Public_record_Type is null then ''
when @.Public_record = 'BP' THEN 'BP' +
CASE when @.Bankruptcy_Type is null THEN ' ' ELSE '1' END +
CASE when @.Acct_Status is NOT NULL and @.Bankruptcy_Type is null THEN '2' ELSE '' END

CASE / NULL Problem

Hello!
I want to set a value depending on a condition but I can't seem to get it
right when the original value is NULL.
Select bla, bla... FROM bla...
*****************************
DellyDate=CASE DelivDate
WHEN NULL THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
***********************************'
This code parses successfully, but even when DelivDate actually is NULL,
DellyDate never has the value '1999-12-31'. What am I doing wrong here?You'll need to use IS NULL.
DellyDate=CASE WHEN DelivDate IS NULL
THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
or alternatively
DellyDate=COALESCE(LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) +
'-' +
RIGHT(DelivDate,2) ,'1999-12-31' )|||DellyDate = CASE WHEN DelivDate IS NULL THEN '1999-12-13' ELSE .... END
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Clarkie" <clarkbones@.rock.sendmenot.etmail.com> wrote in message
news:eKVi$lQJGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>|||you could also
coalesce(convert(varchar(10), convert(datetme, DelivDate), 120),
'1999-12-31')
"Clarkie" wrote:

> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>
>|||Read the definition of the CASE expression. You are using a shorthand
for
SELECT ..
FROM ..
WHERE delly_date
=CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
THEN '1999-12-31'
ELSE .. END ;|||Thanks for all the answers!
Problem solved!
//Clarkie
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138581718.965490.84740@.g14g2000cwa.googlegroups.com...
> Read the definition of the CASE expression. You are using a shorthand
> for
> SELECT ..
> FROM ..
> WHERE delly_date
> =CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
> THEN '1999-12-31'
> ELSE .. END ;
>

Capturing the results from exec command

Hi,

I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.

exec(select count(*) from @.dynamictable where condition = @.dynamiccond)

But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?

Thanks

2 ways

USE pubs
GO

--sp_executesql
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = N'SELECT @.intTableCount = COUNT(*) FROM ' + @.chvTableName

EXEC sp_executesql @.chvSQL, N'@.intTableCount INT OUTPUT', @.intTableCount OUTPUT

SELECT @.intTableCount
GO

--EXEC (SQL)
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = 'Insert into #temp Select Count(*) from ' + @.chvTableName

EXEC( @.chvSQL)

SELECT @.intTableCount = Totalcount from #temp

SELECT @.intTableCount

DROP TABLE #temp

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Or:

DECLARE @.chvTableName VARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'sysobjects'

insert into #temp(totalCount)
EXEC( 'Select Count(*) from ' + @.chvTableName)

SELECT * from #temp

DROP TABLE #temp

Note: it is generally considered a bad practice to do this sort of thing unless you are building some sort of tool. If this is a production application, it would be better to build a procedure per table:

create procedures count_accounts
as
select count(*) from account
go

Yes, it sounds like a lot of maintenance, but unless you build a large quantity of tables, it shouldn't be a big deal.

Cant use nvarchar in BETWEEN condition

Greetings all,

I am trying to do an SQL query with BETWEEN in my where condition. Problem is the values I am checking are nvarchar since they also have letters in them. This results in a query that produces nothing. Here is my query:

CREATE PROCEDURE dbo.qryComplete
(
@.From nvarchar(50),
@.To nvarchar(50)
)

AS SELECT dbo.[tbl Object].[Object ID],
dbo.[tbl Object].[Object Name], dbo.[tbl Object].[Object Type],
dbo.[tbl Object].Category, dbo.[tbl Object].Subcategory,
dbo.[tbl Object].[Group], dbo.[tbl Object].[Accession Number],
dbo.[tbl Object].[Physical Description],
dbo.[tbl Object].[Acquisition Method],
dbo.[tbl Object].[Acquisition Date],
dbo.[tbl Object].[Acquisition Source], dbo.[tbl Object].Status,
dbo.[tbl Object].[Number of Parts or Components],
dbo.[tbl Object].[Description of Parts or Components],
dbo.[tbl Object].Title, dbo.[tbl People].[Persons Name],
dbo.[tbl People].Address, dbo.[tbl People].City, dbo.[tbl People].Province, dbo.[tbl People].Country,
dbo.[tbl People].[Postal Code], dbo.[tbl People].Biography,
dbo.[tbl People].Status AS Dead, dbo.[tbl People].Phone
FROM dbo.[tbl Object] LEFT OUTER JOIN
dbo.[tbl People] ON [tbl Object].[Acquisition Source] = [tbl People].[Persons Name]
WHERE (dbo.[tbl Object].[Accession Number] BETWEEN @.From AND @.To) AND (dbo.[tbl Object].Status LIKE 'A%' OR
dbo.[tbl Object].Status LIKE 'B%' OR
dbo.[tbl Object].Status IS NULL)
ORDER BY dbo.[tbl Object].[Accession Number]
GO

If I replace Accession Number with Object ID in the WHERE condition, this query works fine. With the accession number however it returns no results. I have also tried using convert and cast functions to try to convert the values to numeric but I get an error saying it can't convert to type numeric. I've also tried replacing the BETWEEN with comparison operators (>=) but it still doesn't work. Some examples of the Accession numbers we are using:
CF1968.5
CF1968.7A-E
CS1986.2.2

Any suggestions?Can you post @.FROM and @.To as well and then show us what you think the result set should be|||Check out this example, and let me know what you think

USE Northwind
GO
sp_help Orders
GO
DECLARE @.From char(1), @.To char(1)
SELECT @.From = 'A', @.To = 'B'
SELECT * FROM Orders WHERE ShipName BETWEEN @.From AND @.To
GO

But, really BETWEEN with chars?|||Yes I agree that doing it with nvarchars is not a very good way of doing it. What I am working with is a hand-me down of a hand-me down :).

As a bit more background this file is an ADP accessing a MS SQL Server database. It was originally done in Access but we converted it so that we can eventually use it on the web. I've gotten rid of most of the conversion issues including getting the forms to work properly and all I have left is these pesky queries/reports. Apparently access had no problem using BETWEEN with two nvarchar values but MS SQL seems to be a bit pickier.

Examples of @.From and @.To I've been trying to use are:
@.From : CF1968.5
@.To : CF1968.20

I would expect this to return records with the following Accession numbers:

Awwww crap!

Okay now I feel really stupid. When I did the sort I noticed that CF1968.20 actually comes before CF1968.5. It puts it in alphabetical order after all, not numerical. So when I redid the query between CF1968.5 and CF1968.505 the query returned several values.

Well thanks a lot for your help. Your requests were instrumental in solving my problem for me.

Have a great day.|||glad I could help...

Oh, and I feel for you...just got 1 dumped on me...luckily they don't want to convert...

touching any code in this thing would cause MAJOR explosions...

Keeping it afloat...that's another matter...