Home » SQL & PL/SQL » SQL & PL/SQL » Oracle query error (oracle 11g)
Oracle query error [message #573580] Mon, 31 December 2012 08:05 Go to next message
JSKOBS
Messages: 10
Registered: February 2012
Location: Minneapolis
Junior Member
Hi, when i run my below query im getting an error, can you please tell me how to correct it

SELECT total_count, 
       row_id, 
       ship_type, 
       eligible_flag, 
       violation_flag, 
       snm_critical_flag, 
       book_i, 
       cons_book_id, 
       load_i, 
       coload_i, 
       po_i, 
       dept, 
       status, 
       clpid, 
       pkdcrd, 
       imp_gtwy_i, 
       imp_gtwy_n, 
       vend_si_date, 
       vend_si_time, 
       carr_si_date, 
       carr_si_time, 
       cy_cutoff, 
       carr_info, 
       port_of_origin, 
       etd, 
       last_updated, 
       event_docs_flag 
FROM   (SELECT Count(*) 
                 over() TOTAL_COUNT, 
               ROWNUM   row_id, 
               ship_type, 
               eligible_flag, 
               violation_flag, 
               snm_critical_flag, 
               book_i, 
               cons_book_id, 
               load_i, 
               coload_i, 
               po_i, 
               dept, 
               status, 
               clpid, 
               pkdcrd, 
               imp_gtwy_i, 
               imp_gtwy_n, 
               vend_si_date, 
               vend_si_time, 
               carr_si_date, 
               carr_si_time, 
               cy_cutoff, 
               carr_info, 
               port_of_origin, 
               etd, 
               last_updated, 
               event_docs_flag 
        FROM   (SELECT ship_type, 
                       eligible_flag, 
                       violation_flag, 
                       snm_critical_flag, 
                       book_i, 
                       cons_book_id, 
                       load_i, 
                       coload_i, 
                       po_i, 
                       dept, 
                       status, 
                       clpid, 
                       pkdcrd, 
                       imp_gtwy_i, 
                       imp_gtwy_n, 
                       vend_si_date, 
                       vend_si_time, 
                       carr_si_date, 
                       carr_si_time, 
                       cy_cutoff, 
                       carr_info, 
                       port_of_origin, 
                       etd, 
                       last_updated, 
                       event_docs_flag 
                FROM   (SELECT DISTINCT 'L' 
                                        ship_type 
                                                , 
                                        clp.finl_elig_f 
                                                 eligible_flag, 
                                        CASE 
                                          WHEN To_timestamp(To_char('03-Aug-12', 
                                                            'DD-MON-YYYY') 
                                                            ||'-' 
                                                            || '20:37', 
                                               'DD-MON-YYYY-HH24:MI:SS') >= ( 
                                                      To_timestamp(To_char( 
                                          (SELECT est_deptr_d 
                                           FROM   carr_ship_schd_e 
                                                  cs, 
                                                  carr_ship_seg_e cg 
                                           WHERE  cg.carr_ship_schd_i = 
                                                  cs.carr_ship_schd_i 
                                                  AND strt_loc_type_c = 
                                                      'EXP' 
                                                  AND cs.carr_ship_schd_i = 
                                          l.carr_ship_schd_i), 
                                          'DD-MON-YYYY') 
                                                       ||'-' 
                                                       ||'00:00', 
                                          'DD-MON-YYYY-HH24:MI:SS') 
                                          - ( 24 / 24 ) ) 
                                        THEN 'Y' 
                                          ELSE 'N' 
                                        END 
                                                 SNM_CRITICAL_FLAG, 
                                        CLP.viol_i 
                                        AS 
                                                 violation_flag, 
                                        NULL 
                                        book_i, 
                                        NULL 
                                                 cons_book_id, 
                                        l.load_i 
                                        load_i, 
                                        NULL 
                                        coload_i, 
                                        NULL 
                                        po_i, 
                                        NULL 
                                        dept, 
                                        Nvl(clp.ctnr_load_pln_stat_c, 'PENDG') 
                                        status, 
                                        clp.ctnr_load_pln_i 
                                        clpid, 
                                        Decode(l.load_type_c, 'CT', 
                                        (SELECT Max(lr.ship_pkup_d) 
                                         FROM   load_rte_det lr, 
                                                load_det ld 
                                         WHERE 
                                        lr.load_det_i = ld.load_det_i 
                                        AND ld.load_i = l.load_i 
                                        AND ld.load_type_f IS NOT NULL 
                                                                     GROUP  BY 
                                        l.load_i), 
                                        (SELECT Max(lr.ship_pkup_d) 
                                         FROM   load_rte_det lr 
                                         WHERE 
                                        lr.load_det_i = ld.load_det_i 
                                        AND ld.load_i = l.load_i 
                                                               GROUP  BY 
                                        l.load_i)) 
                                        pkdcrd, 
                                        l.imp_gtwy_i 
                                                imp_gtwy_i 
                                                 , 
                                        (SELECT abbr_n 
                                         FROM   dc_otmx d 
                                         WHERE  d.dc_i = l.imp_gtwy_i) 
                                                imp_gtwy_n 
                                                 , 
                                        (SELECT city_n 
                                         FROM   tstn_pnt_e tp 
                                         WHERE  tp.corp_tstn_pnt_i = l.poex_c) 
                                                 port_of_export, 
                                        si.vend_ship_instr_ctof_d 
                                                 vend_si_date, 
                                        si.vend_ship_instr_ctof_ti 
                                                 vend_si_time, 
                                        si.carr_ship_instr_ctof_d 
                                                 carr_si_date, 
                                        si.carr_ship_instr_ctof_ti 
                                                 carr_si_time, 
                                        si.ctnr_yrd_ctof_d 
                                        cy_cutoff 
                                                , 
                                        l.carr_ship_schd_i, 
                                        (SELECT DISTINCT vend_n 
                                                         || '@' 
                                                         || ves_n 
                                                         || '@' 
                                                         || voyg_i 
                                         FROM   vend_otmx vo, 
                                                vend_e ve, 
                                                carr_ship_schd_e cs, 
                                                carr_ship_ln_e cl, 
                                                carr_ship_seg_e cg 
                                         WHERE  ve.vend_i = vo.vend_i 
                                                AND vo.scac_c = cl.carr_scac_c 
                                                AND VO.tspt_mode_c = 'OCEAN' 
                                                AND cl.carr_ship_ln_i = 
                                                    cs.carr_ship_ln_i 
                                                AND cg.carr_ship_schd_i = 
                                                    cs.carr_ship_schd_i 
                                                AND cs.carr_ship_schd_i = 
                                                    l.carr_ship_schd_i 
                                                AND ves_type_c = 'GC') 
                                        carr_info 
                                                , 
                                        (SELECT city_n 
                                         FROM   tstn_pnt_e, 
                                                carr_ship_schd_e cs, 
                                                carr_ship_seg_e cg 
                                         WHERE 
                        tstn_pnt_e.un_loc_i = strt_seg_loc_c 
                        AND cg.carr_ship_schd_i = 
                            cs.carr_ship_schd_i 
                        AND strt_loc_type_c = 'EXP' 
                        AND cs.carr_ship_schd_i = 
                            l.carr_ship_schd_i) 
                         port_of_origin, 
                                        (SELECT est_deptr_d 
                                         FROM   carr_ship_schd_e cs, 
                                                carr_ship_seg_e cg 
                                         WHERE 
                        cg.carr_ship_schd_i = cs.carr_ship_schd_i 
                        AND strt_loc_type_c = 'EXP' 
                        AND cs.carr_ship_schd_i = l.carr_ship_schd_i) 
                                        etd, 
                                        (SELECT login_i 
                                                || '-' 
                                                || frst_n 
                                         FROM   sys_user su 
                                         WHERE  su.login_i = clp.updt_user_i 
                                                AND SU.user_actv_f = 'Y') 
                                                 last_updated, 
                                        clp.icmp_info_f 
                                                 event_docs_flag 
                        FROM   load l, 
                               load_det ld, 
                               ctnr_load_pln clp, 
                               ship_instr_ctof_date si 
                        WHERE  ld.load_i = l.load_i 
                               AND l.carr_ship_schd_i = si.carr_ship_schd_i 
                               AND clp.load_i = l.load_i 
                               AND clp.lgcl_del_c IS NULL 
                               AND l.load_type_c IN( 'CY', 'OB', 'CT' ) 
                               AND ld.load_type_f IS NULL 
                               AND l.carr_ship_schd_i IS NOT NULL 
                               AND si.vend_ship_instr_ctof_d IS NOT NULL 
                               AND si.vend_ship_instr_ctof_ti IS NOT NULL 
                               AND si.carr_ship_instr_ctof_d IS NOT NULL 
                               AND si.carr_ship_instr_ctof_ti IS NOT NULL 
                               AND l.load_i IN (SELECT DISTINCT( prim_load_i ) 
                                                FROM   book b, 
                                                       tran t 
                                                WHERE  b.tran_i = t.tran_i 
                                                       AND t.po_i = 2707256) 
                               AND l.imp_gtwy_i IN ( '581', '582', '583', '584', 
                                                     '586', '3805', '3850', 
                                                     '3851', 
                                                     '3852', '3890', '3891' ) 
                               AND ( Nvl(clp.ctnr_load_pln_stat_c, 'PENDG') IN 
                                     ( 'SUBMT', 'FNLZD', '' ) 
                                      OR ( clp.ctnr_load_pln_stat_c = 'CLOSD' 
                                           AND ( To_timestamp( 
                                                 ( To_char('03-Aug-12', 
                                                   'DD-MON-YYYY') 
                                                   ||'-' 
                                                   || '20:37' ), 
                                                 'DD-MON-YYYY-HH24:MI:SS') 
                                                 <= 
                                                     ( clp.updt_ts + 180 ) ) ) ) 
                               AND l.poex_c IN ( 'JKT', 'SRG', 'SUB' ) 
                        UNION ALL 
                        SELECT DISTINCT 'B' 
                                        ship_type 
                                        , 
                                        clp.finl_elig_f 
                                        eligible_flag, 
                                        CASE 
                                          WHEN To_timestamp(To_char('03-Aug-12', 
                                                            'DD-MON-YYYY') 
                                                            ||'-' 
                                                            || '20:37', 
                                               'DD-MON-YYYY-HH24:MI:SS') >= ( 
                                                      To_timestamp(To_char( 
                                          (SELECT est_deptr_d 
                                           FROM   carr_ship_schd_e 
                                                  cs, 
                                                  carr_ship_seg_e cg 
                                           WHERE  cg.carr_ship_schd_i = 
                                                  cs.carr_ship_schd_i 
                                                  AND strt_loc_type_c = 
                                                      'EXP' 
                                                  AND cs.carr_ship_schd_i = 
                                          b.carr_ship_schd_i), 
                                          'DD-MON-YYYY') 
                                                       ||'-' 
                                                       ||'00:00', 
                                          'DD-MON-YYYY-HH24:MI:SS') 
                                          - ( 24 / 24 ) ) 
                                        THEN 'Y' 
                                          ELSE 'N' 
                                        END 
                                        SNM_CRITICAL_FLAG, 
                                        CLP.viol_i 
                                        AS 
                                        violation_flag, 
                                        b.book_i 
                                        book_i, 
                                        b.ref_book_i 
                                        cons_book_id, 
                                        NULL 
                                        load_i, 
                                        NULL 
                                        coload_i, 
                                        t.po_i 
                                        po_i, 
                                        t.po_num_src_c 
                                        dept, 
                                        Nvl(clp.ctnr_load_pln_stat_c, 'PENDG') 
                                        status, 
                                        clp.ctnr_load_pln_i 
                                        clpid, 
                                        t.crgo_rdy_d 
                                        pkdcrd, 
                                        b.imp_gtwy_i 
                                        imp_gtwy_i 
                                        , 
                                        (SELECT abbr_n 
                                         FROM   dc_otmx d 
                                         WHERE  d.dc_i = b.imp_gtwy_i) 
                                        imp_gtwy_n 
                                        , 
                                        (SELECT city_n 
                                         FROM   tstn_pnt_e tp 
                                         WHERE  tp.corp_tstn_pnt_i = t.poex_c) 
                                        port_of_export, 
                                        si.vend_ship_instr_ctof_d 
                                        vend_si_date, 
                                        si.vend_ship_instr_ctof_ti 
                                        vend_si_time, 
                                        si.carr_ship_instr_ctof_d 
                                        carr_si_date, 
                                        si.carr_ship_instr_ctof_ti 
                                        carr_si_time, 
                                        si.ctnr_yrd_ctof_d 
                                        cy_cutoff 
                                        , 
                                        b.carr_ship_schd_i, 
                                        (SELECT DISTINCT vend_n 
                                                         || '@' 
                                                         || ves_n 
                                                         || '@' 
                                                         || voyg_i 
                                         FROM   vend_otmx vo, 
                                                vend_e ve, 
                                                carr_ship_schd_e cs, 
                                                carr_ship_ln_e cl, 
                                                carr_ship_seg_e cg 
                                         WHERE  ve.vend_i = vo.vend_i 
                                                AND vo.scac_c = cl.carr_scac_c 
                                                AND cl.carr_ship_ln_i = 
                                                    cs.carr_ship_ln_i 
                                                AND Upper(vo.tspt_mode_c) = 
                                                    'OCEAN' 
                                                AND cg.carr_ship_schd_i = 
                                                    cs.carr_ship_schd_i 
                                                AND cs.carr_ship_schd_i = 
                                                    b.carr_ship_schd_i 
                                                AND ves_type_c = 'GC') 
                                        carr_info 
                                        , 
                                        (SELECT city_n 
                                         FROM   tstn_pnt_e, 
                                                carr_ship_schd_e cs, 
                                                carr_ship_seg_e cg 
                                         WHERE 
                        tstn_pnt_e.un_loc_i = strt_seg_loc_c 
                        AND cg.carr_ship_schd_i = 
                            cs.carr_ship_schd_i 
                        AND strt_loc_type_c = 'EXP' 
                        AND cs.carr_ship_schd_i = 
                            b.carr_ship_schd_i) 
                                        port_of_origin, 
                                        (SELECT est_deptr_d 
                                         FROM   carr_ship_schd_e cs, 
                                                carr_ship_seg_e cg 
                                         WHERE 
                        cg.carr_ship_schd_i = cs.carr_ship_schd_i 
                        AND strt_loc_type_c = 'EXP' 
                        AND cs.carr_ship_schd_i = b.carr_ship_schd_i) 
                                        etd, 
                                        (SELECT login_i 
                                                || '-' 
                                                || frst_n 
                                         FROM   sys_user su 
                                         WHERE  su.login_i = clp.updt_user_i 
                                                AND SU.user_actv_f = 'Y') 
                                        last_updated, 
                                        clp.icmp_info_f 
                                        event_docs_flag 
                        FROM   book b, 
                               tran t, 
                               ctnr_load_pln clp, 
                               ship_instr_ctof_date si 
                        WHERE  t.tran_i = b.tran_i 
                               AND b.srvc_type_c IN( 'CY' ) 
                               AND Nvl(t.cload_f, 'N') = 'N' 
                               AND clp.book_i = b.book_i 
                               AND clp.lgcl_del_c IS NULL 
                               AND b.prim_load_i IS NULL 
                               AND b.carr_ship_schd_i IS NOT NULL 
                               AND b.carr_ship_schd_i = si.carr_ship_schd_i 
                               AND si.vend_ship_instr_ctof_d IS NOT NULL 
                               AND si.vend_ship_instr_ctof_ti IS NOT NULL 
                               AND si.carr_ship_instr_ctof_d IS NOT NULL 
                               AND si.carr_ship_instr_ctof_ti IS NOT NULL 
                               AND t.po_i = 2707256 
                               AND b.imp_gtwy_i IN ( '581', '582', '583', '584', 
                                                     '586', '3805', '3850', 
                                                     '3851', 
                                                     '3852', '3890', '3891' ) 
                               AND ( Nvl(clp.ctnr_load_pln_stat_c, 'PENDG') IN 
                                     ( 'SUBMT', 'FNLZD', '' ) 
                                      OR ( clp.ctnr_load_pln_stat_c = 'CLOSD' 
                                           AND ( To_timestamp( 
                                                 ( To_char('03-Aug-12', 
                                                   'DD-MON-YYYY') 
                                                   ||'-' 
                                                   || '20:37' ), 
                                                 'DD-MON-YYYY-HH24:MI:SS') 
                                                 <= 
                                                     ( clp.updt_ts + 180 ) ) ) ) 
                               AND t.poex_c IN ( 'JKT', 'SRG', 'SUB' ) 
                        UNION ALL 
                        SELECT DISTINCT 'C' 
                                        ship_type, 
                                        clp.finl_elig_f 
                                        eligible_flag, 
                                        CASE 
                                          WHEN To_timestamp(To_char('03-Aug-12', 
                                                            'DD-MON-YYYY') 
                                                            ||'-' 
                                                            || '20:37', 
                                               'DD-MON-YYYY-HH24:MI:SS') >= ( 
                                                      To_timestamp(To_char( 
                                          (SELECT est_deptr_d 
                                           FROM   carr_ship_schd_e 
                                                  cs, 
                                                  carr_ship_seg_e cg 
                                           WHERE  cg.carr_ship_schd_i = 
                                                  cs.carr_ship_schd_i 
                                                  AND strt_loc_type_c = 
                                                      'EXP' 
                                                  AND cs.carr_ship_schd_i = 
                                          b.carr_ship_schd_i), 
                                          'DD-MON-YYYY') 
                                                       ||'-' 
                                                       ||'00:00', 
                                          'DD-MON-YYYY-HH24:MI:SS') 
                                          - ( 24 / 24 ) ) 
                                        THEN 'Y' 
                                          ELSE 'N' 
                                        END 
                                        SNM_CRITICAL_FLAG, 
                                        CLP.viol_i 
                                        AS violation_flag, 
                                        NULL 
                                        book_i, 
                                        NULL 
                                        cons_book_id, 
                                        NULL 
                                        load_i, 
                                        t.cload_i 
                                        coload_i, 
                                        NULL 
                                        po_i, 
                                        NULL 
                                        dept, 
                                        Nvl(clp.ctnr_load_pln_stat_c, 'PENDG') 
                                        status, 
                                        clp.ctnr_load_pln_i 
                                        clpid, 
                                        (SELECT Min(t1.crgo_rdy_d) 
                                         FROM   tran t1, 
                                                book b 
                                         WHERE  t1.cload_f = 'Y' 
                                                AND t1.cload_i = t.cload_i 
                                                AND t1.tran_i = b.tran_i 
                                                AND b.est_arv_d IN 
                                                    (SELECT Min(est_arv_d) 
                                                     FROM   book b, 
                                                            tran tr 
                                                     WHERE  b.tran_i = tr.tran_i 
                                                            AND tr.cload_i = 
                                                                t.cload_i)) 
                                        pkdcrd, 
                                        b.imp_gtwy_i 
                                        imp_gtwy_i, 
                                        (SELECT abbr_n 
                                         FROM   dc_otmx d 
                                         WHERE  d.dc_i = b.imp_gtwy_i) 
                                        imp_gtwy_n, 
                                        (SELECT city_n 
                                         FROM   tstn_pnt_e tp 
                                         WHERE  tp.corp_tstn_pnt_i = t.poex_c) 
                                        port_of_export, 
                                        si.vend_ship_instr_ctof_d 
                                        vend_si_date, 
                                        si.vend_ship_instr_ctof_ti 
                                        vend_si_time, 
                                        si.carr_ship_instr_ctof_d 
                                        carr_si_date, 
                                        si.carr_ship_instr_ctof_ti 
                                        carr_si_time, 
                                        si.ctnr_yrd_ctof_d 
                                        cy_cutoff, 
                                        b.carr_ship_schd_i, 
                                        (SELECT DISTINCT vend_n 
                                                         || '@' 
                                                         || ves_n 
                                                         || '@' 
                                                         || voyg_i 
                                         FROM   vend_otmx vo, 
                                                vend_e ve, 
                                                carr_ship_schd_e cs, 
                                                carr_ship_ln_e cl, 
                                                carr_ship_seg_e cg 
                                         WHERE  ve.vend_i = vo.vend_i 
                                                AND vo.scac_c = cl.carr_scac_c 
                                                AND Upper(vo.tspt_mode_c) = 
                                                    'OCEAN' 
                                                AND cl.carr_ship_ln_i = 
                                                    cs.carr_ship_ln_i 
                                                AND cg.carr_ship_schd_i = 
                                                    cs.carr_ship_schd_i 
                                                AND cs.carr_ship_schd_i = 
                                                    b.carr_ship_schd_i 
                                                AND ves_type_c = 'GC') 
                                        carr_info, 
                                        (SELECT city_n 
                                         FROM   tstn_pnt_e, 
                                                carr_ship_schd_e cs, 
                                                carr_ship_seg_e cg 
                                         WHERE 
                        tstn_pnt_e.un_loc_i = strt_seg_loc_c 
                        AND cg.carr_ship_schd_i = 
                            cs.carr_ship_schd_i 
                        AND strt_loc_type_c = 'EXP' 
                        AND cs.carr_ship_schd_i = 
                            b.carr_ship_schd_i) 
                                        port_of_origin, 
                                        (SELECT est_deptr_d 
                                         FROM   carr_ship_schd_e cs, 
                                                carr_ship_seg_e cg 
                                         WHERE 
                        cg.carr_ship_schd_i = cs.carr_ship_schd_i 
                        AND strt_loc_type_c = 'EXP' 
                        AND cs.carr_ship_schd_i = b.carr_ship_schd_i) 
                                        etd, 
                                        (SELECT login_i 
                                                || '-' 
                                                || frst_n 
                                         FROM   sys_user su 
                                         WHERE  su.login_i = clp.updt_user_i 
                                                AND SU.user_actv_f = 'Y') 
                                        last_updated, 
                                        clp.icmp_info_f 
                                        event_docs_flag 
                        FROM   book b, 
                               tran t, 
                               ctnr_load_pln clp, 
                               ship_instr_ctof_date si 
                        WHERE  t.tran_i = b.tran_i 
                               AND t.cload_i IS NOT NULL 
                               AND Nvl(t.cload_f, 'N') = 'Y' 
                               AND clp.cload_i = t.cload_i 
                               AND clp.lgcl_del_c IS NULL 
                               AND b.prim_load_i IS NULL 
                               AND b.carr_ship_schd_i IS NOT NULL 
                               AND b.carr_ship_schd_i = si.carr_ship_schd_i 
                               AND si.vend_ship_instr_ctof_d IS NOT NULL 
                               AND si.vend_ship_instr_ctof_ti IS NOT NULL 
                               AND si.carr_ship_instr_ctof_d IS NOT NULL 
                               AND si.carr_ship_instr_ctof_ti IS NOT NULL 
                               AND t.po_i = 2707256 
                               AND b.imp_gtwy_i IN ( '581', '582', '583', '584', 
                                                     '586', '3805', '3850', 
                                                     '3851', 
                                                     '3852', '3890', '3891' ) 
                               AND ( Nvl(clp.ctnr_load_pln_stat_c, 'PENDG') IN 
                                     ( 'SUBMT', 'FNLZD', '' ) 
                                      OR ( clp.ctnr_load_pln_stat_c = 'CLOSD' 
                                           AND ( To_timestamp( 
                                                 ( To_char('03-Aug-12', 
                                                   'DD-MON-YYYY') 
                                                   ||'-' 
                                                   || '20:37' ), 
                                                 'DD-MON-YYYY-HH24:MI:SS') 
                                                 <= 
                                                     ( clp.updt_ts + 180 ) ) ) ) 
                               AND t.poex_c IN ( 'JKT', 'SRG', 'SUB' )) 
                ORDER  BY status DESC, 
                          To_date(etd), 
                          To_date(pkdcrd), 
                          coload_i, 
                          load_i, 
                          book_i)) 
WHERE  row_id BETWEEN 1 AND 20 


^BlackSwan formatted SQL & placed between {code} tags correctly

[Updated on: Mon, 31 December 2012 09:19] by Moderator

Report message to a moderator

Re: Oracle query error [message #573582 is a reply to message #573580] Mon, 31 December 2012 08:23 Go to previous messageGo to next message
ind9
Messages: 60
Registered: January 2009
Member
Hi,
If you could provide create table scripts that would be helpful to reproduce the error.
http://www.orafaq.com/forum/t/174502/136319/

Thank you.
Re: Oracle query error [message #573583 is a reply to message #573582] Mon, 31 December 2012 08:27 Go to previous messageGo to next message
JSKOBS
Messages: 10
Registered: February 2012
Location: Minneapolis
Junior Member
Hi, sure. im getting below error when i run the query

Error report:
SQL Error: ORA-01722: invalid number
Re: Oracle query error [message #573584 is a reply to message #573583] Mon, 31 December 2012 08:35 Go to previous messageGo to next message
ind9
Messages: 60
Registered: January 2009
Member
I am getting table doesn't exist error.
Quote:
433 ORDER BY STATUS DESC ,
434 TO_DATE(ETD) ,
435 TO_DATE(PKDCRD) ,
436 COLOAD_I ,
437 LOAD_I ,
438 BOOK_I
439 )
440 )
441* WHERE row_id BETWEEN 1 AND 20
SQL> /
ship_instr_ctof_date si
*
ERROR at line 181:
ORA-00942: table or view does not exist
Re: Oracle query error [message #573585 is a reply to message #573580] Mon, 31 December 2012 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Mon, 13 February 2012 19:41
Michel Cadot wrote on Mon, 13 February 2012 17:49
With all SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel



This will avoid other waste their time searching for inappropriate answer because your specifications are not correct or complete.


Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Do you REALLY think we debug your garbage?

Regards
Michel

[Updated on: Mon, 31 December 2012 11:18]

Report message to a moderator

Re: Oracle query error [message #573586 is a reply to message #573584] Mon, 31 December 2012 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Oracle query error [message #573589 is a reply to message #573580] Mon, 31 December 2012 09:11 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
Hi,
when searching for string/date/number conversion, I came to this expression:
select (TO_CHAR( '03-Aug-12' , 'DD-MON-YYYY' )||'-'|| '20:37' ) from dual;

This (standalone) one does throw ORA-1722 exception as it requires implicit conversion. Just fix it - think about used function, its input parameter data types and its return value data type. Then replace it in the posted query (by the way you should really consider formatting it to human readable form).
For details, consult SQL Language Reference book. It is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
There may be other implicit conversions though. If they throw any error, get rid of them as well.
Re: Oracle query error [message #573591 is a reply to message #573589] Mon, 31 December 2012 09:22 Go to previous messageGo to next message
ind9
Messages: 60
Registered: January 2009
Member
Are you expecting below output? if so highlighted things were missing.

select (TO_CHAR(to_date( '03-Aug-2012' , 'DD-MON-YYYY' ))||'-'|| '20:37' ) from dual;
Output:
03-AUG-12-20:37

It is worth to read
http://psoug.org/definition/TO_DATE.htm
Re: Oracle query error [message #573592 is a reply to message #573591] Mon, 31 December 2012 09:36 Go to previous message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
ind9 wrote on Mon, 31 December 2012 16:22
Are you expecting below output? if so highlighted things were missing.

select (TO_CHAR(to_date( '03-Aug-2012' , 'DD-MON-YYYY' ))||'-'|| '20:37' ) from dual;

That is just one point of view. Another one may be that many things (including TO_CHAR) are superfluous there.
'03-Aug_2012' already is a string. Why would anybody convert it to DATE and then back to VARCHAR2 (without format mask, by the way)?
By the way, your query returns '03-08-2012 00:00:00-20:37' in my environment. Why? Just read about TO_CHAR and you will know.
Previous Topic: REGEXP
Next Topic: Backward accessing super type's attributes from sub type body in oracle collection (Types)
Goto Forum:
  


Current Time: Tue Sep 02 21:22:53 CDT 2014

Total time taken to generate the page: 0.07215 seconds