• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

MS SQL Syntax

This should be an easy one for you guys.

i have the following sql statement:
Select x,y,z
FROM vw
WHERE el_id IN ('192')   AND (case when vw1.status = 'Shipped'
then vw2.amt IS NOT NULL else 1 end)


I am trying to use a case statement in my where clause but i get the error "Incorrect syntax near the keyword IS"

so is the status = 'Shipped the query should look like this:
Select x,y,z
FROM vw
WHERE el_id IN ('192')   AND vw2.amt IS NOT NULL


What am i doing wrong?
0
MoreThanDoubled
Asked:
MoreThanDoubled
  • 6
  • 3
  • 3
1 Solution
 
sdstuberCommented:
don't use case like that

Select x,y,z
FROM vw
WHERE el_id IN ('192')   AND  ((vw1.status = 'Shipped'
and vw2.amt IS NOT NULL) or (vw1.status != 'Shipped'))
0
 
sdstuberCommented:
or simpler


Select x,y,z
FROM vw
WHERE el_id IN ('192')   AND  
  (vw1.status != 'Shipped' or vw2.amt IS NOT NULL)
0
 
sdstuberCommented:
if you "really" want to use case (I wouldn't here)
the case must return a result, not another condition


Select x,y,z
FROM vw
WHERE el_id IN ('192')
AND  case when vw1.status = 'Shipped' and vw2.amt IS NOT NULL then 1
              when vw1.status != 'Shipped' then 1
              else 0
         end = 1
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MoreThanDoubledAuthor Commented:
sdstuber, i will need to use some sort of dynamic sql for my query
Below is a snippet of my TSQL:

Select x,y,z,
CASE WHEN vww.status = ''NOT SHIPPED''
THEN 0 ELSE      vw1.amt END ,
FROM vwrpt_vessel_sched
WHERE ' + @Where  +'

i will need some way to determine if i need to add "vw2.amt IS NOT NULL " because i won't always need it...is that possible?
0
 
sdstuberCommented:
if the inclusion/exclusion will be based on the contents of columns that are being pulled in the query you are constructing, then no it's not possible.  The condition isn't dynamic,  it's a static condition.

If the inclusion/exclusion will be based on criteria known prior to execution of the query then simply wrap your SQL construction in an if/else  no case needed

0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
I think the logic is:
SELECT my fields FROM my table WHERE element ID is 192 and (if Status is shipped, then amount is not null)

I know it doesn't look like it on the surface, but that is the same as:

SELECT my fields FROM my table WHERE element ID is 192 AND status is shipped AND amount is not null

which would read in sql:

SELECT x, y, z FROM vw WHERE el_id IN ('192') AND vw1.status = 'Shipped' AND vw2.amt IS NOT NULL
0
 
MoreThanDoubledAuthor Commented:
i am using a stored procedure that is pulling from views so i believe your later question applies.
can you give me an example because what i am trying to do is not working for me .
0
 
MoreThanDoubledAuthor Commented:
damerval,

if i use the query you wrote it would eliminate all the records that are "Not shipped" which is not what i want to do.

so back to my original query example:

Select x,y,z,
CASE WHEN vww.status = ''NOT SHIPPED''
THEN 0 ELSE      vw1.amt END ,
FROM vw
WHERE el_id IN ('192')   AND (case when vw1.status = 'Shipped'
then vw2.amt IS NOT NULL else 1=1 end)



so depending on the status...
if it is shipped my where clause should look like this:
where el_id IN ('192') AND vw2.amt IS NOT NULL

if it is Not Shipped my where clause should look like this:
where el_id IN('192') AND 1=1



0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Ah, okay, I missed that detail. This will return the records that are not shipped as well as those records that are shipped and have a non-null amount:

SELECT x, y, z FROM vw WHERE el_id IN ('192') AND
  ((vw1.status = 'Shipped' AND vw2.amt IS NOT NULL) OR (vw1.status = "Not shipped"))
0
 
sdstuberCommented:
based on your examples and explanations you can't do this dynamically (nor would you want to)

your choice to include or exclude the amt condition is based on the data you are querying

just use the query in http:#35739583

much simpler, and should be correct unless I'm misunderstanding the goal
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
In any event you cannot return a condition with a CASE statement. You have to return a value or an expression.
0
 
sdstuberCommented:
correct , as noted in http:#35739712

this really isn't a query that should use case
nor is it one where dynamic sql applies (at least not from anything posted thus far)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now