Need help with Oracle SQL SubQuery ORA-00928: missing SELECT keyword

From: mrpauly <paul.millard_at_fedex.com>
Date: 6 Jun 2003 08:53:46 -0700
Message-ID: <17571d1c.0306060753.1e399def_at_posting.google.com>


Hi,

I'm having a rough time figuring out to create an outer query on the SQL query below. From lines 5 through 21, I wrote a subquery to get the total number of bills for all months and place it in each record. This works fine (as shown below) but when I want to create another select outer query such as Select Mth, Bills, AllBills from (code below) I receive a ORA-00928: missing SELECT keyword. I need to do an outer query because I want to take the fields created and perform calculations. I understand my problem has to do with the inner subquery (lines 5 - 21) because I can take it out and everything works. If anyone has a suggestion that would be great. Two examples below shows a working and non-working query.

Thanks,
Paul Millard

This code works:
 1 SELECT
 2 t7.dw_month Mth,
 3 Count(t1.mai_fb_no) Bills,
 4 /*Total for all months*/
 5 Min((
 6 SELECT
 7 Count(t1.mai_fb_no) Pro
 8 FROM
 9 mai_shipment t1,

10  dw_fb_attribute t2,
11  dw_customer t3,
12  dw_date t7

13 WHERE
14  t1.mai_fb_attribute_key=t2.dw_fb_attribute_key and
15  t1.mai_shipper_key=t3.dw_cust_key and
16  t1.mai_dl_date_key=t7.dw_date_key and
17  t2.dw_fb_carrier_scac_code in ('VIKN', 'FXFW') and
18  t7.dw_day_date between to_date('2003-03-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') and
19 to_date('2003-05-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and 20 T3.DW_CUST_OLD_NUMBER In ('KKL2712SKM', '', ''))) AllBills 21 /*End All Bills*/
22 FROM
23 mai_shipment t1,
24  dw_fb_attribute t2,
25  dw_customer t3,
26  dw_date t7

27 WHERE
28  t1.mai_fb_attribute_key=t2.dw_fb_attribute_key and
29  t1.mai_shipper_key=t3.dw_cust_key and
30  t1.mai_dl_date_key=t7.dw_date_key and
31  t2.dw_fb_carrier_scac_code in ('VIKN', 'FXFW') and
32  t7.dw_day_date between to_date('2003-03-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') and
33 to_date('2003-05-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and 34 T3.DW_CUST_OLD_NUMBER In ('KKL2712SKM', '', '') 35 group by
36* t7.dw_month
QL> /

      MTH BILLS ALLBILLS
--------- ---------- ----------

        3         53        122
        4         39        122
        5         30        122

This code does not work:
  1 SELECT
  2 Mth,
  3 Bills,
  4 AllBills
  5 from
  6 (
  7 SELECT
  8 t7.dw_month Mth,
  9 Count(t1.mai_fb_no) Bills,
 10 /*Total for all months*/
 11 Min((
 12 SELECT
 13 Count(t1.mai_fb_no) Pro
 14 FROM
 15 mai_shipment t1,

 16  dw_fb_attribute t2,
 17  dw_customer t3,
 18  dw_date t7

 19 WHERE
 20  t1.mai_fb_attribute_key=t2.dw_fb_attribute_key and
 21  t1.mai_shipper_key=t3.dw_cust_key and
 22  t1.mai_dl_date_key=t7.dw_date_key and
 23  t2.dw_fb_carrier_scac_code in ('VIKN', 'FXFW') and
 24  t7.dw_day_date between to_date('2003-03-01 00:00:00', 'yyyy-mm-dd
hh24:
 25 to_date('2003-05-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and  26 T3.DW_CUST_OLD_NUMBER In ('KKL2712SKM', '', ''))) AllBills  27 /*End All Bills*/
 28 FROM
 29 mai_shipment t1,
 30  dw_fb_attribute t2,
 31  dw_customer t3,
 32  dw_date t7

 33 WHERE
 34  t1.mai_fb_attribute_key=t2.dw_fb_attribute_key and
 35  t1.mai_shipper_key=t3.dw_cust_key and
 36  t1.mai_dl_date_key=t7.dw_date_key and
 37  t2.dw_fb_carrier_scac_code in ('VIKN', 'FXFW') and
 38  t7.dw_day_date between to_date('2003-03-01 00:00:00', 'yyyy-mm-dd
hh24:
 39 to_date('2003-05-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and  40 T3.DW_CUST_OLD_NUMBER In ('KKL2712SKM', '', '')  41 group by
 42 t7.dw_month
 43* )
SQL> /
SELECT
*
ERROR at line 1:
ORA-00928: missing SELECT keyword Received on Fri Jun 06 2003 - 17:53:46 CEST

Original text of this message