3 Replies Latest reply: Dec 10, 2012 6:46 AM by 899401 RSS

    joins problem

    899401
      Hi ,
      i am running below mentione query and i am able to get output when i am running inner query but when i run entire query there is no output,how to comment one joint condition apart from inner query and run the query again without error
      select
      TOTAL.transid ,
      to_timestamp(TOTAL.time_stamp,'yyyy-mm-dd hh24:mi:ss.FF') AS time_stamp ,
      TOTAL.mssincelast ,
      TOTAL.penetrationtime ,
      TOTAL.penetrationspeed ,
      TOTAL.penetrationspeed4850 ,
      TOTAL.penetrationspeed6000 ,
      TOTAL.penetrationspeed6680 ,
      TOTAL.penetrationdistance ,
      TOTAL.sizereached ,
      TOTAL.maxtonnage ,
      TOTAL.eastmanipbite ,
      TOTAL.westmanipbite ,
      TOTAL.eastmaniprotate ,
      TOTAL.westmaniprotate ,
      TOTAL.easttemperature ,
      TOTAL.westtemperature ,
      TOTAL.peelposition ,
      TOTAL.upsetmode ,
      TOTAL.planishmode ,
      TOTAL.insteadystate ,
      TOTAL.reductionnumber ,
      TOTAL.passnumber ,
      TOTAL.strokenumber ,
      TOTAL.returnsetpoint ,
      TOTAL.slowdownsetpoint ,
      TOTAL.sizesetpoint ,
      TOTAL.delaysetpoint ,
      TOTAL.forgespeedsetpoint ,
      TOTAL.bitesetpoint ,
      TOTAL.rotateincrementsetpoint,
      TOTAL.rotatetotalsetpoint ,
      TOTAL.passid ,
      TOTAL.strokeid ,
      TOTAL.created_by_id ,
      TOTAL.changed_by_id ,
      TOTAL.created_on_dt ,
      TOTAL.changed_on_dt ,
      TOTAL.aux1_changed_on_dt ,
      TOTAL.aux2_changed_on_dt ,
      TOTAL.aux3_changed_on_dt ,
      TOTAL.aux4_changed_on_dt ,
      TOTAL.UPD_INS
      FROM
      (
      SELECT
      STG_EXCP.transid ,
      STG_EXCP.time_stamp ,
      STG_EXCP.mssincelast ,
      STG_EXCP.penetrationtime ,
      STG_EXCP.penetrationspeed ,
      STG_EXCP.penetrationspeed4850 ,
      STG_EXCP.penetrationspeed6000 ,
      STG_EXCP.penetrationspeed6680 ,
      STG_EXCP.penetrationdistance ,
      STG_EXCP.sizereached ,
      STG_EXCP.maxtonnage ,
      STG_EXCP.eastmanipbite ,
      STG_EXCP.westmanipbite ,
      STG_EXCP.eastmaniprotate ,
      STG_EXCP.westmaniprotate ,
      STG_EXCP.easttemperature ,
      STG_EXCP.westtemperature ,
      STG_EXCP.peelposition ,
      STG_EXCP.upsetmode ,
      STG_EXCP.planishmode ,
      STG_EXCP.insteadystate ,
      STG_EXCP.reductionnumber ,
      STG_EXCP.passnumber ,
      STG_EXCP.strokenumber ,
      STG_EXCP.returnsetpoint ,
      STG_EXCP.slowdownsetpoint ,
      STG_EXCP.sizesetpoint ,
      STG_EXCP.delaysetpoint ,
      STG_EXCP.forgespeedsetpoint ,
      STG_EXCP.bitesetpoint ,
      STG_EXCP.rotateincrementsetpoint,
      STG_EXCP.rotatetotalsetpoint ,
      STG_EXCP.passid ,
      STG_EXCP.strokeid ,
      STG_EXCP.created_by_id ,
      STG_EXCP.changed_by_id ,
      STG_EXCP.created_on_dt ,
      STG_EXCP.changed_on_dt ,
      STG_EXCP.aux1_changed_on_dt ,
      STG_EXCP.aux2_changed_on_dt ,
      STG_EXCP.aux3_changed_on_dt ,
      STG_EXCP.aux4_changed_on_dt,
      'INS' as UPD_INS
      /*CASE WHEN ods.TRANSID is null --commented as
      and ods.TIME_STAMP is null --update logic not used
      then 'INS' else 'UPD' end as UPD_INS*/
      FROM(
      SELECT
      /*+ use_hash(rs,TRANS) use_hash(rs,PASSDATA) */
      rs.transid ,
      rs.time_stamp ,
      rs.mssincelast ,
      rs.penetrationtime ,
      rs.penetrationspeed ,
      rs.penetrationspeed4850 ,
      rs.penetrationspeed6000 ,
      rs.penetrationspeed6680 ,
      rs.penetrationdistance ,
      rs.sizereached ,
      rs.maxtonnage ,
      rs.eastmanipbite ,
      rs.westmanipbite ,
      rs.eastmaniprotate ,
      rs.westmaniprotate ,
      rs.easttemperature ,
      rs.westtemperature ,
      rs.peelposition ,
      rs.upsetmode ,
      rs.planishmode ,
      rs.insteadystate ,
      rs.reductionnumber ,
      rs.passnumber ,
      rs.strokenumber ,
      rs.returnsetpoint ,
      rs.slowdownsetpoint ,
      rs.sizesetpoint ,
      rs.delaysetpoint ,
      rs.forgespeedsetpoint ,
      rs.bitesetpoint ,
      rs.rotateincrementsetpoint,
      rs.rotatetotalsetpoint ,
      rs.passid ,
      rs.strokeid ,
      rs.created_by_id ,
      rs.changed_by_id ,
      rs.created_on_dt ,
      rs.changed_on_dt ,
      rs.aux1_changed_on_dt ,
      rs.aux2_changed_on_dt ,
      rs.aux3_changed_on_dt ,
      rs.aux4_changed_on_dt
      FROM ( SELECT stg.transid ,
      STG.time_stamp ,
      mssincelast ,
      penetrationtime ,
      penetrationspeed ,
      penetrationspeed4850 ,
      penetrationspeed6000 ,
      penetrationspeed6680 ,
      penetrationdistance ,
      sizereached ,
      maxtonnage ,
      eastmanipbite ,
      westmanipbite ,
      eastmaniprotate ,
      westmaniprotate ,
      easttemperature ,
      westtemperature ,
      peelposition ,
      upsetmode ,
      planishmode ,
      insteadystate ,
      reductionnumber ,
      passnumber ,
      strokenumber ,
      returnsetpoint ,
      slowdownsetpoint ,
      sizesetpoint ,
      delaysetpoint ,
      forgespeedsetpoint ,
      bitesetpoint ,
      rotateincrementsetpoint ,
      rotatetotalsetpoint ,
      passid ,
      STG.strokeid strokeid ,
      created_by_id ,
      changed_by_id ,
      created_on_dt ,
      changed_on_dt ,
      aux1_changed_on_dt ,
      aux2_changed_on_dt ,
      aux3_changed_on_dt ,
      aux4_changed_on_dt ,
      insert_date ,
      row_number() over (PARTITION BY stg.transid,stg.time_stamp
      ORDER BY stg.aux1_changed_on_dt desc, excpt.insert_date DESC) rnum
      FROM STG_TSAF_PRESS_STROKEDATA STG
      LEFT OUTER JOIN
      (SELECT SUBSTR(primary_key_value, INSTR(primary_key_value, ' |@|TRANSID', 1)
      + LENGTH(' |@|TRANSID :'),
      (INSTR(primary_key_value, ' |@|', INSTR(primary_key_value, ' |@|TRANSID', 1) + LENGTH(' |@|TRANSID :') + 1))
      -(INSTR(primary_key_value, ' |@|TRANSID', 1) + LENGTH(' |@|TRANSID :'))) AS TRANSID,
      SUBSTR(primary_key_value, instr(primary_key_value, ' |@|TIME_STAMP', 1)
      + LENGTH(' |@|TIME_STAMP :'), (instr(primary_key_value, ' |@|',
      instr(primary_key_value, ' |@|TIME_STAMP', 1) + LENGTH(' |@|TIME_STAMP :') + 1))
      -(instr(primary_key_value, ' |@|TIME_STAMP', 1) + LENGTH(' |@|TIME_STAMP :'))) AS time_stamp ,
      insert_date
      FROM mes_ods.ods_exception_table
      WHERE table_name = 'ODS_TSAF_PRESS_STROKEDATA'
      AND processed = 'NO'
      )
      excpt
      ON excpt.TRANSID = stg.TRANSID
      AND substr(to_char(to_timestamp(excpt.TIME_STAMP,'yyyy-mm-dd hh24:mi:ss.FF'), 'mmddyyyyhh24missff') , 1,17)
      = substr(to_char(to_timestamp(stg.TIME_STAMP,'yyyy-mm-dd hh24:mi:ss.FF'), 'mmddyyyyhh24missff') , 1,17)
      WHERE (
      changed_on_dt >TO_DATE('10/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
      OR excpt.TRANSID IS NOT NULL
      )) rs ==============================> inner query
      INNER JOIN (select * from mes_ods.ods_TSAF_MES_TRANS
      where starttime between trunc(last_day(add_months(sysdate, -7))+1) and systimestamp) TRANS
      ON rs.TRANSID = TRANS.TRANSID
      INNER JOIN
      (select * from
      (select transid,
      passid,
      passnumber,
      row_number() over(partition by transid, passid order by transid, time_stamp desc) rnum
      from
      mes_ods.ods_TSAF_PRESS_PASSDATA
      where time_stamp between trunc(last_day(add_months(sysdate, -7))+1) and systimestamp) rs
      where rs.rnum = 1
      ) PASSDATA
      ON rs.transid = PASSDATA.transid
      AND rs.passid = PASSDATA.passid
      WHERE rs.rnum = 1)
      stg_excp
      left outer join (select * from MES_ODS.ODS_TSAF_PRESS_STROKEDATA
      where time_stamp between trunc(last_day(add_months(sysdate, -7))+1) and systimestamp) ods
      on
      stg_excp.transid = ods.transid
      AND SUBSTR(to_char(to_timestamp(stg_excp.TIME_STAMP, 'yyyy-mm-dd hh24:mi:ss.FF'), 'mmddyyyyhh24missff'), 1, 17)
      = SUBSTR(to_char(ods.TIME_STAMP, 'mmddyyyyhh24missff'), 1, 17)
      where ods.transid is null
      and ods.TIME_STAMP is null )
      TOTAL
        • 1. Re: joins problem
          899401
          Hi,
          do not know where exactly and which join it is loosing data.inner query running perfectly

          thanks
          • 2. Re: joins problem
            708319
            You should try to split the query and find out the one that makes you lose your data.

            This is the query with alias stg_excp:

            (
            SELECT
            *
            FROM <font color="red">( SELECT *
            row_number() over (PARTITION BY stg.transid,stg.time_stamp
            ORDER BY stg.aux1_changed_on_dt desc, excpt.insert_date DESC) rnum
            FROM STG_TSAF_PRESS_STROKEDATA STG
            LEFT OUTER JOIN
            (SELECT SUBSTR(primary_key_value, INSTR(primary_key_value, ' |@|TRANSID', 1)
            + LENGTH(' |@|TRANSID :'),
            (INSTR(primary_key_value, ' |@|', INSTR(primary_key_value, ' |@|TRANSID', 1) + LENGTH(' |@|TRANSID :') + 1))
            -(INSTR(primary_key_value, ' |@|TRANSID', 1) + LENGTH(' |@|TRANSID :'))) AS TRANSID,
            SUBSTR(primary_key_value, instr(primary_key_value, ' |@|TIME_STAMP', 1)
            + LENGTH(' |@|TIME_STAMP :'), (instr(primary_key_value, ' |@|',
            instr(primary_key_value, ' |@|TIME_STAMP', 1) + LENGTH(' |@|TIME_STAMP :') + 1))
            -(instr(primary_key_value, ' |@|TIME_STAMP', 1) + LENGTH(' |@|TIME_STAMP :'))) AS time_stamp ,
            insert_date
            FROM mes_ods.ods_exception_table
            WHERE table_name = 'ODS_TSAF_PRESS_STROKEDATA'
            AND processed = 'NO'
            )
            excpt
            ON excpt.TRANSID = stg.TRANSID
            AND substr(to_char(to_timestamp(excpt.TIME_STAMP,'yyyy-mm-dd hh24:mi:ss.FF'), 'mmddyyyyhh24missff') , 1,17)
            = substr(to_char(to_timestamp(stg.TIME_STAMP,'yyyy-mm-dd hh24:mi:ss.FF'), 'mmddyyyyhh24missff') , 1,17)
            WHERE (
            changed_on_dt >TO_DATE('10/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
            OR excpt.TRANSID IS NOT NULL
            )) rs, </font>
            <font color="blue">(select * from mes_ods.ods_TSAF_MES_TRANS
            where starttime between trunc(last_day(add_months(sysdate, -7))+1) and systimestamp) TRANS</font>,
            <font color="green">(select * from
            (select transid,
            passid,
            passnumber,
            row_number() over(partition by transid, passid order by transid, time_stamp desc) rnum
            from
            mes_ods.ods_TSAF_PRESS_PASSDATA
            where time_stamp between trunc(last_day(add_months(sysdate, -7))+1) and systimestamp) rs
            where rs.rnum = 1
            )</font> PASSDATA
            where 1=1
            and rs.TRANSID = TRANS.TRANSID
            and rs.transid = PASSDATA.transid
            AND rs.passid = PASSDATA.passid
            AND rs.rnum = 1)


            - <font color="red"> Test rs Query </font>

            - <font color="blue"> Test TRANS Query </font>

            - <font color="green"> Test PASSDATA Query </font>



            Regards,
            Mik
            [url http://www.holidaysoft.it/]holidaysoft.it
            [url http://www.garganosapori.it/]GarganoSapori.it
            [url http://www.osteriaoristorante.it/]OsteriaORistorante.it
            • 3. Re: joins problem
              899401
              Hi,
              this query is working but i just wanted to understand what exactly this query is saying

              thanks