Posted by MGFoster on 02/01/06 00:32

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't use an expression (the CASE expression) from the SELECT clause
as part of the WHERE clause criteria, 'cuz the WHERE criteria is
evaluated before the SELECT columns are returned. Therefore, use a date
column in the WHERE criteria. E.g.:

WHERE end_date >= '2006-01-31 14:01:27'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9/lCIechKqOuFEgEQKbHwCfaitFAiSAnc7qDOuopSIj1cJpw98AoPx4
W9a72aZdML7ftMsgIKIZynqF
=dwNQ
-----END PGP SIGNATURE-----



jerball wrote:
> The situation is that I have a query where a [thing] is supposed to
> end, in the sense that I don't want it to be pulled back in the query,
> at a certain time (end_date), but that is determined by the timezone
> that [thing] is located in (end_timezone).
>
> Basically, I want to say:
> 1) If the current record has a timezone not equal to the current time
> zone, which is mountain, then change the end_date being pulled back to
> reflect the time zone
> 2) Then, only pull back records that are scheduled to end before that
> time.
>
> I know this statement isn't valid, but I'll post it here to try to
> convey what I'm attempting:
>
> select end_date, end_timezone,
> CASE end_timezone
> WHEN 'ET' THEN DATEADD(hh, 2, end_date)
> WHEN 'CT' THEN DATEADD(hh, 1, end_date)
> WHEN 'PT' THEN DATEADD(hh, -1, end_date)
> ELSE end_date
> END AS theEndDate
> from offers
> where end_timezone = 'PT'
> and theEndDate >= {ts '2006-01-31 14:01:27'}
>
> Of course, this statement fails. Any suggestions on how to do what I'm
> trying to do?

[Back to original message]