We help IT Professionals succeed at work.

ternary operator in t-sql sybase

ol muser
ol muser used Ask the Experts™
on
I have a query that looks as below:

select distinct
--some columns
        from some_table
        where EXPR <= LastChangedDate

The way I would code the EXPR in C++ is

      startdate < lastdocdate ? startdate : lastdocdate

How can this be expresses in t-sql for sybase?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Commented:
I don't know C++... what does your expression do? Can you write it in words?
Hi ,

I think he means the following

if start date is less than lastdocdate then check whether LastChangedDate  is >= start date
else
check whether LastChangedDate  is >= lastdocdate

One possible soultion is to use a case statement

See below example  :

1> drop table t
2> go
1> create table t
2> ( a int NULL,
3>   startdate datetime NULL,
4>   lastdocdate datetime NULL,
5>   LastChangedDate datetime NULL
6>  )
7> go
1> insert into t values( 1,'20110101','20110110' ,'20100101')
2> go
(1 row affected)
1> insert into  t values ( 2,'20110101','20110110' ,'20110102')
2> go
(1 row affected)
1> insert into  t values ( 3,'20110110','20110101' ,'20100101')
2> go
(1 row affected)
1> insert into  t values ( 4,'20110110','20110101' ,'20110102')
2> go
(1 row affected)
1> select * from t
2> go
 a           startdate                  lastdocdate                LastChangedDate
 ----------- -------------------------- -------------------------- --------------------------
           1        Jan  1 2011 12:00AM        Jan 10 2011 12:00AM        Jan  1 2010 12:00AM
           2        Jan  1 2011 12:00AM        Jan 10 2011 12:00AM        Jan  2 2011 12:00AM
           3        Jan 10 2011 12:00AM        Jan  1 2011 12:00AM        Jan  1 2010 12:00AM
           4        Jan 10 2011 12:00AM        Jan  1 2011 12:00AM        Jan  2 2011 12:00AM

(4 rows affected)

1> select *
2> from t
3> where LastChangedDate  >= case when startdate < lastdocdate then startdate
4>                                            else      lastdocdate  end
5> go
 a           startdate                  lastdocdate                LastChangedDate
 ----------- -------------------------- -------------------------- --------------------------
           2        Jan  1 2011 12:00AM        Jan 10 2011 12:00AM        Jan  2 2011 12:00AM
           4        Jan 10 2011 12:00AM        Jan  1 2011 12:00AM        Jan  2 2011 12:00AM

(2 rows affected)
1>

HTH

Thanks
Maneksh