Can case be used in advance query?

Can case be used in advance query?

CASE are like if statement , for example CASE expression WHEN expression1 THEN expression1 [[WHEN expression2 THEN expression2] [...]] [ELSE expressionN] END
Hi Robert,

Yes you can use case statement. I have used them quite often.
Beware you cannot switch from database that easily anymore, since it's db-specific language ofcourse.

example snippet:
		, SUM( NVL( CASE WHEN ( @inColumnTimestamp < @inStartDateTime + 1) THEN 1 ELSE 0 END, 0)) Vandaag
		, SUM( NVL( CASE WHEN ( @inColumnTimestamp < @inStartDateTime) THEN 1 ELSE 0 END, 0)) Gisteren
		, SUM( NVL( CASE WHEN ( @inColumnTimestamp < @inStartDateTime - 1) THEN 1 ELSE 0 END, 0)) Eergisteren
		, SUM( NVL( CASE WHEN ( @inColumnTimestamp < @inStartDateTime - 2) THEN 1 ELSE 0 END, 0)) DagMin3
		, SUM( NVL( CASE WHEN ( @inColumnTimestamp < @inStartDateTime - 3) THEN 1 ELSE 0 END, 0)) DagMin4
		, SUM( NVL( CASE WHEN ( @inColumnTimestamp < @inStartDateTime - 4) THEN 1 ELSE 0 END, 0)) DagMin5
		, SUM( NVL( CASE WHEN ( @inColumnTimestamp < @inStartDateTime - 5) THEN 1 ELSE 0 END, 0)) DagMin6
Hi Joost

Thanks for the response and example.

I wonder if there is an alternative solution to the following problem.....

The database tables used in our systems are all soft deletes, and deleted records are flagged by "DeletedDate" (Datatype: datetime).
Where DeletedDate has been assigned a date value, it means the record has been deleted, otherwise DeletedDate is null and that means the record has not been deleted.

Now we need to filter the deleted records....

Example: Get user email list, retrieve user's email by filter
-FilterType (0 for all, 1 for active only [DeletedDate=NullDate()], 2 for deleted only [DeletedDate<>Nulldate()])

We simply used a switch statement, and 3 simple queries, could we use simple query and somehow optimise it?

not sure what you mean by optimizing?
If you mean, reduce the number of queries, I say, use an advanced query.
and the filter-clause will be an expand-inline parameter.
Hi Robert,

I would try it on the the simple query and do an execution plan to see how the sql server optimizes it (or not)

The Simple Query condition would be just something like:

FilterType = 0 Or (FilterType = 1 And DeletedDate=NullDate()) Or (FilterType = 2 And DeletedDate<>NullDate())

Btw on a side note. I'm looking at the Case specification and I don't actually see any difference between Oracle and Sql Server specification, sure is DBMS specific?
On the other end NVL is specific (being the mssql equivament the ISNULL), and both can be easily replaced by COALESCE that does the same and is standard. :)

João Rosado
Thanks alot Joao

Your suggestion is useful.