4 Replies Latest reply: Dec 11, 2012 10:28 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 is giving data but when i run entire query there is no output,i do not know which condition is having problem how to check that

      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
          AlbertoFaenza
          Hi,

          Please read SQL and PL/SQL FAQ

          Additionally when you put some code or output please enclose it between two lines starting with {noformat}
          {noformat}
          
          i.e.:
          {noformat}
          {noformat}
          SELECT ...
          {noformat}
          {noformat}
          
          I had a look at your profile:
          Handle:      896398
          Status Level:      Newbie
          Registered:      Nov 9, 2011
          Total Posts:      454
          Total Questions:      141 (106 unresolved)
          If most of your questions are unresolved I think you are not getting a great help from this forum.
          Or maybe you forgot to mark your questions as answered.
          
          Regards.
          Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: joins problem
            899401
            any suggestion on this


            thanks
            • 3. Re: joins problem
              Stew Ashton
              Comment out
              where ods.transid is null
              and ods.TIME_STAMP is null )
              Like this:
              /* where ods.transid is null
              and ods.TIME_STAMP is null*/ )
              Then see what you get back.

              You are doing a LEFT OUTER JOIN from stg_excp to ods, so that WHERE clause will probably only be true for those records in stg_excp which are not in ods.
              • 4. Re: joins problem
                899401
                can u exlpain me what this query is doing
                Hi,


                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'
                )
                thanks