By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

SQL Query with some problems

P: 7
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:
Expand|Select|Wrap|Line Numbers
  1. select
  2. (case when (left(s.client_id,5) = 504 and b.detail_type_cd = 119) then null else b.amount_nr end) Amount,
  3. (case when (left(s.client_id,5) = 504 and b.detail_type_cd = 119) then null else c.detail_type_ds  end) item_desc
  4. from t_order a with(nolock) 
  5. left outer join t_detail b       on a.order_id = b.order_id
  6. left outer join lu_detail_type c on b.order_detail_type_cd = c.detail_type_cd
  7. left outer join t_client_id d    on a.order_id = d.order_id
  8. Where 
  9. IsNull(b.amount_nr, 0) <> 0 
  10. And a.order_id = 100000 
  11.  
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.
Mar 9 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Build your query dynamically. If your GUI has a client#, build your where with client# on it. If not, use default (amount <> 0).

-- CK
Mar 10 '09 #2

P: 7
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
Mar 11 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.