This discussion is archived
4 Replies Latest reply: Dec 11, 2012 8:28 AM by 899401 RSS

joins problem

899401 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    any suggestion on this


    thanks
  • 3. Re: joins problem
    Stew Ashton Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points