Oracle sql vs database's sql

From: krystd <dennis.krystowiakNOdeSPAM_at_coastalcorp.com.invalid>
Date: 2000/06/07
Message-ID: <11b2fa20.3ce007f9@usw-ex0103-018.remarq.com>#1/1

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)

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!

Received on Wed Jun 07 2000 - 00:00:00 CDT