DSXchange

Hi,
we have requirment in which if( m2.to_loc<>m.from_loc ) ,we have to create fictious record and insert it in Target Table

like in (ex1) so i have run the Select in TOAD and its fine so i am palnning to copy same in Datastage ,But need advice on insert



Thanks,
Bobby











ex1) insert into eqt_movement_app(IRD_NO,direction,from_loc,to_loc,movement_date,
upc_code,SMART_CARD_NO,SAP_PROCESSED,LAST_MODIFIED,FILESRC,
USR,FILENAME,model_no)
values(i.IRD_NO,i.direction,j.to_loc,i.from_loc,i.movement_date,
j.upc_code,j.SMART_CARD_NO,0,sysdate,'D','SYSOP','fictSM_'||sysdate,
j.model_no);



2)SELECT ird_no,direction, from_loc, to_loc, movement_date ,sap_processed,filesrc,direction
FROM sapadmin.eqt_movement_app m
WHERE
m.SAP_PROCESSED = 0 AND
m.FILESRC IN ('P','N') AND
m.DIRECTION = 'S' AND
EXISTS -- R00 neutral transaction
(SELECT 1
FROM sapadmin.eqt_movement_app m2
WHERE m2.ird_no = m.ird_no AND
(m2.to_loc <> m.from_loc ) AND
m2.RECORD_ID =
(SELECT MAX(m4.record_id) -- last IRD movement was to ROL
FROM sapadmin.eqt_movement_app m4
WHERE m4.ird_no = m.IRD_NO AND
m4.record_id <> m.record_id))
AND NOT EXISTS -- missing transaction from ROL to dealer
(SELECT 1
FROM sapadmin.eqt_movement_app m3
WHERE m3.ird_no = m.ird_no AND
(m3.from_loc LIKE '810%' OR m3.from_loc LIKE '820%') )

Hi
To add more

RECORD_ID IRD_NO D FROM_LOC TO_LOC
2946632 R0022241458 S 8455100600889092 900270001
3165011 R0022241458 S 900270001 8455100622188010
3168849 R0022241458 S 911060001 8455100622188010

i want to create a fictious record between 3165011 and 3168849 so as
to plug the differnce.
Thanks
Bobby

Whatever you can do in TOAD or sqlplus you can do with user-defined SQL in DataStage.

The question is how do you detect the requirement. When you're using TOAD or sqlplus you're doing this in your head. You need to introspect and convert the decision-making process into an algorithm that you can implement using DataStage components.