sam2929
asked on
putting updates in main sql
Hi,
I have below set query which update PS_POSITION_DATA table. i want to get rid on update and instead join PS_ACTN_REASON_TBL with PS_POSITION_DATA
select
pos_id,
'' as AG_ACTION_REASON_E
from
PS_POSITION_DATA
i want
select
pos_id,
'' as AG_ACTION_REASON_E
from
PS_POSITION_DATA a
left outer join PS_ACTN_REASON_TBL b
on a.POSITION_NBR=b.POSITION_ NBR
---can anyone help here to make max(eff_dt) simple
UPDATE PS_AG_POSITIONSTMP
SET AG_ACTION_REASON_E = (SELECT A.DESCR
FROM PS_ACTN_REASON_TBL A
WHERE A.ACTION = (SELECT P.ACTION FROM PS_POSITION_DATA P
WHERE P.POSITION_NBR = PS_AG_POSITIONSTMP.POSITIO N_NBR
AND P.EFFDT = (SELECT MAX(P1.EFFDT)
FROM PS_POSITION_DATA P1
WHERE P1.POSITION_NBR = P.POSITION_NBR
AND P1.EFFDT <= $AsOfDate))
AND A.ACTION_REASON = PS_AG_POSITIONSTMP.ACTION_ REASON
AND A.EFFDT = (SELECT MAX(A1.EFFDT)
FROM PS_ACTN_REASON_TBL A1
WHERE A1.ACTION=A.ACTION
AND A1.ACTION_REASON = A.ACTION_REASON
))
! WHERE EXISTS (SELECT *
WHERE EXISTS (SELECT 1
FROM PS_ACTN_REASON_TBL A
WHERE A.ACTION = (SELECT P.ACTION FROM PS_POSITION_DATA P
WHERE P.POSITION_NBR = PS_AG_POSITIONSTMP.POSITIO N_NBR
AND P.EFFDT = (SELECT MAX(P1.EFFDT)
FROM PS_POSITION_DATA P1
WHERE P1.POSITION_NBR = P.POSITION_NBR
AND P1.EFFDT <= $AsOfDate))
AND A.ACTION_REASON = PS_AG_POSITIONSTMP.ACTION_ REASON
AND A.EFFDT = (SELECT MAX(A1.EFFDT)
FROM PS_ACTN_REASON_TBL A1
WHERE A1.ACTION=A.ACTION
AND A1.ACTION_REASON = A.ACTION_REASON
))
I have below set query which update PS_POSITION_DATA table. i want to get rid on update and instead join PS_ACTN_REASON_TBL with PS_POSITION_DATA
select
pos_id,
'' as AG_ACTION_REASON_E
from
PS_POSITION_DATA
i want
select
pos_id,
'' as AG_ACTION_REASON_E
from
PS_POSITION_DATA a
left outer join PS_ACTN_REASON_TBL b
on a.POSITION_NBR=b.POSITION_
---can anyone help here to make max(eff_dt) simple
UPDATE PS_AG_POSITIONSTMP
SET AG_ACTION_REASON_E = (SELECT A.DESCR
FROM PS_ACTN_REASON_TBL A
WHERE A.ACTION = (SELECT P.ACTION FROM PS_POSITION_DATA P
WHERE P.POSITION_NBR = PS_AG_POSITIONSTMP.POSITIO
AND P.EFFDT = (SELECT MAX(P1.EFFDT)
FROM PS_POSITION_DATA P1
WHERE P1.POSITION_NBR = P.POSITION_NBR
AND P1.EFFDT <= $AsOfDate))
AND A.ACTION_REASON = PS_AG_POSITIONSTMP.ACTION_
AND A.EFFDT = (SELECT MAX(A1.EFFDT)
FROM PS_ACTN_REASON_TBL A1
WHERE A1.ACTION=A.ACTION
AND A1.ACTION_REASON = A.ACTION_REASON
))
! WHERE EXISTS (SELECT *
WHERE EXISTS (SELECT 1
FROM PS_ACTN_REASON_TBL A
WHERE A.ACTION = (SELECT P.ACTION FROM PS_POSITION_DATA P
WHERE P.POSITION_NBR = PS_AG_POSITIONSTMP.POSITIO
AND P.EFFDT = (SELECT MAX(P1.EFFDT)
FROM PS_POSITION_DATA P1
WHERE P1.POSITION_NBR = P.POSITION_NBR
AND P1.EFFDT <= $AsOfDate))
AND A.ACTION_REASON = PS_AG_POSITIONSTMP.ACTION_
AND A.EFFDT = (SELECT MAX(A1.EFFDT)
FROM PS_ACTN_REASON_TBL A1
WHERE A1.ACTION=A.ACTION
AND A1.ACTION_REASON = A.ACTION_REASON
))
The query isn't very clear, do you want the update to be modified ? Why ? Would need some clarity on purpose so as to reply accordingly.
ASKER
no want update in select
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think johnsone is on the right track using ROW_NUMBER instead of all the MAX and EXISTS statements.
Once you get the select figured out, I would look at using MERGE to perform the update:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606
If you can provide some sample data and expected results we can probably come up with a working example.
Once you get the select figured out, I would look at using MERGE to perform the update:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606
If you can provide some sample data and expected results we can probably come up with a working example.