Z
ZIMMJE
I inherited some SQL that needs to be run. What is the best way to convert
this to be used in access. There is a lot of code so I will just include the
first few lines. Is it even possible to recreate this using a query?
CREATE TABLE PPO_RNKDEPSDS_PHYS AS
SELECT *
FROM (SELECT RANK() OVER (PARTITION BY PRXH_RPTG_GRP_NM,PRXH_RPTG_SUBGP_NM,
PRXH_RPTG_SPCGP_NM, PROV_NM
ORDER BY Round(DECODE(SIGN(Sum(b.PPTE_PTE_CMPBY_PCT)-1), -1,
Sum(b.PPTE_TOTDR_CVC_AMT),
(Sum(b.PPTE_TOTDR_CVC_AMT)/Sum(b.PPTE_PTE_CMPBY_PCT))),2) Desc) As
Rank_TACC,
RANK() OVER (PARTITION BY PRXH_RPTG_GRP_NM,PRXH_RPTG_SUBGP_NM,
PRXH_RPTG_SPCGP_NM, PROV_NM
ORDER BY Sum(b.PPTE_PTE_CMPBY_PCT) DESC) As Rank_Vol,
Round(DECODE(SIGN(Sum(b.PPTE_PTE_CMPBY_PCT)-1), -1,
(Sum(b.PPTE_TOTDR_CVC_AMT)-Sum(b.PPTE_PSCPTN_CHG_AMT)),
FROM PRVRPTG.PROV a,
PRVRPTG.PPTE_TEPSD b
this to be used in access. There is a lot of code so I will just include the
first few lines. Is it even possible to recreate this using a query?
CREATE TABLE PPO_RNKDEPSDS_PHYS AS
SELECT *
FROM (SELECT RANK() OVER (PARTITION BY PRXH_RPTG_GRP_NM,PRXH_RPTG_SUBGP_NM,
PRXH_RPTG_SPCGP_NM, PROV_NM
ORDER BY Round(DECODE(SIGN(Sum(b.PPTE_PTE_CMPBY_PCT)-1), -1,
Sum(b.PPTE_TOTDR_CVC_AMT),
(Sum(b.PPTE_TOTDR_CVC_AMT)/Sum(b.PPTE_PTE_CMPBY_PCT))),2) Desc) As
Rank_TACC,
RANK() OVER (PARTITION BY PRXH_RPTG_GRP_NM,PRXH_RPTG_SUBGP_NM,
PRXH_RPTG_SPCGP_NM, PROV_NM
ORDER BY Sum(b.PPTE_PTE_CMPBY_PCT) DESC) As Rank_Vol,
Round(DECODE(SIGN(Sum(b.PPTE_PTE_CMPBY_PCT)-1), -1,
(Sum(b.PPTE_TOTDR_CVC_AMT)-Sum(b.PPTE_PSCPTN_CHG_AMT)),
FROM PRVRPTG.PROV a,
PRVRPTG.PPTE_TEPSD b