Items that are filled in

Hello,

AND b.fired_day LIKE (CASE WHEN @.type = 'b' THEN '%' WHEN @.type= 'l' THEN '-1' WHEN @.type= 'o' THEN '0' END)

I want the following thing:

When type= b, then show everything

When type=l, then show the items that aren't filled in

When type=o, then show the items that are filled in

What's wrong with my code? (b.fired_day is a date)

I am assuming the '-1' and '0' clauses are coming from ACCESS.

I think to achieve what you want in SQL server you either need to write a dynamic SQL query or an if-then-else statement like below:

IF @.type = 'b'
BEGIN
select * from TABLE
END

ELSE IF (@.type = 'l')
BEGIN
select * from TABLE
where fired_day IS NULL
END

ELSE
BEGIN
select * from TABLE
where fired_day IS NOT NULL
END

woeter wrote:

Hello,

AND b.fired_day LIKE (CASE WHEN @.type = 'b' THEN '%' WHEN @.type= 'l' THEN '-1' WHEN @.type= 'o' THEN '0' END)

I want the following thing:

When type= b, then show everything

When type=l, then show the items that aren't filled in

When type=o, then show the items that are filled in

What's wrong with my code? (b.fired_day is a date)

|||Thanks for the answer,but isn't there a possibility to wright this in one line? I must use it a couple of times!|||

Well this is the closest to your request I could come.... Let me know if you work out something better:

select * from tblName
where ISNULL(CAST(fired_day as varchar),'-1') LIKE (CASE WHEN @.type = 'b' THEN '%' WHEN @.type = 'l' THEN '-1' END)
OR fired_day LIKE (CASE WHEN @.type = 'o' THEN '%' END)

woeter wrote:

Thanks for the answer,but isn't there a possibility to wright this in one line? I must use it a couple of times!

|||But when the type=b then everything should be shown, also the null fields.|||

Have you tried the code....

the code accomplishes all req...

woeter wrote:

But when the type=b then everything should be shown, also the null fields.