July 26, 2011 at 5:08 pm
Fist of all I would like to say hello to whom reads this, this is my first post on your forum. I'm fairly new to SQL programming and am having trouble merging in data from a temp table that I'm creating on the fly into a table that I've already created.
Here's my table I'm trying to merge the data into.
[/u]
DESC QM_RESPIRATORY
Name Type
PAT_ENC_CSN_ID NUMBER(18)
INP_ADM_DATE DATE
HOSP_DISCH_TIME DATE
HSP_ACCOUNT_ID NUMBER(18)
PAT_ID VARCHAR2(18)
PATIENT_MRN VARCHAR2(18)
LINE NUMBER(10)
ICD9_CODE VARCHAR2(20)
DX_NAME VARCHAR2(200)
ADT_PAT_CLASS_C VARCHAR2(18)
PAT_NAME VARCHAR2(50)
PROV_NAME VARCHAR2(254)
PROV_ID VARCHAR2(18)
Merge code
CREATE TABLE RESP_PROV AS
SELECT DISTINCT
ENC.PAT_ENC_CSN_ID,
ENC.PCP_PROV_ID,
ENC.CONTACT_DATE,
SER.PROV_NAME,
SER.PROV_ID
FROM HC_ADM.HC_PAT_ENC ENC
INNER JOIN HC_ADM.HC_CLARITY_SER SER ON ENC.PCP_PROV_ID = SER.PROV_ID
WHERE ENC.CONTACT_DATE >= '01-JAN-2011';
CREATE INDEX RESP_PROV_PAT_ENC_CSN_ID ON RESP_PROV(PAT_ENC_CSN_ID);
EXEC DBMS_STATS.GATHER_TABLE_STATS('h365123', 'RESP_PROV', CASCADE => TRUE);
MERGE INTO QM_RESPIRATORY TGT
USING RESP_PROV SRC
ON (SRC.PAT_ENC_CSN_ID = TGT.PAT_ENC_CSN_ID)
WHEN MATCHED THEN
UPDATE SET
TGT.PROV_NAME = SRC.PROV_NAME,
TGT.PROV_ID = SRC.PROV_ID
WHEN NOT MATCHED THEN
INSERT
(TGT.PROV_NAME,
TGT.PROV_ID
)
VALUES
(TGT.PROV_NAME,
TGT.PROV_ID
);
EXEC DBMS_STATS.GATHER_TABLE_STATS('h365123', 'QM_RESPIRATORY', CASCADE => TRUE);
COMMIT;
DROP TABLE RESP_PROV;
Here's the error
CREATE TABLE succeeded.
CREATE INDEX succeeded.
anonymous block completed
Error starting at line 15 in command:
MERGE INTO QM_RESPIRATORY TGT
USING RESP_PROV SRC
ON (SRC.PAT_ENC_CSN_ID = TGT.PAT_ENC_CSN_ID)
WHEN MATCHED THEN
UPDATE SET
TGT.PROV_NAME = SRC.PROV_NAME,
TGT.PROV_ID = SRC.PROV_ID
WHEN NOT MATCHED THEN
INSERT
(TGT.PROV_NAME,
TGT.PROV_ID
)
VALUES
(TGT.PROV_NAME,
TGT.PROV_ID
)
Error at Command Line:15 Column:0
Error report:
SQL Error: ORA-38101: Invalid column in the INSERT VALUES Clause: "TGT"."PROV_NAME"
38101. 00000 - "Invalid column in the INSERT VALUES Clause: %s"
*Cause: INSERT VALUES clause refers to the destination table columns
*Action:
anonymous block completed
commited
DROP TABLE RESP_PROV succeeded.
Any help is greatly appreciated, thanks!!
July 27, 2011 at 10:45 am
While trying to trouble shoot this a little attention to detial goes a long way. I noticed that I don't have the source values set, I actually had two target values set.
WHEN NOT MATCHED THEN
INSERT
(TGT.PROV_NAME,
TGT.PROV_ID
)
VALUES
(TGT.PROV_NAME,
TGT.PROV_ID
);
This should fix the problem.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply