Home » SQL & PL/SQL » SQL & PL/SQL » select more rows and insert less (oracle 11gr2)
select more rows and insert less [message #668596] |
Tue, 06 March 2018 08:37 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
Dear Guru,
I am unable to figure out as to why a select statement is returning 7 rows but when trying to insert the rows from select it create just 1 rows.
I have as select query which return 7 rows and i am adding create table test_put as from select statement create just 1 rows
SELECT * FROM (
SELECT
/* FILTER.INOUTGRP1, EXP_TRANSFORM_DATE.OUTGRP1, EXP_TIME_CALC.OUTGRP1 */
TRUNC( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */) )/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_DATE: EXPRESSION */ "START_DATE",
TO_CHAR( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) ,'HH24:MI:SS')/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_TIME: EXPRESSION */ "START_TIME",
CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */))
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */))
ELSE
TRUNC(TO_DATE(TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */),'DD-MON-YYYY') || to_char( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) , 'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL( (NVL( "LOOKUP_INPUT_SUBQUERY"."INITIALDELAYMINUTES$1" ,0)/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INITIALDELAYMINUTES: EXPRESSION */) , 'MINUTE'))
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.END_DATE: EXPRESSION */ "END_DATE",
CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */), 'HH24:MI:SS')
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */), 'HH24:MI:SS')
ELSE
TO_CHAR(TO_DATE(TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */),'DD-MON-YYYY') || to_char( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) , 'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL( (NVL( "LOOKUP_INPUT_SUBQUERY"."INITIALDELAYMINUTES$1" ,0)/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INITIALDELAYMINUTES: EXPRESSION */) , 'MINUTE'),'HH24:MI:SS')
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.END_TIME: EXPRESSION */ "END_TIME",
CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */))
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */))
ELSE
NULL
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.DURATION_END_DATE: EXPRESSION */ "DURATION_END_DATE",
CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */), 'HH24:MI:SS')
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */), 'HH24:MI:SS')
ELSE
NULL
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.DURATION_END_TIME: EXPRESSION */ "DURATION_END_TIME",
TRUNC(TO_DATE(TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */),'DD-MON-YYYY') || to_char( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) , 'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') - (3.01/24))/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.TRAFFIC_DATE: EXPRESSION */ "TRAFFIC_DATE",
"LOOKUP_INPUT_SUBQUERY"."LOCATION_FROM$1" "LOCATION_FROM",
"LOOKUP_INPUT_SUBQUERY"."CUPID_DIRECTION_ID$1" "CUPID_DIRECTION_ID",
"LOOKUP_INPUT_SUBQUERY"."CUPID_CHRLINECODE$1" "CUPID_CHRLINECODE",
"LOOKUP_INPUT_SUBQUERY"."CUPID_PLATFORM$1" "CUPID_PLATFORM",
"LOOKUP_INPUT_SUBQUERY"."BASICINCIDENTCAUSE$1" "BASICINCIDENTCAUSE",
"LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" "INCIDENTTYPE",
"LOOKUP_INPUT_SUBQUERY"."STOCKTYPE$1" "STOCKTYPE",
CASE WHEN (CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */))
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */))
ELSE
NULL
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.DURATION_END_DATE: EXPRESSION */) IS NOT NULL AND
((to_date(TO_CHAR((CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */))
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */))
ELSE
NULL
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.DURATION_END_DATE: EXPRESSION */),'DD-MON-YYYY') ||' '|| (CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */), 'HH24:MI:SS')
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */), 'HH24:MI:SS')
ELSE
NULL
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.DURATION_END_TIME: EXPRESSION */),'DD-MON-YYYY HH24:MI:SS') - TO_DATE( TO_CHAR((TRUNC( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */) )/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_DATE: EXPRESSION */),'DD-MON-YYYY' ) || ' ' || (TO_CHAR( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) ,'HH24:MI:SS')/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_TIME: EXPRESSION */),'DD-MON-YYYY HH24:MI:SS') ) * 86400)/60 > 0 THEN
((to_date(TO_CHAR((CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */))
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */))
ELSE
NULL
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.DURATION_END_DATE: EXPRESSION */),'DD-MON-YYYY') ||' '|| (CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */), 'HH24:MI:SS')
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */), 'HH24:MI:SS')
ELSE
NULL
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.DURATION_END_TIME: EXPRESSION */),'DD-MON-YYYY HH24:MI:SS') - TO_DATE( TO_CHAR((TRUNC( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */) )/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_DATE: EXPRESSION */),'DD-MON-YYYY' ) || ' ' || (TO_CHAR( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) ,'HH24:MI:SS')/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_TIME: EXPRESSION */),'DD-MON-YYYY HH24:MI:SS') ) * 86400)/60
WHEN ((to_date( TO_CHAR((CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */))
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */))
ELSE
TRUNC(TO_DATE(TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */),'DD-MON-YYYY') || to_char( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) , 'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL( (NVL( "LOOKUP_INPUT_SUBQUERY"."INITIALDELAYMINUTES$1" ,0)/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INITIALDELAYMINUTES: EXPRESSION */) , 'MINUTE'))
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.END_DATE: EXPRESSION */),'DD-MON-YYYY') ||' '|| (CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */), 'HH24:MI:SS')
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */), 'HH24:MI:SS')
ELSE
TO_CHAR(TO_DATE(TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */),'DD-MON-YYYY') || to_char( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) , 'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL( (NVL( "LOOKUP_INPUT_SUBQUERY"."INITIALDELAYMINUTES$1" ,0)/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INITIALDELAYMINUTES: EXPRESSION */) , 'MINUTE'),'HH24:MI:SS')
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.END_TIME: EXPRESSION */) ,'DD-MON-YYYY HH24:MI:SS') - TO_DATE( TO_CHAR((TRUNC( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */) )/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_DATE: EXPRESSION */),'DD-MON-YYYY' ) || ' ' || (TO_CHAR( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) ,'HH24:MI:SS')/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_TIME: EXPRESSION */),'DD-MON-YYYY HH24:MI:SS') ) * 86400)/60 > 0 THEN
((to_date( TO_CHAR((CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */))
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TRUNC((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */))
ELSE
TRUNC(TO_DATE(TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */),'DD-MON-YYYY') || to_char( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) , 'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL( (NVL( "LOOKUP_INPUT_SUBQUERY"."INITIALDELAYMINUTES$1" ,0)/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INITIALDELAYMINUTES: EXPRESSION */) , 'MINUTE'))
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.END_DATE: EXPRESSION */),'DD-MON-YYYY') ||' '|| (CASE WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" IN ('TCN', 'TWD') THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."RESTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.RESTIME: EXPRESSION */), 'HH24:MI:SS')
WHEN "LOOKUP_INPUT_SUBQUERY"."INCIDENTTYPE$1" ='SIG' THEN
TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."AISIGNALFIXTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.AISIGNALFIXTIME: EXPRESSION */), 'HH24:MI:SS')
ELSE
TO_CHAR(TO_DATE(TO_CHAR((NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */),'DD-MON-YYYY') || to_char( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) , 'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL( (NVL( "LOOKUP_INPUT_SUBQUERY"."INITIALDELAYMINUTES$1" ,0)/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INITIALDELAYMINUTES: EXPRESSION */) , 'MINUTE'),'HH24:MI:SS')
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.END_TIME: EXPRESSION */) ,'DD-MON-YYYY HH24:MI:SS') - TO_DATE( TO_CHAR((TRUNC( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */) )/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_DATE: EXPRESSION */),'DD-MON-YYYY' ) || ' ' || (TO_CHAR( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) ,'HH24:MI:SS')/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.START_TIME: EXPRESSION */),'DD-MON-YYYY HH24:MI:SS') ) * 86400)/60
ELSE
0
END/* ATTRIBUTE EXP_TOTAL_DURATION_IN_MINUTE.OUTGRP1.TOTAL_DURATION_IN_MINUTE: EXPRESSION */ "TOTAL_DURATION_IN_MINUTE",
to_date( '05-MAR-2018'/* ATTRIBUTE INPUT_PARAMETER.OUTGRP1.IP_DELTA_LOAD_DATE */ ,'dd-mon-yyyy')/* ATTRIBUTE EXP_PARAMETER.OUTGRP1.OUT_DELTA_LOAD_DATE: EXPRESSION */ "OUT_DELTA_LOAD_DATE",
to_number( '210'/* ATTRIBUTE INPUT_PARAMETER.OUTGRP1.IP_DELTA_LOAD_ID */ )/* ATTRIBUTE EXP_PARAMETER.OUTGRP1.OUT_DELTA_LAOD_ID: EXPRESSION */ "OUT_DELTA_LAOD_ID",
"LOOKUP_INPUT_SUBQUERY"."INTFULLTRIPCANCELLATIONS$1" "INTFULLTRIPCANCELLATIONS",
"LOOKUP_INPUT_SUBQUERY"."REPORTCOMPLETE$1" "REPORTCOMPLETE",
"LOOKUP_INPUT_SUBQUERY"."LOSTCUSTOMERHOURS$1" "LOSTCUSTOMERHOURS",
"LOOKUP_INPUT_SUBQUERY"."ISDEPOTLOCATION$1" "ISDEPOTLOCATION",
"LOOKUP_INPUT_SUBQUERY"."EFOLDERID$1" "EFOLDERID",
"LOOKUP_INPUT_SUBQUERY"."TRAINNUMBER$1" "TRAINNUMBER",
"LOOKUP_INPUT_SUBQUERY"."LCINCIDENTDESCRIPTION$1" "LCINCIDENTDESCRIPTION",
"LOOKUP_INPUT_SUBQUERY"."INITIALDELAYMINUTES$1" "INITIALDELAYMINUTES",
TRUNC((TO_DATE(TO_CHAR( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTDATE" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTDATE: EXPRESSION */) , 'DD-MON-YYYY') || TO_CHAR( (NVL( "LOOKUP_INPUT_SUBQUERY"."INCIDENTTIME" ,TO_DATE('01-JAN-1753 00:01:00','DD-MON-YYYY HH24:MI:SS'))/* ATTRIBUTE EXP_TRANSFORM_DATE.OUTGRP1.INCIDENTTIME: EXPRESSION */) , 'HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS')) - (3.01/24))/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.STARTDT_TRFDT: EXPRESSION */ "STARTDT_TRFDT",
"LOOKUP_INPUT_SUBQUERY"."CARSINVOLVED$1" "CARSINVOLVED",
"LOOKUP_INPUT_SUBQUERY"."DETAILEDCAUSECODE$1" "DETAILEDCAUSECODE",
"LOOKUP_INPUT_SUBQUERY"."LCORIGINATING$1" "LCORIGINATING",
'NO_DEFECT_FOUND'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.NO_DEFECT_FOUND_TYPE: EXPRESSION */ "NO_DEFECT_FOUND_TYPE",
'No'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.NO_DEFECT_FOUND: EXPRESSION */ "NO_DEFECT_FOUND",
'PRIMARY_FLAG'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.PRIMARY_ATTRIBUTE_TYPE: EXPRESSION */ "PRIMARY_ATTRIBUTE_TYPE",
'Yes'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.PRIMARY_ATTRIBUTE_VAL: EXPRESSION */ "PRIMARY_ATTRIBUTE_VAL",
'BOOKED_ACCESS'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.BOOKED_ACCESS_TYPE: EXPRESSION */ "BOOKED_ACCESS_TYPE",
'No'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.BOOKED_ACCESS_VAL: EXPRESSION */ "BOOKED_ACCESS_VAL",
'ENG_OVER_RUN'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.ENG_OVER_RUN_TYPE: EXPRESSION */ "ENG_OVER_RUN_TYPE",
'No'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.ENG_OVER_RUN_VAL: EXPRESSION */ "ENG_OVER_RUN_VAL",
'LANDE_FLAG'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.LANDE_FLAG_ATTRIBUTE_TYPE: EXPRESSION */ "LANDE_FLAG_ATTRIBUTE_TYPE",
'No'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.LANDE_FLAG_ATTRIBUTE_VAL: EXPRESSION */ "LANDE_FLAG_ATTRIBUTE_VAL",
'24_HOUR_REPORT'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.24_HOUR_REPORT_TYPE: EXPRESSION */ "24_HOUR_REPORT_TYPE",
'N/A'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.24_HOUR_REPORT_VAL: EXPRESSION */ "24_HOUR_REPORT_VAL",
'FINAL_REPORT_RECEIVED'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.FINAL_REPORT_RECEIVED: EXPRESSION */ "FINAL_REPORT_RECEIVED",
'N/A'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.FINAL_REPORT_RECEIVED_VAL: EXPRESSION */ "FINAL_REPORT_RECEIVED_VAL",
'LEGITIMATE_NAX_ZERO'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.LEGITMATE_NAX_TYP: EXPRESSION */ "LEGITMATE_NAX_TYP",
'No'/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.LEGITMATE_NAX_VAL: EXPRESSION */ "LEGITMATE_NAX_VAL",
CASE WHEN UPPER("LOOKUP_INPUT_SUBQUERY"."PUTINTERVENTION$1") = 'Y' THEN
'Yes'
WHEN UPPER("LOOKUP_INPUT_SUBQUERY"."PUTINTERVENTION$1") = 'N' THEN
'No'
Else
'N/A'
END/* ATTRIBUTE EXP_TIME_CALC.OUTGRP1.PUTINTERVENTION: EXPRESSION */ "PUTINTERVENTION"
FROM
(SELECT
/* JNR_ISF_LOCATION_FROM.OUTGRP1, EXP_RNK.OUTGRP1 */
ROW_NUMBER() OVER(PARTITION BY "DS_ISF"."MYFOLDERNAME" ORDER BY "DS_ISF"."MYFOLDERNAME" )/* ATTRIBUTE EXP_RNK.OUTGRP1.EXP_ISF_RNK: EXPRESSION */ "EXP_ISF_RNK",
"DS_ISF"."EFOLDERID" "EFOLDERID$1",
"DS_ISF"."AISIGNALFIXTIME" "AISIGNALFIXTIME",
"DS_ISF"."CCRRESSABTIME" "CCRRESSABTIME",
"DS_ISF"."INTFULLTRIPCANCELLATIONS" "INTFULLTRIPCANCELLATIONS$1",
"DS_ISF"."INCIDENTDATE" "INCIDENTDATE",
"DS_ISF"."INCIDENTTIME" "INCIDENTTIME",
"DS_ISF"."INITIALDELAYMINUTES" "INITIALDELAYMINUTES$1",
"DS_ISF"."REPORTCOMPLETE" "REPORTCOMPLETE$1",
"DS_ISF"."RESTIME" "RESTIME",
"DS_ISF"."STOCKTYPE" "STOCKTYPE$1",
"DS_ISF"."TRAINNUMBER" "TRAINNUMBER$1",
"DS_ISF"."MYFOLDERNAME" "MYFOLDERNAME",
"DS_ISF"."BASICINCIDENTCAUSE" "BASICINCIDENTCAUSE$1",
"DS_ISF"."INCIDENTTYPE" "INCIDENTTYPE$1",
"DS_ISF"."ISDEPOTLOCATION" "ISDEPOTLOCATION$1",
"DS_ISF"."LOSTCUSTOMERHOURS" "LOSTCUSTOMERHOURS$1",
"DS_ISF"."REMIND_TO_COMPLETE" "REMIND_TO_COMPLETE",
"CUPID_LINE"."CUPID_CHRLINECODE" "CUPID_CHRLINECODE$1",
"CUPID_PALTFORM"."CUPID_BRS_STATION_CODE" "LOCATION_FROM$1",
"CUPID_PALTFORM"."CUPID_DIRECTION_ID" "CUPID_DIRECTION_ID$1",
"CUPID_PALTFORM"."CUPID_PLATFORM" "CUPID_PLATFORM$1",
"DS_ISF"."LCINCIDENTDESCRIPTION" "LCINCIDENTDESCRIPTION$1",
"DS_ISF"."DETAILEDCAUSECODE" "DETAILEDCAUSECODE$1",
"DS_ISF"."CARSINVOLVED" "CARSINVOLVED$1",
"DS_IRF"."LCORIGINATING" "LCORIGINATING$1",
"DS_IRF"."LCORIGINATING" "LCORIGINATING_1",
"DS_IRF"."PUTINTERVENTION" "PUTINTERVENTION$1"
FROM
(SELECT
/* DS_EIRF_CUPID_LINE_MAPPINGS.INOUTGRP1 */
"DS_EIRF_CUPID_LINE_MAPPINGS"."EIRF_LINE" "EIRF_LINE",
"DS_EIRF_CUPID_LINE_MAPPINGS"."CUPID_CHRLINECODE" "CUPID_CHRLINECODE",
"DS_EIRF_CUPID_LINE_MAPPINGS"."CUPID_TINLINEID" "CUPID_TINLINEID"
FROM
"DS_EWORK"."DS_EIRF_CUPID_LINE_MAPPINGS" "DS_EIRF_CUPID_LINE_MAPPINGS") "CUPID_LINE" ,
(SELECT
/* JOINER.OUTGRP1 */
"DS_EIRF_CUPID_PLTFORM_MAPPNGS"."CUPID_BRS_STATION_CODE" "CUPID_BRS_STATION_CODE",
"DS_EIRF_CUPID_PLTFORM_MAPPNGS"."CUPID_DIRECTION_ID" "CUPID_DIRECTION_ID",
"DS_EIRF_CUPID_PLTFORM_MAPPNGS"."CUPID_CHRLINECODE" "CUPID_CHRLINECODE",
"DS_EIRF_CUPID_PLTFORM_MAPPNGS"."EIRF_LINE" "EIRF_LINE",
"DS_EIRF_CUPID_PLTFORM_MAPPNGS"."EIRF_STATION_NAME" "EIRF_STATION_NAME",
"DS_EIRF_CUPID_PLTFORM_MAPPNGS"."EIRF_DIRECTION_ID" "EIRF_DIRECTION_ID",
"DS_EIRF_CUPID_PLTFORM_MAPPNGS"."CUPID_PLATFORM" "CUPID_PLATFORM"
FROM
"DS_EWORK"."DS_EIRF_CUPID_PLTFORM_MAPPNGS" "DS_EIRF_CUPID_PLTFORM_MAPPNGS",
"DS_EWORK"."DS_EIRF_CUPID_LINE_MAPPINGS" "DS_EIRF_CUPID_LINE_MAPPINGS_1"
WHERE
( "DS_EIRF_CUPID_PLTFORM_MAPPNGS"."CUPID_CHRLINECODE" = "DS_EIRF_CUPID_LINE_MAPPINGS_1"."CUPID_CHRLINECODE" ) AND
( "DS_EIRF_CUPID_PLTFORM_MAPPNGS"."EIRF_LINE" = "DS_EIRF_CUPID_LINE_MAPPINGS_1"."EIRF_LINE"/* OPERATOR JOINER JOIN CONDITION */ ) ) "CUPID_PALTFORM" ,
(SELECT
/* DS_IRF.INOUTGRP1 */
"DS_IRF"."LCFOLDERID" "LCFOLDERID",
"DS_IRF"."LCFOLDERNAME" "LCFOLDERNAME",
"DS_IRF"."LCORIGINATING" "LCORIGINATING",
"DS_IRF"."THISFOLDERNAME" "THISFOLDERNAME",
"DS_IRF"."PUTINTERVENTION" "PUTINTERVENTION"
FROM
"DS_EWORK"."DS_IRF" "DS_IRF") "DS_IRF"
,
"DS_EWORK"."DS_ISF" "DS_ISF"
WHERE
( "DS_ISF"."DS_LOAD_DELTA_ID" >= (to_number( '210'/* ATTRIBUTE INPUT_PARAMETER.OUTGRP1.IP_DELTA_LOAD_ID */ )/* ATTRIBUTE EXP_PARAMETER.OUTGRP1.OUT_DELTA_LAOD_ID: EXPRESSION */) - 7
OR "DS_ISF"."DS_UPDATE_DELTA_ID" >= (to_number( '210'/* ATTRIBUTE INPUT_PARAMETER.OUTGRP1.IP_DELTA_LOAD_ID */ )/* ATTRIBUTE EXP_PARAMETER.OUTGRP1.OUT_DELTA_LAOD_ID: EXPRESSION */) - 7 ) AND
( "DS_ISF"."DS_DELETED_FLAG" = 'N' ) AND
( "DS_ISF"."REPORTCOMPLETE" = 'Y'/* OPERATOR FLT_OTHER: FILTER CONDITION */ ) AND
( UPPER("DS_ISF"."REPORTLINEDEPT") = UPPER("CUPID_LINE"."EIRF_LINE" (+)) ) AND
( UPPER("DS_ISF"."REPORTLOCATION") = UPPER("CUPID_PALTFORM"."EIRF_STATION_NAME" (+)) ) AND
( NVL(upper(trim("DS_ISF"."TRAINDIRECTION")),NVL(upper(trim("CUPID_PALTFORM"."EIRF_DIRECTION_ID" (+))), 'XYZ')) = NVL(upper(trim("CUPID_PALTFORM"."EIRF_DIRECTION_ID" (+))), 'XYZ') ) AND
( upper("DS_ISF"."REPORTLINEDEPT") = upper("CUPID_PALTFORM"."EIRF_LINE" (+)) ) AND
( "DS_ISF"."MYFOLDERNAME" = "DS_IRF"."LCFOLDERNAME" (+)/* OPERATOR JNR_ISF_LOCATION_FROM JOIN CONDITION */ ) ) "LOOKUP_INPUT_SUBQUERY"
WHERE
( "LOOKUP_INPUT_SUBQUERY"."EXP_ISF_RNK" = 1/* OPERATOR FILTER: FILTER CONDITION */ ))
WHERE PUTINTERVENTION <> 'N/A'
Any clue
thansk
*BlackSwan corrected {code} tags
[Updated on: Tue, 06 March 2018 08:45] by Moderator Report message to a moderator
|
|
|
|
Re: select more rows and insert less [message #668599 is a reply to message #668598] |
Tue, 06 March 2018 09:59 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The session running the select can't see the same data as the session running the insert.
When running the select standalone you aren't using the same values as when run as part of the insert.
The insert process is filtering out the other 6 lines somehow.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:38:44 CDT 2024
|