6 Replies Latest reply: Jun 24, 2008 1:21 PM by Satyaki_De RSS

    ORA-00905: missing keyword

    Satyaki_De
      Hi!

      I'm facing some strange problem. Can anyone suggest where am i missing? Here is the script.
      satyaki>
      satyaki>ed
      Wrote file afiedt.buf
      
        1  SELECT ARPT_GATE.stn_ctl_cntr_zn as ZONE,
        2         decode('&deparrind',
        3                'A',
        4                NVL(OPS_FLT_LEG.arr_Subgate_Nbr,OPS_FLT_LEG.arr_gate_Nbr),
        5                NVL(OPS_FLT_LEG.dep_Subgate_Nbr,OPS_FLT_LEG.dep_gate_Nbr)
        6               )  as GATE,
        7         BAG_TAG_FLT_LEG.carr_iata_cd as CARRIER,
        8                  BAG_TAG_FLT_LEG.flt_nbr as FLTNO,
        9                  To_Char(BAG_TAG_FLT_LEG.flt_leg_lcl_dep_dt,'MM/DD/YYYY') as DEPDATE,
       10                  To_Char(BAG_TAG_FLT_LEG.flt_lcl_orig_dt,'MM/DD/YYYY') as FLTDATE, 
       11                  BAG_TAG_FLT_LEG.orig_arpt_cd as DEPSTA,
       12                  BAG_TAG_FLT_LEG.dest_arpt_cd as ARRSTA, 
       13         decode('&gmtlclind',
       14                'G',
       15                To_Char(SCHED_FLT_LEG.gmt_dep_dtm, 'hh24:mi:ss'),
       16                To_Char(SCHED_FLT_LEG.lcl_dep_dtm, 'hh24:mi:ss')
       17               )  as SCHDEPTIME, 
       18        decode('&gmtlclind',
       19                'G',
       20                To_Char(SCHED_FLT_LEG.gmt_arr_dtm, 'hh24:mi:ss'),
       21                To_Char(SCHED_FLT_LEG.lcl_arr_dtm, 'hh24:mi:ss')
       22              ) as SCHARRTIME,
       23        decode('&gmtlclind',
       24                'G',
       25                To_Char(NVL(OPS_FLT_LEG.gmt_out_dtm,OPS_FLT_LEG.gmt_est_dep_dtm), 'hh24:mi:ss'),
       26                To_Char(NVL(OPS_FLT_LEG.lcl_out_dtm,OPS_FLT_LEG.lcl_est_dep_dtm), 'hh24:mi:ss')
       27                ) as DEPTIME,
       28        decode('&gmtlclind',
       29                'G',
       30                To_Char(NVL(OPS_FLT_LEG.gmt_in_dtm,OPS_FLT_LEG.gmt_est_arr_dtm), 'hh24:mi:ss'),
       31                To_Char(NVL(OPS_FLT_LEG.lcl_in_dtm,OPS_FLT_LEG.lcl_est_arr_dtm), 'hh24:mi:ss')
       32              ) as ARRTIME,
       33        OPS_FLT_LEG.flt_stat as FLTSTATUS,
       34        COUNT(*) as TOTALBAGS,
       35        case 
       36          when '&deparrind' = 'D' then
       37            SUM(CASE WHEN BAG_TAG_FLT_LEG.dep_rte_type_cd = 'THRU' THEN 1 else 0 END)
       38        else
       39           SUM(CASE WHEN BAG_TAG_FLT_LEG.arr_rte_type_cd = 'THRU' THEN 1 else 0 END)
       40        end THRUBAGS,
       41        case 
       42          when '&deparrind' = 'D' then
       43            SUM(CASE 
       44                 WHEN BAG_TAG_FLT_LEG.scan_on_ind = 1 
       45                 AND BAG_TAG_FLT_LEG.dep_rte_type_cd <> 'THRU' THEN 
       46                   1 
       47                else 
       48                  0 
       49                END)
       50        else
       51           SUM(CASE 
       52                WHEN BAG_TAG_FLT_LEG.arr_rte_type_cd <> 'THRU' 
       53                AND (
       54                       BAG_TAG_FLT_LEG.scan_off_ind = '1' 
       55                       OR BAG_TAG_FLT_LEG.METRO_LOGIC_SCAN_IND = '1'
       56                    ) THEN 
       57                  1 
       58                else 
       59                 0 
       60                END)
       61        end SCANNEDBAGS,
       62        case 
       63          when '&deparrind' = 'D' then
       64            SUM(CASE 
       65                 WHEN BAG_TAG_FLT_LEG.dep_rte_type_cd <> 'THRU' THEN 
       66                  1 
       67                else 
       68                 0 
       69                END) - SUM(CASE 
       70                            WHEN BAG_TAG_FLT_LEG.scan_on_ind = 1 
       71                            AND BAG_TAG_FLT_LEG.dep_rte_type_cd <> 'THRU' THEN 
       72                             1 
       73                           else 
       74                            0 
       75                           END)
       76        else
       77           SUM(CASE 
       78                 WHEN BAG_TAG_FLT_LEG.arr_rte_type_cd <> 'THRU' THEN 
       79                   1 
       80               else 
       81                 0 
       82               END) - SUM(CASE 
       83                           WHEN BAG_TAG_FLT_LEG.arr_rte_type_cd <> 'THRU' 
       84                           AND (
       85                                  BAG_TAG_FLT_LEG.scan_off_ind = '1' 
       86                                  OR BAG_TAG_FLT_LEG.METRO_LOGIC_SCAN_IND = '1'
       87                               ) THEN 
       88                             1
       89                          else 
       90                            0 
       91                          END)
       92        end NOTSCANNEDBAGS
       93  FROM BAG_TAG_FLT_LEG, 
       94             OPS_FLT_LEG, 
       95             ARPT_GATE,
       96             SCHED_FLT_LEG 
       97  WHERE BAG_TAG_FLT_LEG.flt_leg_actv_ind = '1'
       98  and   BAG_TAG_FLT_LEG.carr_iata_cd = SCHED_FLT_LEG.carr_iata_cd 
       99  and   BAG_TAG_FLT_LEG.flt_nbr = SCHED_FLT_LEG.flt_nbr 
      100  and   BAG_TAG_FLT_LEG.flt_lcl_orig_dt = SCHED_FLT_LEG.flt_lcl_orig_dt 
      101  and   BAG_TAG_FLT_LEG.orig_arpt_cd = SCHED_FLT_LEG.orig_arpt_cd 
      102  and   BAG_TAG_FLT_LEG.dest_arpt_cd = SCHED_FLT_LEG.dest_arpt_cd 
      103  and   SCHED_FLT_LEG.carr_iata_cd = OPS_FLT_LEG.carr_iata_cd 
      104  and   SCHED_FLT_LEG.flt_nbr = OPS_FLT_LEG.flt_nbr 
      105  and   SCHED_FLT_LEG.flt_lcl_orig_dt = OPS_FLT_LEG.flt_lcl_orig_dt 
      106  and  ( case 
      107          when '&deparrind' = 'A' then
      108            SCHED_FLT_LEG.dest_arpt_cd
      109        else
      110          SCHED_FLT_LEG.orig_arpt_cd
      111        end)  = 
      112        (case 
      113          when '&deparrind' = 'A' then
      114            OPS_FLT_LEG.dest_arpt_cd
      115        else
      116            OPS_FLT_LEG.orig_arpt_cd
      117        end)
      118  and   (OPS_FLT_LEG.active_ind = '1' OR BITAND(OPS_FLT_LEG.flt_stat,16) = 16 ) 
      119  and  ( case 
      120          when '&deparrind' = 'A' then
      121            OPS_FLT_LEG.dest_arpt_cd
      122        else
      123            OPS_FLT_LEG.orig_arpt_cd
      124        end ) =  ARPT_GATE.arpt_iata_cd (+)
      125  and  ( case 
      126          when '&deparrind' = 'A' then
      127            OPS_FLT_LEG.arr_gate_nbr
      128        else
      129          OPS_FLT_LEG.dep_gate_nbr
      130        end ) = ARPT_GATE.gate_nm (+) 
      131  and   OPS_FLT_LEG.ORIG_ARPT_CD <> OPS_FLT_LEG.DEST_ARPT_CD 
      132  and   case 
      133          when '&operschedind' <> null
      134          and  '&operschedind' = 'S' then
      135            case
      136             when '&gmtlclind' = 'G' then
      137               case
      138                 when '&deparrind' = 'A' then
      139                   SCHED_FLT_LEG.gmt_arr_dtm 
      140                 else
      141                   SCHED_FLT_LEG.gmt_dep_dtm 
      142               end 
      143            else
      144              case
      145                 when '&deparrind' = 'A' then
      146                   SCHED_FLT_LEG.lcl_arr_dtm 
      147              else
      148                 SCHED_FLT_LEG.lcl_dep_dtm 
      149              end
      150            end 
      151        else
      152          case
      153             when '&gmtlclind' = 'G' then
      154               case
      155                 when '&deparrind' = 'A' then
      156                   OPS_FLT_LEG.gmt_est_arr_dtm 
      157                 else
      158                   OPS_FLT_LEG.gmt_est_dep_dtm 
      159               end 
      160            else
      161              case
      162                 when '&deparrind' = 'A' then
      163                   OPS_FLT_LEG.lcl_est_arr_dtm 
      164              else
      165                 OPS_FLT_LEG.lcl_est_dep_dtm 
      166              end
      167            end 
      168        end between to_date('&dt_from','DD-MON-YYYY hh24:mi:ss') and to_date('&dt_to','DD-MON-YYYY hh24:mi:ss') 
      169  and  SCHED_FLT_LEG.orig_arpt_cd = decode(nvl('&depsta','XXXX'),'XXXX',SCHED_FLT_LEG.orig_arpt_cd,'*', SCHED_FLT_LEG.orig_arpt_cd ,'&depsta')
      170  and SCHED_FLT_LEG.dest_arpt_cd = decode(nvl('&arrsta','XXXX'),'XXXX',SCHED_FLT_LEG.dest_arpt_cd,'&arrsta')
      171  and SCHED_FLT_LEG.CARR_IATA_CD =  case 
      172                              when '&carr' <> null 
      173                              and  trim('&carr') <> '*' then 
      174                                   decode('&carr','UAX',( SELECT carr_iata_cd  
      175                                                          FROM FLT_NBR_RNG_CARR 
      176                                                          WHERE SCHED_FLT_LEG.carr_iata_cd = FLT_NBR_RNG_CARR.carr_iata_cd 
      177                                                          AND to_number(SCHED_FLT_LEG.flt_nbr) 
      178                                                              BETWEEN to_number(FLT_NBR_RNG_CARR.flt_nbr_rng_start) 
      179                                                              AND     to_number(FLT_NBR_RNG_CARR.flt_nbr_rng_end) 
      180                                                          AND A.flt_lcl_orig_dt 
      181                                                              BETWEEN FLT_NBR_RNG_CARR.eff_dt 
      182                                                              AND FLT_NBR_RNG_CARR.expr_dt 
      183                                                          AND FLT_NBR_RNG_CARR.carr_type = 'UAX' 
      184                                                                      ) 
      185                                                          ), 
      186                                                     '&carr' 
      187                                            ) 
      188                            else 
      189                              SCHED_FLT_LEG.CARR_IATA_CD 
      190                            end
      191  and  ARPT_GATE.stn_ctl_cntr_zn in (
      192                                       case 
      193                                         when '&multipleZones' then
      194                                           '&zone'
      195                                       else
      196                                         DECODE('&zone','*',ARPT_GATE.stn_ctl_cntr_zn,null,ARPT_GATE.stn_ctl_cntr_zn,'&zone')
      197                                       end
      198                                    )
      199  and  (case 
      200          when trim('&gate') is not null
      201          and  trim('&gate') <> trim('*') then
      202            DECODE('&deparrStr',
      203                   'A',
      204                   TRIM(NVL(OPS_FLT_LEG.arr_Subgate_Nbr,OPS_FLT_LEG.arr_gate_Nbr)),
      205                   TRIM(NVL(OPS_FLT_LEG.dep_Subgate_Nbr,OPS_FLT_LEG.dep_gate_Nbr))
      206                  )
      207         else
      208          '&gate'
      209         end) IN ('&gate') 
      210  GROUP BY ARPT_GATE.stn_ctl_cntr_zn, 
      211           DECODE('&deparrStr',
      212                  'A',
      213                   NVL(OPS_FLT_LEG.arr_Subgate_Nbr,OPS_FLT_LEG.arr_gate_Nbr),
      214                   NVL(OPS_FLT_LEG.dep_Subgate_Nbr,OPS_FLT_LEG.dep_gate_Nbr)
      215                 ), 
      216           BAG_TAG_FLT_LEG.carr_iata_cd, 
      217           BAG_TAG_FLT_LEG.flt_nbr,
      218           To_Char(BAG_TAG_FLT_LEG.flt_leg_lcl_dep_dt,'MM/DD/YYYY'),
      219           To_Char(BAG_TAG_FLT_LEG.flt_lcl_orig_dt,'MM/DD/YYYY'),
      220           BAG_TAG_FLT_LEG.orig_arpt_cd,
      221           BAG_TAG_FLT_LEG.dest_arpt_cd,
      222           DECODE('&gmtlclStr',
      223                  'G',
      224                  To_Char(SCHED_FLT_LEG.gmt_dep_dtm, 'hh24:mi:ss'),
      225                  To_Char(SCHED_FLT_LEG.lcl_dep_dtm, 'hh24:mi:ss')
      226                 ),
      227           DECODE('&gmtlclStr',
      228                  'G',
      229                  To_Char(SCHED_FLT_LEG.gmt_arr_dtm, 'hh24:mi:ss'),
      230                  To_Char(SCHED_FLT_LEG.lcl_arr_dtm, 'hh24:mi:ss')
      231                  ),
      232           DECODE('&gmtlclStr',
      233                  'G',
      234                  To_Char(NVL(OPS_FLT_LEG.gmt_out_dtm,OPS_FLT_LEG.gmt_est_dep_dtm), 'hh24:mi:ss'),
      235                  To_Char(NVL(OPS_FLT_LEG.lcl_out_dtm,OPS_FLT_LEG.lcl_est_dep_dtm), 'hh24:mi:ss')
      236                  ), 
      237           DECODE('&gmtlclStr',
      238                  'G',
      239                  To_Char(NVL(OPS_FLT_LEG.gmt_in_dtm,OPS_FLT_LEG.gmt_est_arr_dtm), 'hh24:mi:ss'),
      240                  To_Char(NVL(OPS_FLT_LEG.lcl_in_dtm,OPS_FLT_LEG.lcl_est_arr_dtm), 'hh24:mi:ss')
      241                  ), 
      242*          OPS_FLT_LEG.flt_stat
      satyaki>/
      Enter value for deparrind: A
      old   2:        decode('&deparrind',
      new   2:        decode('A',
      Enter value for gmtlclind: G
      old  13:        decode('&gmtlclind',
      new  13:        decode('G',
      Enter value for gmtlclind: G
      old  18:       decode('&gmtlclind',
      new  18:       decode('G',
      Enter value for gmtlclind: G
      old  23:       decode('&gmtlclind',
      new  23:       decode('G',
      Enter value for gmtlclind: G
      old  28:       decode('&gmtlclind',
      new  28:       decode('G',
      Enter value for deparrind: A
      old  36:         when '&deparrind' = 'D' then
      new  36:         when 'A' = 'D' then
      Enter value for deparrind: A
      old  42:         when '&deparrind' = 'D' then
      new  42:         when 'A' = 'D' then
      Enter value for deparrind: A
      old  63:         when '&deparrind' = 'D' then
      new  63:         when 'A' = 'D' then
      Enter value for deparrind: A
      old 107:         when '&deparrind' = 'A' then
      new 107:         when 'A' = 'A' then
      Enter value for deparrind: A
      old 113:         when '&deparrind' = 'A' then
      new 113:         when 'A' = 'A' then
      Enter value for deparrind: A
      old 120:         when '&deparrind' = 'A' then
      new 120:         when 'A' = 'A' then
      Enter value for deparrind: A
      old 126:         when '&deparrind' = 'A' then
      new 126:         when 'A' = 'A' then
      Enter value for operschedind: S
      old 133:         when '&operschedind' <> null
      new 133:         when 'S' <> null
      Enter value for operschedind: S
      old 134:         and  '&operschedind' = 'S' then
      new 134:         and  'S' = 'S' then
      Enter value for gmtlclind: G
      old 136:            when '&gmtlclind' = 'G' then
      new 136:            when 'G' = 'G' then
      Enter value for deparrind: A
      old 138:                when '&deparrind' = 'A' then
      new 138:                when 'A' = 'A' then
      Enter value for deparrind: A
      old 145:                when '&deparrind' = 'A' then
      new 145:                when 'A' = 'A' then
      Enter value for gmtlclind: G
      old 153:            when '&gmtlclind' = 'G' then
      new 153:            when 'G' = 'G' then
      Enter value for deparrind: A
      old 155:                when '&deparrind' = 'A' then
      new 155:                when 'A' = 'A' then
      Enter value for deparrind: A
      old 162:                when '&deparrind' = 'A' then
      new 162:                when 'A' = 'A' then
      Enter value for dt_from: 01-JAN-2008 01:01:01
      Enter value for dt_to: 24-JUN-2008 10:45:00
      old 168:       end between to_date('&dt_from','DD-MON-YYYY hh24:mi:ss') and to_date('&dt_to','DD-MON-YYYY hh24:mi:ss') 
      new 168:       end between to_date('01-JAN-2008 01:01:01','DD-MON-YYYY hh24:mi:ss') and to_date('24-JUN-2008 10:45:00','DD-MON-YYYY hh24:mi:ss') 
      Enter value for depsta: *
      Enter value for depsta: *
      old 169: and  SCHED_FLT_LEG.orig_arpt_cd = decode(nvl('&depsta','XXXX'),'XXXX',SCHED_FLT_LEG.orig_arpt_cd,'*', SCHED_FLT_LEG.orig_arpt_cd ,'&depsta')
      new 169: and  SCHED_FLT_LEG.orig_arpt_cd = decode(nvl('*','XXXX'),'XXXX',SCHED_FLT_LEG.orig_arpt_cd,'*', SCHED_FLT_LEG.orig_arpt_cd ,'*')
      Enter value for arrsta: 
      Enter value for arrsta: 
      old 170: and SCHED_FLT_LEG.dest_arpt_cd = decode(nvl('&arrsta','XXXX'),'XXXX',SCHED_FLT_LEG.dest_arpt_cd,'&arrsta')
      new 170: and SCHED_FLT_LEG.dest_arpt_cd = decode(nvl('','XXXX'),'XXXX',SCHED_FLT_LEG.dest_arpt_cd,'')
      Enter value for carr: UAX
      old 172:                             when '&carr' <> null 
      new 172:                             when 'UAX' <> null 
      Enter value for carr: UAX
      old 173:                             and  trim('&carr') <> '*' then 
      new 173:                             and  trim('UAX') <> '*' then 
      Enter value for carr: UAX
      old 174:                                  decode('&carr','UAX',( SELECT carr_iata_cd  
      new 174:                                  decode('UAX','UAX',( SELECT carr_iata_cd  
      Enter value for carr: UAX
      old 186:                                                    '&carr' 
      new 186:                                                    'UAX' 
      Enter value for multiplezones: TRUE
      old 193:                                        when '&multipleZones' then
      new 193:                                        when 'TRUE' then
      Enter value for zone: *
      old 194:                                          '&zone'
      new 194:                                          '*'
      Enter value for zone: *
      Enter value for zone: *
      old 196:                                        DECODE('&zone','*',ARPT_GATE.stn_ctl_cntr_zn,null,ARPT_GATE.stn_ctl_cntr_zn,'&zone')
      new 196:                                        DECODE('*','*',ARPT_GATE.stn_ctl_cntr_zn,null,ARPT_GATE.stn_ctl_cntr_zn,'*')
      Enter value for gate: *
      old 200:         when trim('&gate') is not null
      new 200:         when trim('*') is not null
      Enter value for gate: *
      old 201:         and  trim('&gate') <> trim('*') then
      new 201:         and  trim('*') <> trim('*') then
      Enter value for deparrstr: A
      old 202:           DECODE('&deparrStr',
      new 202:           DECODE('A',
      Enter value for gate: *
      old 208:         '&gate'
      new 208:         '*'
      Enter value for gate: *
      old 209:        end) IN ('&gate') 
      new 209:        end) IN ('*') 
      Enter value for deparrstr: A
      old 211:          DECODE('&deparrStr',
      new 211:          DECODE('A',
      Enter value for gmtlclstr: G
      old 222:          DECODE('&gmtlclStr',
      new 222:          DECODE('G',
      Enter value for gmtlclstr: G
      old 227:          DECODE('&gmtlclStr',
      new 227:          DECODE('G',
      Enter value for gmtlclstr: G
      old 232:          DECODE('&gmtlclStr',
      new 232:          DECODE('G',
      Enter value for gmtlclstr: G
      old 237:          DECODE('&gmtlclStr',
      new 237:          DECODE('G',
                DECODE('A',
                     *
      ERROR at line 202:
      ORA-00905: missing keyword 
      
      
      Elapsed: 00:00:02.09
      satyaki>
      satyaki>spool off
      I know it's bit difficult to tell such a situation like this where you have no idea regarding data and other important information. Since, this is the error other than logic - that's why i'm posting this without this information.

      Does anybody have any idea regarding this abnormal behavior.

      My Db is 9i.

      Regards.

      Satyaki De.
        • 1. Re: ORA-00905: missing keyword
          114079
          I think no one have time to check this big script.

          Break the script in small parts and then run them.. After that u can merge them.
          • 2. Re: ORA-00905: missing keyword
            gerd_99
            One error is in the decode statement beginning at line 174. There are too much right parentheses. I´m afraid this is not the only error though...
            • 3. Re: ORA-00905: missing keyword
              gerd_99
              192                                       case 
              193                                         when '&multipleZones' then
              194                                           '&zone'
              case WHAT?

              Regards,
              Gerd
              • 4. Re: ORA-00905: missing keyword
                561825
                I guess this line which is causing the problem.
                174                                   decode('&carr','UAX',( SELECT carr_iata_cd  
                175                                                          FROM FLT_NBR_RNG_CARR 
                176                                                          WHERE SCHED_FLT_LEG.carr_iata_cd = FLT_NBR_RNG_CARR.carr_iata_cd 
                177                                                          AND to_number(SCHED_FLT_LEG.flt_nbr) 
                178                                                              BETWEEN to_number(FLT_NBR_RNG_CARR.flt_nbr_rng_start) 
                179                                                              AND     to_number(FLT_NBR_RNG_CARR.flt_nbr_rng_end) 
                180                                                          AND A.flt_lcl_orig_dt 
                181                                                              BETWEEN FLT_NBR_RNG_CARR.eff_dt 
                182                                                              AND FLT_NBR_RNG_CARR.expr_dt 
                183                                                          AND FLT_NBR_RNG_CARR.carr_type = 'UAX' 
                184                                                                      ) 
                185                                                          ), 
                186                                                     '&carr' 
                187                                            ) 
                You have opened only two parenthesis but you have closed three parenthesis.

                Also I notice couple of other anomalies like
                133          when '&operschedind' <> null
                125  and  ( case 
                126          when '&deparrind' = 'A' then
                127            OPS_FLT_LEG.arr_gate_nbr
                128        else
                129          OPS_FLT_LEG.dep_gate_nbr
                130        end ) = ARPT_GATE.gate_nm (+) 
                191  and  ARPT_GATE.stn_ctl_cntr_zn in (
                192                                       case 
                193                                         when '&multipleZones' then
                194                                           '&zone'
                195                                       else
                196                                         DECODE('&zone','*',ARPT_GATE.stn_ctl_cntr_zn,null,ARPT_GATE.stn_ctl_cntr_zn,'&zone')
                197                                       end
                198                                    )
                You know what I mean in the above code.

                Hope that helps

                regards

                Raj

                [Edit : ] @gerd_99 I missed to read your post.

                Message was edited by:
                R.Subramanian
                • 5. Re: ORA-00905: missing keyword
                  577396
                  Cause: A required keyword is missing.
                  Action: Correct the syntax.
                  • 6. Re: ORA-00905: missing keyword
                    Satyaki_De
                    Thank you guys. Actually i've changed my script. But, thanks for your help. Basically, this is a conversion fro java service to SQL. Thats' why this mistakes have taken place. But, anyway, thanks for your minute observe.

                    Regards.

                    Satyaki De.