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
 syntax
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

75 Posts

Posted - 09/06/2013 :  09:47:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/06/2013 :  10:18:17  Show Profile  Reply with Quote
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.
Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 09/06/2013 :  10:33:37  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/06/2013 :  11:09:58  Show Profile  Reply with Quote
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.
Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 09/06/2013 :  11:31:45  Show Profile  Reply with Quote
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'
)

)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/06/2013 :  12:50:40  Show Profile  Reply with Quote
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.
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.09 seconds. Powered By: Snitz Forums 2000