SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case syntax help !!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

75 Posts

Posted - 09/05/2013 :  10:57:32  Show Profile  Reply with Quote
Hi,

Please let me know what i wrong with this syntax since i kept getting an error below.

Thanks

CASE i.cust.type
WHEN 1 and sum(ic.cost.t_amount * b.qana ) =0 then 'No' Else 'Yes'
END as Cost,

CASE r001.owc
WHEN 3 and sum(ic.cost.t_amount * b.qana ) =0 then 'No' Else 'Yes'
END as purchase,

CASE i.cust.type
When 2 and r.mitm ! = ' ' then 'Yes' Else 'No'
END as Manufacture,

Error :
Msg 156, Level 15, State 1, Line 66
Incorrect syntax near the keyword 'and'.
Msg 319, Level 15, State 1, Line 82
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 09/05/2013 :  11:04:36  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
can you try this
select CASE WHEN i.cust.type=1 and sum(ic.cost.t_amount * b.qana ) =0 then 'No' Else 'Yes'
END as Cost,

CASE WHEN r001.owc=3 and sum(ic.cost.t_amount * b.qana ) =0 then 'No' Else 'Yes'
END as purchase,

CASE When i.cust.type=2 and r.mitm ! = ' ' then 'Yes' Else 'No'
END as Manufacture,

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 09/05/2013 :  12:01:54  Show Profile  Reply with Quote
worked, thank you,
I have another question hope that you can help me with.

select icpr.amount, b.sub_item
from ticprs icpr
left join bus b
on b.item = icpr.item
where b.item ='abc1235'

this query returned the results below. If one of the value in the result = 0 then i need to set the No for the whole item, else, yes. How can i do it?

amount sub_item
10.15 abc123
20.55 def456
0 hik758
8 xyz963
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 09/05/2013 :  12:04:55  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
case when amount=0 then 'No' else 'Yes' end as columnName

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 09/05/2013 :  12:18:40  Show Profile  Reply with Quote
oh yeah, forgot that, thank you verymuch !!!
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 09/05/2013 :  12:19:27  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
you are welcome

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
769 Posts

Posted - 09/05/2013 :  14:05:49  Show Profile  Reply with Quote
quote:
Originally posted by kt

worked, thank you,
I have another question hope that you can help me with.

select icpr.amount, b.sub_item
from ticprs icpr
left join bus b
on b.item = icpr.item
where b.item ='abc1235'

this query returned the results below. If one of the value in the result = 0 then i need to set the No for the whole item, else, yes. How can i do it?

amount sub_item
10.15 abc123
20.55 def456
0 hik758
8 xyz963



Note: your outer join is not working as you expect because you are also filtering (where clause) on a column from the outer table. This effectively turns the outer join into an inner join. If you do not need the outer join, change it to an inner join to be clear on the intent of the query.
Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 09/05/2013 :  14:21:43  Show Profile  Reply with Quote
why this sysntax is not working, can you please help me with this? - thank you,

CASE WHEN i.cust.type=1
(select ic.cost.t_amount , b.t_sitm from icprdel icpr left join bquant b on icpr.item = b.item)
case when ic.cost.t_amount = 0 'No' else 'Yes' end as cost
END


Edited by - kt on 09/05/2013 14:22:09
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000