Home » SQL & PL/SQL » SQL & PL/SQL » Oracle query error (oracle 11g)
| Oracle query error [message #573580] |
Mon, 31 December 2012 08:05  |
 |
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 #573584 is a reply to message #573583] |
Mon, 31 December 2012 08:35  |
ind9
Messages: 53
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 |
 |
Michel Cadot
Messages: 54188
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Mon, 13 February 2012 19:41Michel Cadot wrote on Mon, 13 February 2012 17:49With 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 #573589 is a reply to message #573580] |
Mon, 31 December 2012 09:11 |
flyboy
Messages: 1670
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 #573592 is a reply to message #573591] |
Mon, 31 December 2012 09:36 |
flyboy
Messages: 1670
Registered: November 2006
|
Senior Member |
|
|
ind9 wrote on Mon, 31 December 2012 16:22Are 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.
|
|
|
|
Goto Forum:
Current Time: Wed May 22 08:30:05 CDT 2013
Total time taken to generate the page: 0.14385 seconds
|
] 
Members
Search
Help
Register
Login
Home
