Re: SQL differences between 8i and 9i
From: Jim Kennedy <kennedy-down_with_spammers_at_attbi.com>
Date: Thu, 12 Jun 2003 12:39:36 GMT
Message-ID: <cw_Fa.1209962$F1.143157_at_sccrnsc04>
Date: Thu, 12 Jun 2003 12:39:36 GMT
Message-ID: <cw_Fa.1209962$F1.143157_at_sccrnsc04>
is valuedate a date field?(and if it is a string field with a date in it
then double ugh) If so don't compare dates and strings. Dates are dates
and strings are strings. If you are relying on such comparison (dates vs
strings) then you are depending upon some default date format specification.
(which is probably why it does not work) Instead use to_date on the strings
eg
to_date('20060507','yyyymmdd')
> VALUEDATE >= to_date('20000613','yyyymmdd') AND VALUEDATE <=
to_date('20060507','yyyymmdd'),
...
Jim
-- Replace part of the email address: kennedy-down_with_spammers_at_attbi.com with family. Remove the negative part, keep the minus sign. You can figure it out. "Tom Geyzen" <tomgeyzen_at_hotmail.com> wrote in message news:2c8b4611.0306120056.21808339_at_posting.google.com...Received on Thu Jun 12 2003 - 14:39:36 CEST
> Hi,
>
> I've got an application running on Oracle 9i which used nested queries
> with aggregate functions. I now have to make the app run on Oracle 8.
>
> This kind of query doesn't run anymore :
>
> SELECT SUM(Count) AS GRAND_Total,
> (SELECT SUM(Count) AS Total_OK FROM TABLE1 WHERE STATUS = 'OK' AND
> VALUEDATE >= '20000613' AND VALUEDATE <= '20060507'),
> (SELECT SUM(Count) AS Total_SKIP FROM TABLE1 WHERE STATUS = 'SKIP' AND
> VALUEDATE >= '20000613' AND VALUEDATE <= '20060507'),
> (SELECT SUM(Count) AS Total_HIT FROM TABLE1 WHERE STATUS <> 'OK' AND
> STATUS <> 'SKIP' AND VALUEDATE >= '20000613' AND VALUEDATE <=
> '20060507')
> FROM TABLE1 WHERE VALUEDATE >= '20000613' AND VALUEDATE <=
> '20060507'GROUP BY 1
>
>
> It runs fine on 9i...
>
> Any advice on how to convert this to work under 8i would be
> appreciated ...
>
> tia,
>
> Tom