case syntax help !!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

case syntax help !!!

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-05 : 10:57:32

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

737 Posts

Posted - 2013-09-05 : 11:04:36

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

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-05 : 12:01:54

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

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-09-05 : 12:04:55

case when amount=0 then 'No' else 'Yes' end as columnName

mohammad.javeed.ahmed@gmail.com

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-05 : 12:18:40

oh yeah, forgot that, thank you verymuch !!!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-09-05 : 12:19:27

you are welcome

mohammad.javeed.ahmed@gmail.com

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-09-05 : 14:05:49

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.

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-05 : 14:21:43

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources