Need help with Oracle SQL SubQuery ORA-00928: missing SELECT keyword
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-ddhh24: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-ddhh24: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-ddhh24:
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-ddhh24:
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