INSERT INTO TT_SPEC_LIST SE

[smsinger3]

Hello all. I am having trouble with this insert statement. I believe it's the inner Select statement that I have in bold. Is there a way to do this without using a cursor or a separate update sql statement? I'm just trying to make this as efficient as possible.

INSERT INTO TT_SPEC_LIST
SELECT A.TTCL_FLD1 as FLD1,
(SELECT MAX(B1.SCHS_CODE)
FROM TB_SCHEDULES B1,
TB_SCHEDULE_CODES C1
WHERE B1.SCHS_COMPONENT_FK = B.COMP_CODE AND
C1.SCHC_CODE = B1.SCHS_STEP_FK) as FLD2,
A.TTCL_FLD3 as FLD3
FROM TT_COMP_LIST A,
TB_COMPONENTS B
WHERE B.COMP_CHILD_PARENT_FK = A.TTCL_COMP_CODE AND B.COMP_COLLECTION_TYPE_FK = 99;

Thanks,

Steve

 

[carp]

You can't use an in-line view as a column.
Try this instead:

INSERT INTO TT_SPEC_LIST
SELECT A.TTCL_FLD1 as FLD1,
C.FLD2,
A.TTCL_FLD3 as FLD3
FROM TT_COMP_LIST A,
TB_COMPONENTS B,
(SELECT MAX(B1.SCHS_CODE)as FLD2
FROM TB_SCHEDULES B1,
TB_SCHEDULE_CODES C1
WHERE B1.SCHS_COMPONENT_FK = B.COMP_CODE AND
C1.SCHC_CODE = B1.SCHS_STEP_FK) C,
WHERE B.COMP_CHILD_PARENT_FK = A.TTCL_COMP_CODE
AND B.COMP_COLLECTION_TYPE_FK = 99;

 

[smsinger3]

I tried this and got the following error: PLS-00201: identifier 'B.COMP_CODE' must be declared.

I think it's in the imbedded "Select" statement where B.COMP_CODE is referring to the outer FROM statement.

Confusing? Any ideas? s-)

Thanks!

 

[carp]

Hm. OK, let's try this:

INSERT INTO TT_SPEC_LIST
SELECT A.TTCL_FLD1 as FLD1,
max(C.FLD2),
A.TTCL_FLD3 as FLD3
FROM TT_COMP_LIST A,
TB_COMPONENTS B,
(SELECT B1.SCHS_COMPONENT_FK, B1.SCHS_CODE as FLD2
FROM TB_SCHEDULES B1,
TB_SCHEDULE_CODES C1
WHERE C1.SCHC_CODE = B1.SCHS_STEP_FK) C,
WHERE B.COMP_CHILD_PARENT_FK = A.TTCL_COMP_CODE
AND B.COMP_COLLECTION_TYPE_FK = 99
AND C.SCHS_COMPONENT_FK = B.COMP_CODE