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 Go to next message
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 #668598 is a reply to message #668596] Tue, 06 March 2018 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
IMO, the posted code is an incomprehensible mess.

We don't have your tables.
We don't have your data.

Therefore we can't run, test, debug or improve posted code.
I doubt the answer to your mystery will be posted here any time soon.
Re: select more rows and insert less [message #668599 is a reply to message #668598] Tue, 06 March 2018 09:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: select more rows and insert less [message #668600 is a reply to message #668599] Tue, 06 March 2018 10:08 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Thanks,

I think it is filtering out
Re: select more rows and insert less [message #668607 is a reply to message #668600] Tue, 06 March 2018 13:17 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I may ask, which software has produced such a query?
Re: select more rows and insert less [message #668616 is a reply to message #668607] Wed, 07 March 2018 03:39 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
It is prodecued by Oracle warehouse builder
Re: select more rows and insert less [message #668760 is a reply to message #668616] Tue, 13 March 2018 09:16 Go to previous message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi,


I hope this query produced by Oracle Warehouse builder. Please use next time SQL formatter.
Previous Topic: DBMS_CRYPTO package
Next Topic: Import data from excel to oracle Db
Goto Forum:
  


Current Time: Thu Mar 28 14:09:49 CDT 2024