syntax - 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

syntax

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-06 : 09:47:56

what did i have wrong from this syntax, 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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-06 : 10:18:17

The syntax and the structure are incorrect. Is this part of a query, or is this your whole query? If it is the whole query, the alias/table i is not defined. The use of case expression is incorrect.

If you can describe what you are trying to accomplish, it would be easier to suggest how that needs to be structured.

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-06 : 10:33:37

this is just a part of the query, because i have a very long query , but this is the only part gave me an error.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-06 : 11:09:58

Te second case expression is okay as a column in a select list. Just add a THEN keyword:

case when ic.cost.t_amount = 0 THEN 'No' else 'Yes' end as cost,

By the way, what are the 3 parts in i.cust.type? Are they schema name, table name and column name, respectively? In other words, is your table/table alias cust?

I am not able to offer much help for code within the first case expression because I am not able to discern what you are trying to do based on what you have posted so far.

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-06 : 11:31:45

thanks,

hope you can help me with this, thank you

select stim from tbell
where item in
(
select b.stim
from ibd itm
left join bocm b
on itm.item = b.stim
where type =2
and ltrim(item) ='040500-21204993'
)

This query return 3 values:
ABC
DEF
GHJ

I now want to check to see if what value exists in the roudel table with the query below then got the one 1 record (ABC). I now want to check for not eixsts (run the same query but replace first in by not in, then gosh, it lised the entired DB instead of list only DEF and GHJ.

select r.mitm from roudel rou
where rou.mitm
in
(

select stim from tbell
where item in
(
select b.stim
from ibd itm
left join bocm b
on itm.item = b.stim
where type =2
and ltrim(item) ='040500-21204993'
)

)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-06 : 12:50:40

That sounds like you want to reverse the logic, like shown below:

SELECT  stim
FROM    tbell
WHERE   item IN ( SELECT    b.stim
                  FROM      ibd itm
                            LEFT JOIN bocm b ON itm.item = b.stim
                  WHERE     type = 2
                            AND LTRIM(item) = '040500-21204993' )
	AND item NOT IN( SELECT rou.mitm FROM roudel rou)

Be aware that if you have nulls in the mitm column, the results may not be what you expect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources