CASE statement.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CASE statement..

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2009-07-23 : 09:10:27

In my query i have some thing like...

eg.

declare @city varchar(10)
@city = 'hyd'

select
*
from employees
where
salary>10000
and
empcode in('111','112','113','114')
and

if @city = 'hyd' or @city = 'bglr' or @city = 'chen'
then following condition also need to satisfy
name like 'A%' else only above condition need to satisfy for getting data from employee table...

please help on this query....

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-23 : 09:14:06

select
*
from employees
where
salary>10000
and
empcode in('111','112','113','114')
and case when @city = 'hyd' or @city = 'bglr' or @city = 'chen' then name like 'A%'
else 1=1 end

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-23 : 09:27:15

[code]DECLARE @CITY VARCHAR(100)

SELECT *
FROM EMPLOYEES
WHERE SALARY>10000
AND EMPCODE IN(111,112,113,114) --IF EMPCODE IS INT NO NEED TO PUT ''
AND
CASE
WHEN @CITY IN( 'HYD' ,
'BGLR',
'CHEN')
THEN EMPNAME LIKE 'A%'
ELSE 1=1
END

[/code]

-------------------------
R..

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2009-07-23 : 09:29:22

Thanks for reply but its displaying error msg as

Incorrect syntax near the keyword 'like'.

quote:
Originally posted by senthil_nagore

select
*
from employees
where
salary>10000
and
empcode in('111','112','113','114')
and case when @city = 'hyd' or @city = 'bglr' or @city = 'chen' then name like 'A%'
else 1=1 end

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-23 : 09:40:12

select
*
from employees
where
(salary>10000
and
empcode in('111','112','113','114')
)
and (((@city = 'hyd' or @city = 'bglr' or @city = 'chen') and name like 'A%')
or not((@city = 'hyd' or @city = 'bglr' or @city = 'chen')))

Madhivanan

Failing to plan is Planning to fail

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2009-07-23 : 10:09:21

Hi,

Its displaying result correct when @city = 'hyd' or @city = 'bglr' or @city = 'chen', but when we give other city name like @city = 'jpr' Then resultset is NULL

quote:
Originally posted by madhivanan

select
*
from employees
where
(salary>10000
and
empcode in('111','112','113','114')
)
and (((@city = 'hyd' or @city = 'bglr' or @city = 'chen') and name like 'A%')
or not((@city = 'hyd' or @city = 'bglr' or @city = 'chen')))

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-23 : 10:34:55

quote:
Originally posted by vision.v1

Hi,

Its displaying result correct when @city = 'hyd' or @city = 'bglr' or @city = 'chen', but when we give other city name like @city = 'jpr' Then resultset is NULL

quote:
Originally posted by madhivanan

select
*
from employees
where
(salary>10000
and
empcode in('111','112','113','114')
)
and (((@city = 'hyd' or @city = 'bglr' or @city = 'chen') and name like 'A%')
or not((@city = 'hyd' or @city = 'bglr' or @city = 'chen')))

Madhivanan

Failing to plan is Planning to fail

Can you post the full query you used?

Madhivanan

Failing to plan is Planning to fail

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2009-07-23 : 10:46:06

below is the original query that i used..

use dbone

DECLARE @DATABASENAME VARCHAR(50)
DECLARE @DATE VARCHAR(50)
DECLARE @FROMDATE VARCHAR(50)

SET @DATABASENAME = DB_NAME()
SET @DATE = CONVERT(VARCHAR(50),DATEADD(dd, -1 ,getdate()),101)
SELECT @FROMDATE = MIN(CONVERT(VARCHAR(50),effectiveDate,101)) FROM ctlDeliverableSplitSources WHERE effectiveDate>='01/01/2009'

SELECT
STR(sum(dailyhours)/3600.0,10,2) AS prmtableHours
FROM
prmTable
WHERE
(DATE BETWEEN @FROMDATE AND @DATE
AND
source IN(SELECT DISTINCT source FROM ctlDeliverableSplitSources WHERE effectiveDate>=@FROMDATE)
)
and
((@DATABASENAME = 'dbone' or @DATABASENAME = 'dbtwo' or @DATABASENAME = 'dbthree') and source like 'Q%')

if i execute the above query in databases dbone,dbtwo,dbthree its displaying result fine...if i execute other than that like dbfour result is NULL

Note: prmTable is present in all databases...and condition "source like 'Q%'" need to satisfy only for databases dbone, dbtwo, dbthree for all the remaining databases i want to ignore the condition "source like 'Q%'"

quote:
Originally posted by madhivanan

quote:
Originally posted by vision.v1

Hi,

Its displaying result correct when @city = 'hyd' or @city = 'bglr' or @city = 'chen', but when we give other city name like @city = 'jpr' Then resultset is NULL

quote:
Originally posted by madhivanan

select
*
from employees
where
(salary>10000
and
empcode in('111','112','113','114')
)
and (((@city = 'hyd' or @city = 'bglr' or @city = 'chen') and name like 'A%')
or not((@city = 'hyd' or @city = 'bglr' or @city = 'chen')))

Madhivanan

Failing to plan is Planning to fail

Can you post the full query you used?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-23 : 10:53:06

You didnt simulate my query fully

SELECT
STR(sum(dailyhours)/3600.0,10,2) AS prmtableHours
FROM
prmTable
WHERE
(DATE BETWEEN @FROMDATE AND @DATE
AND
source IN(SELECT DISTINCT source FROM ctlDeliverableSplitSources WHERE effectiveDate>=@FROMDATE)
)
and
(((@DATABASENAME = 'dbone' or @DATABASENAME = 'dbtwo' or @DATABASENAME = 'dbthree') and source like 'Q%')
or not ((@DATABASENAME = 'dbone' or @DATABASENAME = 'dbtwo' or @DATABASENAME = 'dbthree')))

Madhivanan

Failing to plan is Planning to fail

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2009-07-23 : 11:07:33

Thanks for reply its working fine.......:)

quote:
Originally posted by madhivanan

You didnt simulate my query fully

SELECT
STR(sum(dailyhours)/3600.0,10,2) AS prmtableHours
FROM
prmTable
WHERE
(DATE BETWEEN @FROMDATE AND @DATE
AND
source IN(SELECT DISTINCT source FROM ctlDeliverableSplitSources WHERE effectiveDate>=@FROMDATE)
)
and
(((@DATABASENAME = 'dbone' or @DATABASENAME = 'dbtwo' or @DATABASENAME = 'dbthree') and source like 'Q%')
or not ((@DATABASENAME = 'dbone' or @DATABASENAME = 'dbtwo' or @DATABASENAME = 'dbthree')))

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 02:58:55

quote:
Originally posted by vision.v1

Thanks for reply its working fine.......:)
quote:
Originally posted by madhivanan

You didnt simulate my query fully

SELECT
STR(sum(dailyhours)/3600.0,10,2) AS prmtableHours
FROM
prmTable
WHERE
(DATE BETWEEN @FROMDATE AND @DATE
AND
source IN(SELECT DISTINCT source FROM ctlDeliverableSplitSources WHERE effectiveDate>=@FROMDATE)
)
and
(((@DATABASENAME = 'dbone' or @DATABASENAME = 'dbtwo' or @DATABASENAME = 'dbthree') and source like 'Q%')
or not ((@DATABASENAME = 'dbone' or @DATABASENAME = 'dbtwo' or @DATABASENAME = 'dbthree')))

Madhivanan

Failing to plan is Planning to fail

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources