Subselects making miseries

[christopheleroy]

I had to change:
INSERT into DEPOT_STUDY_PERSON_LINK(DEPOT_STUDY_PERSON_LINK_ID, PERSON_ID, STUDY_ID, DEPOT_ID,
INV_UPLOAD_ID,INV_EFFECTIVE_DATE, COMMIT_TIME, IS_ACTIVE, MOD_TIME)
SELECT ?n, (SELECT MIN(PERSON_ID) FROM PERSON WHERE lower(EMP521)=?v),
(SELECT STUDY_ID FROM STUDY WHERE PROTOCOL_NUMBER = ?v),
(SELECT DEPOT_ID FROM DEPOT where LOCAL_NAME = ?v),
INV_UPLOAD_ID, INV_EFFECTIVE_DATE, SYSTIMESTAMP,1, SYSTIMESTAMP
FROM INV_UPLOAD where INV_UPLOAD_ID=?n

to 2 steps:

INSERT into DEPOT_STUDY_PERSON_LINK(DEPOT_STUDY_PERSON_LINK_ID, PERSON_ID, STUDY_ID, DEPOT_ID,
INV_UPLOAD_ID,INV_EFFECTIVE_DATE, COMMIT_TIME, IS_ACTIVE, MOD_TIME)
VALUES(?n,?n,?n,?n,?n,SYSDATE,SYSTIMESTAMP,1,SYSTIMESTAMP)

and then an update statement to get the correct SYSDATE - because I did not want the app to have to know it in advance.

UPDATE DEPOT_STUDY_PERSON_LINK
SET
INV_EFFECTIVE_DATE = (SELECT INV_EFFECTIVE_DATE
FROM INV_UPLOAD
WHERE INV_UPLOAD_ID=DEPOT_STUDY_PERSON_LINK.INV_UPLOAD_ID)
WHERE DEPOT_STUDY_PERSON_LINK_ID = ?n

Likewise, I had to change the below:
INSERT into INVENTORY(inventory_id, depot_study_person_link_id, md5, row_cat, drug_name,
strength,strength_unit,
container_amt, container_unit,
pcn_number, batch,
quantity, expiry_date,
orig_json, comments,
is_active, mod_time)
SELECT idgenseq.nextval, ?n, md5, row_cat, drug_name, strength, strength_unit, container_amt, container_unit,
pcn_number, batch,
quantity, expiry_date,
orig_json, inv_comments,
1,systimestamp
FROM PROCESSED_DATA WHERE INV_UPLOAD_ID=?n and PROTOCOL_NUMBER=?v and DEPOT=?v AND NOT (IS_IGNORE_ROW=1)

to

INSERT into INVENTORY(inventory_id, depot_study_person_link_id, md5, row_cat, drug_name,
strength,strength_unit,
container_amt, container_unit,
pcn_number, batch,
quantity, expiry_date,
orig_json, comments,
is_active, mod_time)
SELECT idgenseq.nextval, DEPOT_STUDY_PERSON_LINK_ID, md5, row_cat, drug_name, strength, strength_unit, container_amt, container_unit,
pcn_number, batch,
quantity, expiry_date,
orig_json, inv_comments,
1,systimestamp
FROM PROCESSED_DATA p, DEPOT_STUDY_PERSON_LINK l
WHERE p.INV_UPLOAD_ID=?n and PROTOCOL_NUMBER=?v and DEPOT=?v AND NOT (IS_IGNORE_ROW=1)
AND l.DEPOT_STUDY_PERSON_LINK_ID = ?n

[varontron]

The problem here after cursory review I believe is that the deparser is unable to handle YADA parameters in the list of SelectItem objects. This is probably resolvable, but without a named column reference or alias, the YADA column syntax (e.g., YADA_1, YADA_2, which is typically used only for standard parameters) may have to be employed.