| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle sql vs database's sql
In article <11b2fa20.3ce007f9_at_usw-ex0103-018.remarq.com>,
krystd <dennis.krystowiakNOdeSPAM_at_coastalcorp.com.invalid> wrote:
> This group by query works in adaptive server and ms sql server
> but does appear to work in oracle. I have a work around in the
> second query with subqueries but am wonder why it won't work in
> oracle(NEW ORACLE USER)
>
the question should be:
"why is sybase and MS giving me the wrong answer to this query?"
When you group by x, y, z (and you grouped by record start date, consider it X for a moment), then by definition in a having clause
X = min(x) = max(x) = sum(x) = avg(x)
You grouped by it -- its unique already -- its max is its min is its avg is its sum.
Look at a small example:
scott_at_8i> select deptno from emp group by deptno HAVING deptno = max (deptno);
DEPTNO
10
20
30
that has to return all deptnos since after GROUPING by deptno (and thats when a having clause is evaluated -- after the GROUPING), the max (deptno) is by definition deptno!
The following shows that:
Elapsed: 00:00:00.02
scott_at_8i> select deptno, max(deptno) from emp group by deptno;
DEPTNO MAX(DEPTNO)
---------- -----------
10 10
20 20
30 30
Elapsed: 00:00:00.00
scott_at_8i>
the having clause is just a where clause that gets evaluated AFTER the group by. If you aggregate X and group by X, X = agg(X).
the correct query that should work in all databases could be:
select LOCATION_NUMBER,
TO_NUMBER('0')lo_loc_legacy_loc_id,
TO_CHAR('')name,
TO_CHAR('')legal_desc_st_name_abbrev,
TO_CHAR('')state_parish,
RECORD_START_DATE,
STD_ENERGY,
GMS_DATE
WHERE ( LOCATION_NUMBER IN(36000510,37035669 )
AND ( ACCOUNTING_DATE) like '01-JUN-00%' )
AND ( record_start_date =
( select max(record_start_date)
from gms_ff_location_daily b
WHERE ( LOCATION_NUMBER = a.location_number
and ( ACCOUNTING_DATE = a.accounting_date )
)
Cause it sounds like you want the rows where:
I don't like the looks of:
AND ( ACCOUNTING_DATE) like '01-JUN-00%' )
that looks like a "bug" waiting to happen. If you are using the default date make of dd-mon-yy, the like is not needed but comparing a DATE to a string can be dangerous unless to TO_CHAR() the date or TO_DATE the string -- implicit conversions can *burn* you, especially with 1999-2000 data
> Connected to:
> Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production
> With the Partitioning and Parallel Server options
> PL/SQL Release 8.0.5.1.0 - Production
>
> SQL> SELECT FCOWNER.GMS_FF_LOCATION_DAILY.LOCATION_NUMBER,
> 2 TO_NUMBER('0')lo_loc_legacy_loc_id,
> 3 TO_CHAR('')name,
> 4 TO_CHAR('')legal_desc_st_name_abbrev,
> 5 TO_CHAR('')state_parish,
> 6 FCOWNER.GMS_FF_LOCATION_DAILY.RECORD_START_DATE,
> 7 FCOWNER.GMS_FF_LOCATION_DAILY.STD_ENERGY,
> 8 FCOWNER.GMS_FF_LOCATION_DAILY.GMS_DATE
> 9 FROM FCOWNER.GMS_FF_LOCATION_DAILY
> 10 WHERE ( FCOWNER.GMS_FF_LOCATION_DAILY.LOCATION_NUMBER IN
> (36000510,37035669 ) AND
> 11 (FCOWNER.GMS_FF_LOCATION_DAILY.ACCOUNTING_DATE) like
> '01-JUN-00%' )
> 12 GROUP BY FCOWNER.GMS_FF_LOCATION_DAILY.LOCATION_NUMBER,
> 13
> FCOWNER.GMS_FF_LOCATION_DAILY.RECORD_START_DATE,
> 14 FCOWNER.GMS_FF_LOCATION_DAILY.STD_ENERGY,
> 15 FCOWNER.GMS_FF_LOCATION_DAILY.GMS_DATE
> 16 HAVING FCOWNER.GMS_FF_LOCATION_DAILY.RECORD_START_DATE =
> 17 MAX (FCOWNER.GMS_FF_LOCATION_DAILY.RECORD_START_DATE);
>
> LOCATION_NUMBER LO_LOC_LEGACY_LOC_ID N L S RECORD_ST STD_ENERGY
> GMS_DATE
> ---------------- -------------------- - - - --------- ----------
> ---------
> 036000510 0 01-JUN-00 26209.467
> 01-JUN-00
> 036000510 0 02-JUN-00 13415.498
> 02-JUN-00
> 036000510 0 03-JUN-00 13194.504
> 03-JUN-00
> 036000510 0 04-JUN-00 21020.635
> 05-JUN-00
> 036000510 0 05-JUN-00 30246.351
> 05-JUN-00
> 036000510 0 06-JUN-00 29666.644
> 06-JUN-00
> 036000510 0 07-JUN-00 1254.6202
> 07-JUN-00
> 037035669 0 01-JUN-00 8866.8745
> 01-JUN-00
> 037035669 0 02-JUN-00 8275.8654
> 02-JUN-00
> 037035669 0 03-JUN-00 8590.3228
> 03-JUN-00
> 037035669 0 04-JUN-00 8707.0074
> 04-JUN-00
> 037035669 0 05-JUN-00 8539.2562
> 06-JUN-00
> 037035669 0 06-JUN-00 8437.5946
> 06-JUN-00
> 037035669 0 07-JUN-00 340.3588
> 07-JUN-00
>
> 14 rows selected.
>
> SQL> SELECT FCOWNER.GMS_FF_LOCATION_DAILY.LOCATION_NUMBER,
> 2 TO_NUMBER('0')lo_loc_legacy_loc_id,
> 3 TO_CHAR('')name,
> 4 TO_CHAR('')legal_desc_st_name_abbrev,
> 5 TO_CHAR('')state_parish,
> 6 FCOWNER.GMS_FF_LOCATION_DAILY.RECORD_START_DATE,
> 7 FCOWNER.GMS_FF_LOCATION_DAILY.STD_ENERGY,
> 8 FCOWNER.GMS_FF_LOCATION_DAILY.GMS_DATE
> 9 FROM FCOWNER.GMS_FF_LOCATION_DAILY
> 10 WHERE
> 11 ( FCOWNER.GMS_FF_LOCATION_DAILY.LOCATION_NUMBER IN
> (36000510,37035669) AND
> 12 TRUNC(FCOWNER.GMS_FF_LOCATION_DAILY.ACCOUNTING_DATE) =
> '01-JUN-00' AND
> 13 FCOWNER.GMS_FF_LOCATION_DAILY.RECORD_START_DATE =
> 14 (SELECT MAX
> (FCOWNER.GMS_FF_LOCATION_DAILY.RECORD_START_DATE)
> 15 FROM FCOWNER.GMS_FF_LOCATION_DAILY
> 16 WHERE FCOWNER.GMS_FF_LOCATION_DAILY.LOCATION_NUMBER IN
> (36000510,37035669) AND
> 17 TRUNC
> (FCOWNER.GMS_FF_LOCATION_DAILY.ACCOUNTING_DATE) = '01-JUN-00'
> 18 ));
>
> LOCATION_NUMBER LO_LOC_LEGACY_LOC_ID N L S RECORD_ST STD_ENERGY
> GMS_DATE
> ---------------- -------------------- - - - --------- ----------
> ---------
> 036000510 0 07-JUN-00 1254.6202
> 07-JUN-00
> 037035669 0 07-JUN-00 340.3588
> 07-JUN-00
>
> SQL>
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion
Network *
> The fastest and easiest way to search and participate in Usenet -
Free!
>
>
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Wed Jun 07 2000 - 00:00:00 CDT
 |
 |