Merge off of IN Line view

[cmmrfrds]

I made an inline view and then created a Merge Statement using the inline view. Oracle gave an error message that the select statement was missing even though there was a reference to the inline view in the Using Clause of the Merge Statement. Is there a syntax way around this or am I going to need to make an Oracle View out of the inline View. When this goes production I don't mind making an Oracle View, but during the testing it would be nice to be able to do this inline. Am I stuck with making a Oracle View? This gets into turn around(s) with the DBA which I had hoped to avoid.

 

[SantaMufasa]

cmmrfrds,

A copy of your current code would help us help you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

Here is the Error message. ORA-00928: missing SELECT keyword

I stubbed out some code in the Merge and there are some comments and code that will be implemented later, but here is the code.

/* prior to 7/1/2007 the GL information is derived, after it comes from GL table */
With TDL_DETAIL as
(
SELECT
TX_ID,
SERV_AREA_ID,
ORIG_SERVICE_DATE,
ORIG_POST_DATE,
ORIGINAL_PAYOR_ID,
PERFORMING_PROV_ID,
BILLING_PROVIDER_ID,
LOC_ID,
DEPT_ID,
PROC_ID,
POS_ID,
ACCOUNT_ID,
REFERRAL_SOURCE_ID,
R_ORIG_CHG_TX_ID,
TX_NUM,
MODIFIER_ONE,
MODIFIER_TWO,
MODIFIER_THREE,
MODIFIER_FOUR,
CHARGE,
INSURANCE_CHARGE,
PATIENT_CHARGE,
CREDIT_ADJ,
INSURANCE_PAYMENTS,
PATIENT_PAYMENTS,
TOTAL_PAYMENTS,
BAD_DEBT_AMOUNT,
PROC_QTY,
RELATIVE_VALUE_UNIT,
(CASE WHEN (PATIENT_BALANCE = INSURANCE_BALANCE * -1) AND BALANCE = 0
THEN 0 ELSE PATIENT_BALANCE END) AS PATIENT_BALANCE,
(CASE WHEN (PATIENT_BALANCE = INSURANCE_BALANCE * -1) AND BALANCE = 0
THEN 0 ELSE INSURANCE_BALANCE END) AS INSURANCE_BALANCE,
BALANCE,
LAST_PAYOR_ID,
LAST_POST_DATE,
LAST_DETAIL_TYPE,
LAST_PAYMENT_DATE,
LAST_ADJUSTMENT_DATE,
LAST_ACTION_DATE,
GL_COMPANY,
GL_LOC,
GL_DEPT,
SPLIT_AMOUNT,
SPLIT_DEPT,
trunc(sysdate) as LAST_UPDATE_DATE
FROM
(SELECT
TX_ID,
MAX(SERV_AREA_ID) AS SERV_AREA_ID,
MAX(ORIG_SERVICE_DATE) AS ORIG_SERVICE_DATE,
MAX(ORIG_POST_DATE) AS ORIG_POST_DATE,
MAX(ORIGINAL_PAYOR_ID) AS ORIGINAL_PAYOR_ID,
MAX(PERFORMING_PROV_ID) AS PERFORMING_PROV_ID,
MAX(BILLING_PROVIDER_ID) AS BILLING_PROVIDER_ID,
MAX(LOC_ID) AS LOC_ID,
MAX(DEPT_ID) AS DEPT_ID,
MAX(PROC_ID) AS PROC_ID,
MAX(POS_ID) AS POS_ID,
MAX(ACCOUNT_ID) AS ACCOUNT_ID,
MAX(REFERRAL_SOURCE_ID) AS REFERRAL_SOURCE_ID,
MAX(R_ORIG_CHG_TX_ID) AS R_ORIG_CHG_TX_ID,
MAX(TX_NUM) AS TX_NUM,
MAX(MODIFIER_ONE) AS MODIFIER_ONE,
MAX(MODIFIER_TWO) AS MODIFIER_TWO,
MAX(MODIFIER_THREE) AS MODIFIER_THREE,
MAX(MODIFIER_FOUR) AS MODIFIER_FOUR,
SUM(CASE WHEN DETAIL_TYPE = 1 OR DETAIL_TYPE = 10 THEN AMOUNT ELSE 0 END) AS CHARGE,
SUM(CASE WHEN DETAIL_TYPE = 1 THEN INSURANCE_AMOUNT ELSE 0 END) AS INSURANCE_CHARGE,
SUM(CASE WHEN DETAIL_TYPE = 1 THEN PATIENT_AMOUNT ELSE 0 END) AS PATIENT_CHARGE,
SUM(CASE WHEN DETAIL_TYPE = 21 THEN AMOUNT ELSE 0 END) AS CREDIT_ADJ,
SUM(CASE WHEN DETAIL_TYPE = 20 THEN INSURANCE_AMOUNT ELSE 0 END) AS INSURANCE_PAYMENTS,
SUM(CASE WHEN DETAIL_TYPE = 20 THEN PATIENT_AMOUNT ELSE 0 END) AS PATIENT_PAYMENTS,
SUM(CASE WHEN DETAIL_TYPE = 20 THEN AMOUNT ELSE 0 END) AS TOTAL_PAYMENTS,
SUM(BAD_DEBT_AMOUNT) AS BAD_DEBT_AMOUNT,
SUM(PROCEDURE_QUANTITY) AS PROC_QTY,
SUM(CASE WHEN DETAIL_TYPE = 1 THEN RELATIVE_VALUE_UNIT ELSE 0 END) AS RELATIVE_VALUE_UNIT,
SUM(CASE WHEN DETAIL_TYPE IN (1, 20, 21, 40, 41) THEN PATIENT_AMOUNT ELSE 0 END) AS PATIENT_BALANCE,
SUM(CASE WHEN DETAIL_TYPE IN (1, 20, 21, 40, 41) THEN INSURANCE_AMOUNT ELSE 0 END) AS INSURANCE_BALANCE,
SUM(CASE WHEN DETAIL_TYPE IN (1, 20, 21) THEN AMOUNT ELSE 0 END) AS BALANCE,
MAX(CASE WHEN LAST_PAYOR_ID IS NULL THEN FIRST_PAYOR_ID ELSE LAST_PAYOR_ID END) AS LAST_PAYOR_ID,
MAX(LAST_POST_DATE) AS LAST_POST_DATE,
MAX(LAST_DETAIL_TYPE) AS LAST_DETAIL_TYPE,
MAX(LAST_PAYMENT_DATE) AS LAST_PAYMENT_DATE,
MAX(LAST_ADJUSTMENT_DATE) AS LAST_ADJUSTMENT_DATE,
MAX(LAST_ACTION_DATE) AS LAST_ACTION_DATE,
MAX(GL_COMPANY) AS GL_COMPANY,
MAX(GL_LOC) AS GL_LOC,
MAX(GL_DEPT) AS GL_DEPT,
MAX(SPLIT_AMOUNT) AS SPLIT_AMOUNT,
MAX(SPLIT_DEPT) AS SPLIT_DEPT
FROM
/* SELECT FIELDS THESE FIELDS ONLY */
(SELECT
TRAN.TX_ID,
DETAIL_TYPE,
TDL_ID,
SERV_AREA_ID,
POST_DATE,
PROCEDURE_QUANTITY,
RELATIVE_VALUE_UNIT,
INSURANCE_AMOUNT,
PATIENT_AMOUNT,
AMOUNT,
ORIG_SERVICE_DATE,
ORIG_POST_DATE,
ORIGINAL_PAYOR_ID,
PERFORMING_PROV_ID,
BILLING_PROVIDER_ID,
LOC_ID,
DEPT_ID,
PROC_ID,
POS_ID,
ACCOUNT_ID,
REFERRAL_SOURCE_ID,
R_ORIG_CHG_TX_ID,
TX_NUM,
MODIFIER_ONE,
MODIFIER_TWO,
MODIFIER_THREE,
MODIFIER_FOUR,
(CASE WHEN EAP1.GL_NUM_DEBIT IN ('00473', '00476') THEN AMOUNT ELSE 0 END)
AS BAD_DEBT_AMOUNT,
first_value(CASE
WHEN (DETAIL_TYPE = 1 OR DETAIL_TYPE = 40 OR DETAIL_TYPE = 41) AND INSURANCE_AMOUNT <> 0 THEN CUR_PAYOR_ID
WHEN DETAIL_TYPE = 20 OR DETAIL_TYPE = 21 THEN NULL
ELSE 1 END)
OVER (PARTITION BY TRAN.TX_ID ORDER BY TDL_ID DESC NULLS LAST)
AS LAST_PAYOR_ID,
first_value(CASE
WHEN (DETAIL_TYPE = 1 OR DETAIL_TYPE = 40 OR DETAIL_TYPE = 41) AND INSURANCE_AMOUNT <> 0 THEN CUR_PAYOR_ID
WHEN DETAIL_TYPE = 20 OR DETAIL_TYPE = 21 THEN NULL
ELSE 1 END)
OVER (PARTITION BY TRAN.TX_ID ORDER BY TDL_ID ASC NULLS LAST)
AS FIRST_PAYOR_ID,
first_value(POST_DATE)
OVER (PARTITION BY TRAN.TX_ID ORDER BY TDL_ID DESC NULLS LAST)
AS LAST_POST_DATE,
first_value(DETAIL_TYPE)
OVER (PARTITION BY TRAN.TX_ID ORDER BY TDL_ID DESC NULLS LAST)
AS LAST_DETAIL_TYPE,
first_value(CASE
WHEN DETAIL_TYPE = 20 THEN TRAN.POST_DATE
ELSE NULL END)
OVER (PARTITION BY TRAN.TX_ID ORDER BY TDL_ID DESC NULLS LAST)
AS LAST_PAYMENT_DATE,
first_value(CASE
WHEN DETAIL_TYPE = 21 THEN TRAN.POST_DATE
ELSE NULL END)
OVER (PARTITION BY TRAN.TX_ID ORDER BY TDL_ID DESC NULLS LAST)
AS LAST_ADJUSTMENT_DATE,
first_value(CASE
WHEN DETAIL_TYPE IN (40, 41) THEN TRAN.POST_DATE
ELSE NULL END)
OVER (PARTITION BY TRAN.TX_ID ORDER BY TDL_ID DESC NULLS LAST)
AS LAST_ACTION_DATE,
case when tran.loc_id is null THEN 'NO LOC_ID'
when (bucket.gl_type_prefix is null) then substr(tran.loc_id,3,3)
when tran.orig_service_date < TO_DATE('07/01/2006','MM/DD/YYYY') then substr(tran.loc_id,3,3)
when tran.dept_id = 103000092410 then substr(tran.loc_id,3,3)
when (eap1.cpt_code IN ('76090', '76091', '76092', '76082', '76083') and tran.modifier_one is null)
or tran.modifier_one = '26'
then substr(tran.loc_id,3,3)
when bucket.gl_type_prefix is not null then substr(bucket.gl_type_prefix,1,3)
else SUBSTR(tran.loc_id,3,3) end as GL_COMPANY,
case when tran.loc_id is null THEN 'NO LOC_ID'
when tran.orig_service_date < TO_DATE('07/01/2006','MM/DD/YYYY') then substr(tran.loc_ID,6,3)
when (eap1.cpt_code IN ('76090', '76091', '76092', '76082', '76083') and tran.modifier_one is null)
or tran.modifier_one = '26' then '040'
when dept_id IN (103000401490) and (tran.modifier_one is null
or tran.modifier_one = '26') and pos_id NOT IN (10300450, 10300050, 10300451, 10300051,
10300638) then '040'
when eap1.proc_code = 'J9190.99' and pos_id = 10300559 then '040'
when pos.pos_loc_is_outside is null and loc_id = 103000902 then '051'
when pos.pos_loc_is_outside = 'Yes' and loc_id = 103000902 then '060'
when pos.pos_loc_is_outside is null and loc_id = 103000901 then '050'
when pos.pos_loc_is_outside = 'Yes' and loc_id = 103000902 then '060'
when tran.dept_id = 103000092410 then substr(tran.loc_id,6,3)
when (bucket.gl_type_prefix is null) THEN substr(tran.loc_id,6,3)
when bucket_pos.gl_type_prefix is not null then substr(bucket_pos.gl_type_prefix,5,3)
else substr(tran.loc_id,6,3) end as GL_LOC,
case when eap1.rpt_grp_six is not null then to_char(eap1.rpt_grp_six)
else substr(tran.dept_id,9,4) end AS GL_DEPT,
(CASE WHEN EAP1.PROC_GROUP_ID IS NULL THEN 0
WHEN EPG.PROC_GROUP_ID > 103 and (instr(proc_group_name,'%')) = 0
THEN 0
WHEN EPG.PROC_GROUP_ID > 103 THEN
((tran.amount *
(substr(proc_group_name,(instr(proc_group_name,'%')-3),3)/100) ))
ELSE 0 END) AS SPLIT_AMOUNT,
(case when EAP1.PROC_GROUP_ID > 103 then
(substr(proc_group_name,(instr(proc_group_name,'%')-7),4))
else null end) as split_dept
FROM CLARITY.CLARITY_TDL_TRAN TRAN
LEFT OUTER JOIN CLARITY.X_GL_BUCKET BUCKET_POS
ON BUCKET_POS.FACILITY_ID = TRAN.POS_ID
AND BUCKET_POS.LINE = 2
LEFT OUTER JOIN CLARITY.X_GL_BUCKET BUCKET
ON BUCKET.FACILITY_ID = TRAN.LOC_ID
AND BUCKET.LINE = 1
LEFT OUTER JOIN CLARITY.CLARITY_EAP EAP1
ON MATCH_PROC_ID = EAP1.PROC_ID
LEFT JOIN CLARITY.CLARITY_EPG EPG
ON EAP1.PROC_GROUP_ID = EPG.PROC_GROUP_ID
LEFT OUTER JOIN CLARITY.CLARITY_POS POS
ON TRAN.POS_ID = POS.POS_ID
WHERE (DETAIL_TYPE = 1
OR DETAIL_TYPE = 10
OR DETAIL_TYPE = 20
OR DETAIL_TYPE = 21
OR DETAIL_TYPE = 40
OR DETAIL_TYPE = 41 )
AND CHARGE_SLIP_NUMBER IS NOT NULL
-- AND (POST_DATE >= trunc(last_day(add_months(sysdate, -2))+1)
-- AND ORIG_POST_DATE >= trunc(last_day(add_months(sysdate, -2))+1) )
AND (POST_DATE < to_date('01/01/2003','MM/DD/YYYY')
AND ORIG_POST_DATE < to_date('01/01/2003','MM/DD/YYYY') )

) /* END OF SELECT FIELDS */
GROUP BY
TX_ID
) /* END OF PRE PROCESSING */
) --, LastUpdate as
--(
--Select max(last_update_date) from x_tdl_balances_tx_id
--) /* End of With statement */
--select * from tdl_detail where rownum < 1;
Merge Into x_tdl_balances_tx_id xtx_id
Using tdl_detail dtx_id
On xtx_id.tx_id = dtx_id.tx_id
When Matched Then
Update
Set 1=1
When Not Matched Then
Insert (xtx_id.tx_id)
Values (dtx_id.tx_id)

 

[SantaMufasa]

cmmrfrds,

If I have code that is this complex, is throwing errors, and uses techniques/syntax that I have not previously proved usable, I (personally) create a "bare-minimum" set of code that represents what I want to do.

So, I propose that you create a single table with a couple of columns, and use a minimal in-line view that tests the MERGE syntax. Once you get that working, then you can extrapolate the code to your much more complex example, above.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

I tested the Merge syntax this way and the syntax is fine.

Merge Into clarity.clarity_emp emp
Using
(select user_id from clarity.clarity_emp) demp
On (demp.user_id = emp.user_id)
When Matched Then
Update
Set emp.user_id = demp.user_id
When Not Matched Then
Insert (emp.user_id)
Values (demp.user_id);

When I put the inline view in the Using clause like above I still get the error that the Select is missing.

Merge Into x_tdl_balances_tx_id xtx_id
Using (select * from tdl_detail) dtx_id
On (xtx_id.tx_id = dtx_id.tx_id)
When Matched Then
Update
Set xtx_id = dtx_id
When Not Matched Then
Insert (xtx_id.tx_id)
Values (dtx_id.tx_id)

This appears to be a limitation on defining an inline view using the "With Clause" which normally requires its use in a Select Statement. It seems the select for the inline view cannot be in the Using Clause of the Merge. This is unfortunate since it does except a subquery, but I wanted to avoid cluttering up the Merge Statement with all that code. This is annoying since it seems logical that it should work with the inline view.