Using Case to prepare a dynamic where clause

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 2000 Forums

SQL Server Development (2000)

Using Case to prepare a dynamic where clause

Author

Topic

peddi_praveen
Starting Member

48 Posts

Posted - 2002-11-07 : 04:12:05

Hi guys,
I have a table like this
Create Table table1
( ColPK INT,
ColDate1 DATETIME,
ColPeriod INT,
ColDate2 DATETIME
)

i need to retrieve the records from this table which meet following criteria
1. if colperiod column is not null, system date should be greater than or equal to coldate1+colperiod
2. if colperiod column is null, system date should be less than or equal to coldate2

I did this using OR condition , but I failed to do the same using CASE in where clause
I tried following statement:
Select *
From Table1
Where CASE colperiod
WHEN IS NOT NULL dateadd(day,colperiod,coldate1)>= getdate()
ELSE coldate2 >= getdate()
END

its causing syntax error. Want to know whether it can b done in this manner or not, if it can b done, help me in solving this

Thanks in advance,
Praveen

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-07 : 04:18:44

I think that the OR condition is the way to go, though I have a feeling that you can use the CASE statement in the WHERE clause but it would need to be on the right hand side of the comparison operator.

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-11-07 : 04:20:57

Hmmm... I think your result expression(THEN) is missing.

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-07 : 04:25:30

quote:

Hmmm... I think your result expression(THEN) is missing.

yes the CASE is badly formed, but even correctly formed it will not work.

mohamedyousuff@yahoo.com
Starting Member

24 Posts

Posted - 2002-11-07 : 04:50:16

I could not solve the problem your way using CASE. But I think the following query solves the problem.

select * from table1
where
( colperiod is not null and getdate() > (coldate1 + colperiod) )
or
( colperiod is null and getdate() <= (ColDate2) )

quote:

Hi guys,
I have a table like this
Create Table table1
( ColPK INT,
ColDate1 DATETIME,
ColPeriod INT,
ColDate2 DATETIME
)

i need to retrieve the records from this table which meet following criteria
1. if colperiod column is not null, system date should be greater than or equal to coldate1+colperiod
2. if colperiod column is null, system date should be less than or equal to coldate2

I did this using OR condition , but I failed to do the same using CASE in where clause
I tried following statement:
Select *
From Table1
Where CASE colperiod
WHEN IS NOT NULL dateadd(day,colperiod,coldate1)>= getdate()
ELSE coldate2 >= getdate()
END

its causing syntax error. Want to know whether it can b done in this manner or not, if it can b done, help me in solving this

Thanks in advance,
Praveen

Nazim
A custom title

1408 Posts

Posted - 2002-11-07 : 05:18:56

Select *
From Table1
Where getdate() >= CASE WHEN colperiod IS NOT NULL then
(colperiod+coldate1)
END or getdate() <= CASE WHEN colperiod IS NULL then
coldate2 end

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Edited by - Nazim on 11/07/2002 05:22:13

peddi_praveen
Starting Member

48 Posts

Posted - 2002-11-11 : 23:32:35

Thank U All for ur suggestions.
Hi Nazim,
Your solution is somewhat closer to wwhat im looking for. but, sql statement ignores indexes on column while fetching records> SO, i opted traditional way of doing with "OR" condition.

Thank U all once again,
Praveen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources