Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Update Syntax problem ora-00936 2

Status
Not open for further replies.
cmmrfrds

cmmrfrds

Programmer
Feb 13, 2000
4,690
US
First, I tried a From clause where I got an ora-00933, "sql command not properly ended", then I tried a subquery and get the error ora-00936. Any ideas? Here is the SQL.

/* SQL to update the HBOC staging table after it is loaded */
UPDATE RVU.RVU_STAGING_HBOC HBOC
SET HBOC.UNIT_CHARGE_AMOUNT = aout.UNIT_CHARGE_AMOUNT,
HBOC.UNIT_RVU = aout.UNIT_RVU,
HBOC.PRICE_PER_RVU = aout.PRICE_PER_RVU,
HBOC.RELATIVE_VALUE_UNIT = aout.RELATIVE_VALUE_UNIT,
HBOC.INCLUSION_YN = aout.INCLUSION_YN
(Select
arowid,
UNIT_CHARGE_AMOUNT,
UNIT_RVU,
PRICE_PER_RVU,
RELATIVE_VALUE_UNIT,
INCLUSION_YN
From
(
SELECT
aview.arowid,
CALENDAR_PERIOD,
FISCAL_PERIOD,
DETAIL_TYPE,
PERFORMING_PROV_ID,
BILLING_PROVIDER_ID,
MODIFIER_ONE,
MODIFIER_TWO,
MODIFIER_THREE,
MODIFIER_FOUR,
POS_TYPE_NUMERIC,
SERV_DEPT,
LOC_ID,
ORIG_SERVICE_DATE,
PROC_CODE,
CPT_CODE,
PROCEDURE_QUANTITY,
PC_AMOUNT,
PAT_MRN_ID,
ACCOUNT_ID,
POST_DATE,
PAYOR_ID,
CATEGORY_ID,
UNIT_CHARGE_AMOUNT,
UNIT_RVU,
PRICE_PER_RVU,
CASE WHEN PROCEDURE_QUANTITY < 0 THEN
-1 * ROUND((CASE WHEN BASIC_RVU = 0 THEN BASIC_RVU
ELSE (BASIC_RVU * MOD4_RVU * MOD3_RVU * MOD2_RVU * MOD1_RVU) END),2)
ELSE
ROUND((CASE WHEN BASIC_RVU = 0 THEN BASIC_RVU
ELSE (BASIC_RVU * MOD4_RVU * MOD3_RVU * MOD2_RVU * MOD1_RVU) END),2)
END AS RELATIVE_VALUE_UNIT,
INCLUSION_YN
FROM
(
SELECT
ROWIDTOCHAR(hboc.rowid) as arowid,
TO_CHAR(HBOC.POST_DATE,'YYYYMM') AS CALENDAR_PERIOD,
TO_CHAR(ADD_MONTHS(POST_DATE,6),'YYYYMM') AS FISCAL_PERIOD,
PERFORMING_PROV_ID,
BILLING_PROVIDER_ID,
DETAIL_TYPE,
HBOC.MODIFIER_ONE,
HBOC.MODIFIER_TWO,
HBOC.MODIFIER_THREE,
HBOC.MODIFIER_FOUR,
POS_TYPE_NUMERIC,
SUBSTR(CLARITY_SER_DEPT.DEPARTMENT_ID,6,3) AS SERV_DEPT,
LOC_ID,
ORIG_SERVICE_DATE,
PROC_CODE,
CPT_CODE,
PROCEDURE_QUANTITY,
PC_AMOUNT,
PAT_MRN_ID,
ACCOUNT_ID,
POST_DATE,
PAYOR_ID,
HBOC.CATEGORY_ID,
(1 + (CASE when MOD1.RVU_CHANGE_PCT is not null then (MOD1.RVU_CHANGE_PCT/100)
ELSE 0 END)) AS MOD1_RVU,
(1 + (CASE when MOD2.RVU_CHANGE_PCT is not null then (MOD2.RVU_CHANGE_PCT/100)
ELSE 0 END)) AS MOD2_RVU,
(1 + (CASE when MOD3.RVU_CHANGE_PCT is not null then (MOD3.RVU_CHANGE_PCT/100)
ELSE 0 END)) AS MOD3_RVU,
(1 + (CASE when MOD4.RVU_CHANGE_PCT is not null then (MOD4.RVU_CHANGE_PCT/100)
ELSE 0 END)) AS MOD4_RVU,
(CASE WHEN REFERENCE.UNIT_RVU IS NULL AND REFERENCE.PRICE_PER_RVU IS NULL then 0
WHEN REFERENCE.PRICE_PER_RVU IS NULL then
REFERENCE.UNIT_RVU
ELSE (PC_AMOUNT/REFERENCE.PRICE_PER_RVU) END) AS BASIC_RVU,
REFERENCE.UNIT_CHARGE_AMOUNT,
REFERENCE.UNIT_RVU,
REFERENCE.PRICE_PER_RVU,
(CASE WHEN EAP.MODIFIER = 'TC' THEN 'TC'
WHEN ((EAP.CATEGORY_CODE_ID IN (102,103,104,109) OR
(EAP.CATEGORY_CODE_ID >= 18 AND EAP.CATEGORY_CODE_ID <= 31))
AND PROV_DEPT.GL_PREFIX NOT IN ('1500', '1501')) THEN 'L'
WHEN ((EAP.CATEGORY_CODE_ID IN (102,103,104,109) OR
(EAP.CATEGORY_CODE_ID >= 40 AND EAP.CATEGORY_CODE_ID <= 50))
AND PROV_DEPT.GL_PREFIX NOT IN ('1490', '1491')) THEN 'R'
WHEN CLARITY_SER.PROVIDER_TYPE_C IN (0.00, 122.00, 125.00, 126.00, 127.00, 128.00)
THEN 'TYPEC'
WHEN EAP.CATEGORY_CODE_ID IN (106.00, 108.00, 110.00, 111.00, 15.00, 16.00,
17.00, 54.00, 57.00, 60.00, 61.00, 63.00, 64.00, 67.00, 68.00, 69.00, 72.00,
73.00, 74.00, 75.00, 76.00, 77.00, 78.00, 79.00,
80.00, 81.00, 82.00, 84.00, 91.00, 97.00) THEN 'CODEX'
WHEN CLARITY_SER_DEPT.LINE IS NULL THEN 'NO DEPT'
ELSE 'YES' END) AS INCLUSION_YN
FROM RVU.RVU_STAGING_HBOC HBOC
LEFT JOIN RVU.RVU_STAGING_REFERENCE REFERENCE
ON HBOC.CPT_CODE=REFERENCE.CPT_CODE
AND HBOC.CPT_CODE = REFERENCE.PROC_CODE
AND REFERENCE.CALENDAR_PERIOD = HBOC.CALENDAR_PERIOD
LEFT JOIN CLARITY.CLARITY_EAP EAP
ON EAP.PROC_ID=REFERENCE.PROC_ID
LEFT JOIN CLARITY.CLARITY_EPG CLARITY_EPG
ON EAP.PROC_GROUP_ID = CLARITY_EPG.PROC_GROUP_ID
LEFT JOIN CLARITY.CLARITY_SER CLARITY_SER
ON CLARITY_SER.PROV_ID=HBOC.PERFORMING_PROV_ID
LEFT JOIN CLARITY.CLARITY_SER_DEPT CLARITY_SER_DEPT
ON CLARITY_SER.PROV_ID=CLARITY_SER_DEPT.PROV_ID
AND CLARITY_SER_DEPT.LINE = 1
LEFT JOIN CLARITY.CLARITY_DEP PROV_DEPT
ON PROV_DEPT.DEPARTMENT_ID=CLARITY_SER_DEPT.DEPARTMENT_ID
LEFT JOIN CLARITY.CLARITY_MOD MOD1
ON MOD1.EXTERNAL_ID = HBOC.MODIFIER_ONE
LEFT JOIN CLARITY.CLARITY_MOD MOD2
ON MOD2.EXTERNAL_ID = HBOC.MODIFIER_TWO
LEFT JOIN CLARITY.CLARITY_MOD MOD3
ON MOD3.EXTERNAL_ID = HBOC.MODIFIER_THREE
LEFT JOIN CLARITY.CLARITY_MOD MOD4
ON MOD4.EXTERNAL_ID = HBOC.MODIFIER_FOUR
WHERE
(HBOC.POST_DATE between last_day(add_months(sysdate, -7) + 1)
and last_day(add_months(sysdate, -6)))
) aView
) aout)
where arowid = hboc.ROWIDTOCHAR(rowid)

 
Sort by date Sort by votes

Your SQL statement is malformed (wrong syntax) begining here:
Code: 
UPDATE ...etc...
       HBOC.RELATIVE_VALUE_UNIT = aout.RELATIVE_VALUE_UNIT,
[red][b]        HBOC.INCLUSION_YN = aout.INCLUSION_YN
(Select
       arowid,[/b][/red]
      UNIT_CHARGE_AMOUNT,  
      UNIT_RVU,            
    PRICE_PER_RVU,
    RELATIVE_VALUE_UNIT,                       
    INCLUSION_YN
From      
(
SELECT ...ETC...
[3eyes]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
LKBrwnDBA, can you give me another hint on how to handle this I am still not sure how to update more than 1 field at a time.
 
Upvote 0 Downvote
cmmrfrds,


is pretty much the definitive source for this sort of problem. Tom's use of the update statement against a join makes it much easier to see what's going on.

I have yet to see a significantly better explanation that Mr Kyte's anywhere.

Regards

Tharg

Grinding away at things Oracular
 
Upvote 0 Downvote
Thank you thargtheslayer, that does provide syntax along with a reasonable explanation. I will try it at work on Monday. Do you know if rowid will work in place of a unique constraint or primary key? If that doesn't work, what is the best way to assign a unique value to a column which sole purpose will be update the record later. This table is used for cleaning or preprocessing the data for the warehouse.
 
Upvote 0 Downvote
Cmmrfrds, I frequently UPDATE/DELETE based upon ROWID as unique method of addressing a record, but you do not want to use it as a link (i.e., a foreign-key value in a child record) since ROWID can change without warning as a result of an export/import or some other coalescence scheme.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Thank you SantaMufasa, since the table is transaction data (that will be copied to another table) without good logical key that will save me making a surrogate key on the table. Yes, I would not use it as a foreign key.
 
Upvote 0 Downvote
The Query worked fine, thank you for the good advice. Here is the syntax of the query that worked successfully for anybody that runs into a similar problem with updating.

/* SQL to update the HBOC staging table after it is loaded */
UPDATE RVU.RVU_STAGING_HBOC
SET (UNIT_CHARGE_AMOUNT,
UNIT_RVU,
PRICE_PER_RVU,
RELATIVE_VALUE_UNIT,
INCLUSION_YN) =
(Select
UNIT_CHARGE_AMOUNT,
UNIT_RVU,
PRICE_PER_RVU,
RELATIVE_VALUE_UNIT,
INCLUSION_YN
From
(
SELECT
arowid,
CALENDAR_PERIOD,
FISCAL_PERIOD,
DETAIL_TYPE,
PERFORMING_PROV_ID,
BILLING_PROVIDER_ID,
MODIFIER_ONE,
MODIFIER_TWO,
MODIFIER_THREE,
MODIFIER_FOUR,
POS_TYPE_NUMERIC,
SERV_DEPT,
LOC_ID,
ORIG_SERVICE_DATE,
PROC_CODE,
CPT_CODE,
PROCEDURE_QUANTITY,
PC_AMOUNT,
PAT_MRN_ID,
ACCOUNT_ID,
POST_DATE,
PAYOR_ID,
CATEGORY_ID,
UNIT_CHARGE_AMOUNT,
UNIT_RVU,
PRICE_PER_RVU,
CASE WHEN PROCEDURE_QUANTITY < 0 THEN
-1 * ROUND((CASE WHEN BASIC_RVU = 0 THEN BASIC_RVU
ELSE (BASIC_RVU * MOD4_RVU * MOD3_RVU * MOD2_RVU * MOD1_RVU) END),2)
ELSE
ROUND((CASE WHEN BASIC_RVU = 0 THEN BASIC_RVU
ELSE (BASIC_RVU * MOD4_RVU * MOD3_RVU * MOD2_RVU * MOD1_RVU) END),2)
END AS RELATIVE_VALUE_UNIT,
INCLUSION_YN
FROM
(
SELECT
ROWIDTOCHAR(hboc.rowid) as arowid,
TO_CHAR(HBOC.POST_DATE,'YYYYMM') AS CALENDAR_PERIOD,
TO_CHAR(ADD_MONTHS(POST_DATE,6),'YYYYMM') AS FISCAL_PERIOD,
PERFORMING_PROV_ID,
BILLING_PROVIDER_ID,
DETAIL_TYPE,
HBOC.MODIFIER_ONE,
HBOC.MODIFIER_TWO,
HBOC.MODIFIER_THREE,
HBOC.MODIFIER_FOUR,
POS_TYPE_NUMERIC,
SUBSTR(CLARITY_SER_DEPT.DEPARTMENT_ID,6,3) AS SERV_DEPT,
LOC_ID,
ORIG_SERVICE_DATE,
PROC_CODE,
CPT_CODE,
PROCEDURE_QUANTITY,
PC_AMOUNT,
PAT_MRN_ID,
ACCOUNT_ID,
POST_DATE,
PAYOR_ID,
HBOC.CATEGORY_ID,
(1 + (CASE when MOD1.RVU_CHANGE_PCT is not null then (MOD1.RVU_CHANGE_PCT/100)
ELSE 0 END)) AS MOD1_RVU,
(1 + (CASE when MOD2.RVU_CHANGE_PCT is not null then (MOD2.RVU_CHANGE_PCT/100)
ELSE 0 END)) AS MOD2_RVU,
(1 + (CASE when MOD3.RVU_CHANGE_PCT is not null then (MOD3.RVU_CHANGE_PCT/100)
ELSE 0 END)) AS MOD3_RVU,
(1 + (CASE when MOD4.RVU_CHANGE_PCT is not null then (MOD4.RVU_CHANGE_PCT/100)
ELSE 0 END)) AS MOD4_RVU,
(CASE WHEN REFERENCE.UNIT_RVU IS NULL AND REFERENCE.PRICE_PER_RVU IS NULL then 0
WHEN REFERENCE.PRICE_PER_RVU IS NULL then
REFERENCE.UNIT_RVU
ELSE (PC_AMOUNT/REFERENCE.PRICE_PER_RVU) END) AS BASIC_RVU,
REFERENCE.UNIT_CHARGE_AMOUNT,
REFERENCE.UNIT_RVU,
REFERENCE.PRICE_PER_RVU,
(CASE WHEN EAP.MODIFIER = 'TC' THEN 'TC'
WHEN ((EAP.CATEGORY_CODE_ID IN (102,103,104,109) OR
(EAP.CATEGORY_CODE_ID >= 18 AND EAP.CATEGORY_CODE_ID <= 31))
AND PROV_DEPT.GL_PREFIX NOT IN ('1500', '1501')) THEN 'L'
WHEN ((EAP.CATEGORY_CODE_ID IN (102,103,104,109) OR
(EAP.CATEGORY_CODE_ID >= 40 AND EAP.CATEGORY_CODE_ID <= 50))
AND PROV_DEPT.GL_PREFIX NOT IN ('1490', '1491')) THEN 'R'
WHEN CLARITY_SER.PROVIDER_TYPE_C IN (0.00, 122.00, 125.00, 126.00, 127.00, 128.00)
THEN 'TYPEC'
WHEN EAP.CATEGORY_CODE_ID IN (106.00, 108.00, 110.00, 111.00, 15.00, 16.00,
17.00, 54.00, 57.00, 60.00, 61.00, 63.00, 64.00, 67.00, 68.00, 69.00, 72.00,
73.00, 74.00, 75.00, 76.00, 77.00, 78.00, 79.00,
80.00, 81.00, 82.00, 84.00, 91.00, 97.00) THEN 'CODEX'
WHEN CLARITY_SER_DEPT.LINE IS NULL THEN 'NO DEPT'
ELSE 'YES' END) AS INCLUSION_YN
FROM RVU.RVU_STAGING_HBOC HBOC
LEFT JOIN RVU.RVU_STAGING_REFERENCE REFERENCE
ON HBOC.CPT_CODE=REFERENCE.CPT_CODE
AND HBOC.CPT_CODE = REFERENCE.PROC_CODE
AND REFERENCE.CALENDAR_PERIOD = HBOC.CALENDAR_PERIOD
LEFT JOIN CLARITY.CLARITY_EAP EAP
ON EAP.PROC_ID=REFERENCE.PROC_ID
LEFT JOIN CLARITY.CLARITY_EPG CLARITY_EPG
ON EAP.PROC_GROUP_ID = CLARITY_EPG.PROC_GROUP_ID
LEFT JOIN CLARITY.CLARITY_SER CLARITY_SER
ON CLARITY_SER.PROV_ID=HBOC.PERFORMING_PROV_ID
LEFT JOIN CLARITY.CLARITY_SER_DEPT CLARITY_SER_DEPT
ON CLARITY_SER.PROV_ID=CLARITY_SER_DEPT.PROV_ID
AND CLARITY_SER_DEPT.LINE = 1
LEFT JOIN CLARITY.CLARITY_DEP PROV_DEPT
ON PROV_DEPT.DEPARTMENT_ID=CLARITY_SER_DEPT.DEPARTMENT_ID
LEFT JOIN CLARITY.CLARITY_MOD MOD1
ON MOD1.EXTERNAL_ID = HBOC.MODIFIER_ONE
LEFT JOIN CLARITY.CLARITY_MOD MOD2
ON MOD2.EXTERNAL_ID = HBOC.MODIFIER_TWO
LEFT JOIN CLARITY.CLARITY_MOD MOD3
ON MOD3.EXTERNAL_ID = HBOC.MODIFIER_THREE
LEFT JOIN CLARITY.CLARITY_MOD MOD4
ON MOD4.EXTERNAL_ID = HBOC.MODIFIER_FOUR
WHERE
(HBOC.POST_DATE between last_day(add_months(sysdate, -7) + 1)
and last_day(add_months(sysdate, -6)))
) aView
) aout
where aout.arowid = ROWIDTOCHAR(RVU_STAGING_HBOC.rowid)
)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Get part of a string with SQL 1
Replies
2
Views
854
Andrzejek
Andrzejek
Rasheed Shaik
  • Locked
  • Question
\Orant\DBS\FMCUS.MSG not found
Replies
0
Views
960
Rasheed Shaik
Rasheed Shaik
Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
4K
carp
carp
mibeach7
  • Locked
  • Question
OEM extract ORA- from string text 1
Replies
2
Views
209
mibeach7
mibeach7
Server Guy
  • Locked
  • Question
Solaris 10 update 9 iso file
Replies
0
Views
207
Server Guy
Server Guy

Part and Inventory Search

Sponsor

Back
Top