creation of temp table within a proc ISSue

[Healthc12]

create or replace
PROCEDURE BANDIT(
rc1 in out spm.rctl)
IS
BEGIN
open rc1 for

"CREATE GLOBAL TEMP TABLE Bandi"
(TA_SERVICING_TYPE CHAR(2),
TA_TCV_PVD_UID_PHYS NUMBER(10,0),
TA_tcm_uid NUMBER(10,0),
TA_OPP_LOB_CODE VARCHAR2(50),
TA_MEM_ID VARCHAR2(80),
TA_MEM_FORMATTED_NAME VARCHAR2(64),
TA_MEM_DATE_OF_BIRTH DATE,
TA_cny_desc VARCHAR2(50),
TA_PVD_NAME VARCHAR2(64),
TA_dxc_code_display VARCHAR2(11),
TA_dxc_desc_SHORT VARCHAR2(50),
TA_TCM_DATE_ADMITTED DATE,
TA_TCM_DAYS_AUTHD DATE,
TA_TAU_LAST_UPDATE_DATE DATE)
on commit delete rows

I am trying ti create a temp table within a proc as shown above. Keep receiving errors. Thanks for your assistance in advance!

 

[SantaMufasa]

Health,

I'm not certain what you are attempting with the cursor, but if, as you said in your post's subject line, that you want "creation of temp table within a proc", then here is code that accomplishes that:

create or replace PROCEDURE BANDIT is
    sql_stm varchar2(2000);
BEGIN
sql_stm := 'CREATE GLOBAL TEMPORARY TABLE Bandi
 (TA_SERVICING_TYPE        CHAR(2),
   TA_TCV_PVD_UID_PHYS      NUMBER(10,0),
   TA_tcm_uid               NUMBER(10,0),
   TA_OPP_LOB_CODE          VARCHAR2(50),
   TA_MEM_ID                VARCHAR2(80),
   TA_MEM_FORMATTED_NAME    VARCHAR2(64),
   TA_MEM_DATE_OF_BIRTH     DATE,
   TA_cny_desc              VARCHAR2(50),
   TA_PVD_NAME              VARCHAR2(64),
   TA_dxc_code_display      VARCHAR2(11),
   TA_dxc_desc_SHORT        VARCHAR2(50),
   TA_TCM_DATE_ADMITTED     DATE,
   TA_TCM_DAYS_AUTHD        DATE,
   TA_TAU_LAST_UPDATE_DATE  DATE) on commit delete rows';

    execute immediate sql_stm;
end;
/

Procedure created.

execute bandit

PL/SQL procedure successfully completed.

SQL> DESC BANDI
 Name                          Null?    Type
 ----------------------------- -------- ------------
 TA_SERVICING_TYPE                      CHAR(2)
 TA_TCV_PVD_UID_PHYS                    NUMBER(10)
 TA_TCM_UID                             NUMBER(10)
 TA_OPP_LOB_CODE                        VARCHAR2(50)
 TA_MEM_ID                              VARCHAR2(80)
 TA_MEM_FORMATTED_NAME                  VARCHAR2(64)
 TA_MEM_DATE_OF_BIRTH                   DATE
 TA_CNY_DESC                            VARCHAR2(50)
 TA_PVD_NAME                            VARCHAR2(64)
 TA_DXC_CODE_DISPLAY                    VARCHAR2(11)
 TA_DXC_DESC_SHORT                      VARCHAR2(50)
 TA_TCM_DATE_ADMITTED                   DATE
 TA_TCM_DAYS_AUTHD                      DATE
 TA_TAU_LAST_UPDATE_DATE                DATE

BTW, you had a minor syntax problem with the CREATE statement...you must fully spell out "TEMPORARY". Also, double quotes are valid in Oracle and PL/SQL when identifying aliases or when creating non-standard Oracle identifiers. The rest of the time, use single quotes.

Let us know if all this helps resolve your needs.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Healthc12]

