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%') )
advice on insert in target
Moderators: chulett, rschirm, roy
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.