Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Need help with Oracle SQL SubQuery ORA-00928: missing SELECT keyword
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
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
24 dw_fb_attribute t2, 25 dw_customer t3, 26 dw_date t7
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
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
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:
30 dw_fb_attribute t2, 31 dw_customer t3, 32 dw_date t7
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: