SQL Query with some problems

[lrod]

I'm having an issue with this query. I'm basically running a query where i show two fields using two tables. One will give me the amount and the second one will give me the description. However in my "Where" clause i'm filtering that shows the ones that doesn't have '0' as amount. All that is good, but it comes complicate when i have to filter more descriptions if the order has a specific client number, when this happened then i have to take out the amount that's less than '0'. This is what i did but when VB runs it, it tells me that is an incorrect use of Null, but my goal is basically not to show it but i don't know any other way then i just show it as null. Here is my example:

select
(case when (left(s.client_id,5) = 504 and b.detail_type_cd = 119) then null else b.amount_nr end) Amount,
(case when (left(s.client_id,5) = 504 and b.detail_type_cd = 119) then null else c.detail_type_ds  end) item_desc
from t_order a with(nolock) 
left outer join t_detail b       on a.order_id = b.order_id
left outer join lu_detail_type c on b.order_detail_type_cd = c.detail_type_cd
left outer join t_client_id d    on a.order_id = d.order_id
Where 
IsNull(b.amount_nr, 0) <> 0 
And a.order_id = 100000 
 

Like i said what i need is that if the clien# 504 and the detail type is 119 then the amount_nr > 0 else amount<>0.

Any help will be good. Thanks in advanced.

[ck9663]

Build your query dynamically. If your GUI has a client#, build your where with client# on it. If not, use default (amount <> 0).

-- CK

[lrod]

Thanks CK, I actually was able to fix it by modifying the CASE and adding it the where clause.
Thank you for your input.

LRod