OK this is driving me absolutely crazy, whats wrong with this code:

select
          <somestuff>
from
           <sometable>
where
    case  when @option = 'greater' then
        air.flag is null
        and itin.flag is null
        and air.rec_loc > 'AAAAAA'
        and air.rec_loc = itin.rec_loc
        and air.segment + 1 = itin.segment
        and itin.depart - air.arive > cast(cast( cast( @minutes as int)*18000
as binary) as datetime)
        and itin.depart - air.arive < '1900-01-02 00:00:00.000'
        and air.arive > getDate()
        and air.city_to = itin.city_from
        and info.year in (select travel_year from sys_Info)
        and info.dl_id = itin.dl_id
    else
        air.flag is null
        and itin.flag is null
        and air.rec_loc > 'AAAAAA'
        and air.rec_loc = itin.rec_loc
        and air.segment + 1 = itin.segment
        and itin.depart - air.arive < cast(cast( cast( @minutes as int)*18000
as binary) as datetime)
        and itin.depart - air.arive < '1900-01-02 00:00:00.000'
        and air.arive > getDate()
        and air.city_to = itin.city_from
        and info.year in (select travel_year from sys_Info)
        and info.dl_id = itin.dl_id
    end

When I run it I get a "Incorrect syntax near the keyword 'and'." error,
the "and" it's refering to is the last "and" statement. Can someone
shed some light on this, have I missed something?

In SQL, CASE is a function that returns a value, not a condition.  So
something like
Where a = Case When b=5 Then 23 Else 17 End
is legal, but
Where Case When a = 6 Then b is Null Or c = 17 Else b = 5 And C is Null End
is illegal.

So you could do something like (assuming that the two values allowed in
@option are 'greater' and 'smaller'):

select
          somestuff
from
           sometable
where
air.flag is null
and itin.flag is null
and air.rec_loc > 'AAAAAA'
and air.rec_loc = itin.rec_loc
and air.segment + 1 = itin.segment
and itin.depart - air.arive < '1900-01-02 00:00:00.000'
and air.arive > getDate()
and air.city_to = itin.city_from
and info.year in (select travel_year from sys_Info)
and info.dl_id = itin.dl_id
and @option = case
  when itin.depart - air.arive > cast(cast( cast( @minutes as int)*18000
      as binary) as datetime) then 'greater'
  when itin.depart - air.arive < cast(cast( cast( @minutes as int)*18000
      as binary) as datetime) then 'smaller'
  else Null
  end

Or you could use an If statement to split this into two queries, e.g.,

If @option = 'greater'
  select somestuff ...
Else
  select somestuff ...

BTW, I would normally use the Date functions like DateDiff, etc, instead of
subtracting two dates.

Tom

Show quoteHide quote

Psycho..

CASE returns a scalar value -not a part of the command statement. You can't
use it in the manner that you have. (You could return a string if you were
concatenating a SQL statement to execute using Dynamic SQL.

I noticed that the only difference between the two WHERE clauses is the use
of [>] and [<]. So, I assume that you are attempting to 'switch' the use of
the operator.

You may be best served to have two complete SELECT statements, the correct
one executed by a switch on @Option. (That bias due to my reluctance to
recommend dynamic SQL for several reasons. But Dynamic SQL may solve your
problem. See: http://www.sommarskog.se/dynamic_sql.html )

Show quoteHide quote

As the other alluded to, ...... CASE is not the same type of beast that
exists in vb.net or c#.

Its more of an "in line" thing, where I can check one value or condition, to
give me a value of a column in a query.

For one option for dealing with the where clause, check this article.

http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp


Show quoteHide quote

Marking (Not just Finding) Duplicate Values ......
Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string