Thanks, but now I need to utilize the table.
SELECT
CASE
WHEN
ttp.TCV_FACILITY_YN = 'Y'
AND ttp.TCV_TYPE = 'R'
AND ttp.TCV_PRIMARY_YN = 'Y' THEN 'FACILITY'
WHEN
ttp.TCV_FACILITY_YN = 'N'
AND ttp.TCV_TYPE = 'R'
AND ttp.TCV_PRIMARY_YN = 'Y' THEN 'ADMITTING DOCTOR'
ELSE
'NO MATCH'
END AS SERVICING_TYPE,
ttp.TCV_PVD_UID_PHYS AS SERVICING_FACILITY,
tcm.tcm_uid,
opp.OPP_LOB_CODE AS A_PLAN,
m.MEM_ID AS MEMBER_ID,
m.MEM_FORMATTED_NAME AS MEMBER_NAME,
m.MEM_DATE_OF_BIRTH AS MEMBER_DOB,
cc.cny_desc AS MEMBER_COUNTY,
initcap(p.pvd_first_name)||' '||initcap(p.pvd_last_name) AS PROVIDER,
d.dxc_code_display AS ICD9,
d.dxc_desc_SHORT AS PRIMARY_DIAGNOSIS,
tcm.TCM_DATE_ADMITTED AS DATE_ADMITTED,
tcm.TCM_DAYS_AUTHD AS AUTH_DAYS,
t.TAU_LAST_UPDATE_DATE AS LAST_NOTE
FROM
TAU_TREATMENT_AUTHORIZATION t
LEFT OUTER JOIN MEM_MEMBER m ON t.TAU_MEM_UID = m.MEM_UID
LEFT OUTER JOIN CNY_COUNTY cc ON m.mem_cny_code = cc.cny_code
LEFT OUTER JOIN DXC_DIAGNOSIS_CODE d ON t.TAU_DXC_UID_PRIMARY = DXC_UID
LEFT OUTER JOIN TCM_TAU_COMPONENT tcm ON t.tau_uid = tcm.tcm_tau_uid
LEFT OUTER JOIN pvd_provider p ON m.mem_pvd_uid_pcp = p.pvd_uid
LEFT OUTER JOIN TCV_TCM_PROVIDER ttp ON tcm.TCM_UID = ttp.TCV_TCM_UID
LEFT OUTER JOIN MPG_MEM_PLAN_GROUP mpg ON t.TAU_MEM_UID = mpg.mpg_mem_uid
LEFT OUTER JOIN OPP_ORG_PAYER_PLAN opp ON mpg.mpg_opp_id = opp.opp_id
WHERE t.TAU_POS_SUMMARY = 'Inpatient Hospital'
AND tcm.TCM_DATE_DISCHARGED IS NULL
AND LENGTH(tcm.TCM_DATE_ADMITTED) > 0
ORDER BY 1;
END;

 

[SantaMufasa]

...Okay...and what is the question?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Healthc12]

How do I load the following into the table?
SELECT
CASE
WHEN
ttp.TCV_FACILITY_YN = 'Y'
AND ttp.TCV_TYPE = 'R'
AND ttp.TCV_PRIMARY_YN = 'Y' THEN 'FACILITY'
WHEN
ttp.TCV_FACILITY_YN = 'N'
AND ttp.TCV_TYPE = 'R'
AND ttp.TCV_PRIMARY_YN = 'Y' THEN 'ADMITTING DOCTOR'
ELSE
'NO MATCH'
END AS SERVICING_TYPE,
ttp.TCV_PVD_UID_PHYS AS SERVICING_FACILITY,
tcm.tcm_uid,
opp.OPP_LOB_CODE AS A_PLAN,
m.MEM_ID AS MEMBER_ID,
m.MEM_FORMATTED_NAME AS MEMBER_NAME,
m.MEM_DATE_OF_BIRTH AS MEMBER_DOB,
cc.cny_desc AS MEMBER_COUNTY,
initcap(p.pvd_first_name)||' '||initcap(p.pvd_last_name) AS PROVIDER,
d.dxc_code_display AS ICD9,
d.dxc_desc_SHORT AS PRIMARY_DIAGNOSIS,
tcm.TCM_DATE_ADMITTED AS DATE_ADMITTED,
tcm.TCM_DAYS_AUTHD AS AUTH_DAYS,
t.TAU_LAST_UPDATE_DATE AS LAST_NOTE
FROM
TAU_TREATMENT_AUTHORIZATION t
LEFT OUTER JOIN MEM_MEMBER m ON t.TAU_MEM_UID = m.MEM_UID
LEFT OUTER JOIN CNY_COUNTY cc ON m.mem_cny_code = cc.cny_code
LEFT OUTER JOIN DXC_DIAGNOSIS_CODE d ON t.TAU_DXC_UID_PRIMARY = DXC_UID
LEFT OUTER JOIN TCM_TAU_COMPONENT tcm ON t.tau_uid = tcm.tcm_tau_uid
LEFT OUTER JOIN pvd_provider p ON m.mem_pvd_uid_pcp = p.pvd_uid
LEFT OUTER JOIN TCV_TCM_PROVIDER ttp ON tcm.TCM_UID = ttp.TCV_TCM_UID
LEFT OUTER JOIN MPG_MEM_PLAN_GROUP mpg ON t.TAU_MEM_UID = mpg.mpg_mem_uid
LEFT OUTER JOIN OPP_ORG_PAYER_PLAN opp ON mpg.mpg_opp_id = opp.opp_id
WHERE t.TAU_POS_SUMMARY = 'Inpatient Hospital'
AND tcm.TCM_DATE_DISCHARGED IS NULL
AND LENGTH(tcm.TCM_DATE_ADMITTED) > 0
ORDER BY 1;
END;

 

[SantaMufasa]

How do I load the following into the table?

Precede the SELECT statement with:

INSERT INTO BANDI

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.