Using PL/SQL Developer and or SQL Developer and Oracle 12c Database
Looking to Create a Classic Report and need to update a Field with a Value with a Value of another Field....
but also need the Update Statement to be a Sub Query so that it is another Column in Report (Not sure if that makes sense or I'm asking correctly )
something like this but this give Error: ORA-00936: Missing Expression
Select 'Construction Cost' as "Label"
,
(
update jobpiece_closing_worksheet
set fed = (select participating_const_disb from jobpiece_closing_worksheet where id = 118)
where id = 118
) as "Federal"
from dual ;
This is what the Layout of Results Need to Look like in the "Classic Report" (Keep in mind the Report below is based on 1 I've already done)
where "Label Above would be 'Construction Cost' which is 'Participating Construction' Below
and
"Federal" Above would be 'Federal' which is Completion Report Below
and the "Value would be "set Fed " from updated statement = (select participating_const_disb from jobpiece_closing_worksheet where id = 118) --This Value is 8487491.05
118
Below is the Create Table and Insert Statement
CREATE TABLE "JOBPIECE_CLOSING_WORKSHEET" ("ID" NUMBER(*,0), "JOBPIECE" VARCHAR2(7), "PROJECT_ID" VARCHAR2(40), "PROJECT_DESC" VARCHAR2(150), "ADDED_USERID" VARCHAR2(40), "ADDED_DATE" TIMESTAMP (6) DEFAULT sysdate, "PREPARED_USERID" VARCHAR2(40), "PREPARED_DATE" TIMESTAMP (6), "APPROVED_USERID" VARCHAR2(40), "APPROVED_DATE" TIMESTAMP (6), "STATUS" VARCHAR2(25) DEFAULT 'In Processing', "FED" NUMBER(11,2), "STATE" NUMBER(11,2), "LOCAL" NUMBER(11,2), "OTHER" NUMBER(11,2), "AC" NUMBER(11,2), "CONVERTED" NUMBER(11,2), "SOFTMATCH" NUMBER(11,2), "PRIVATE" NUMBER(11,2), "NON_MONEY" NUMBER(11,2), "CIRB" VARCHAR2(1), "THIRD_PARTY_NAME" VARCHAR2(50), "STATE_CONST" NUMBER(11,2), "STATE_ENG" NUMBER(11,2), "CIRB_CONST" NUMBER(11,2), "CIRB_ENG" NUMBER(11,2), "THIRD_PARTY_CONST" NUMBER(11,2), "THIRD_PARTY_ENG" NUMBER(11,2), "RAIL_CONST" NUMBER(11,2), "RAIL_ENG" NUMBER(11,2), "FMIS_CONST" NUMBER(11,2), "FMIS_ENG" NUMBER(11,2), "PARTICIPATING_CONST_COMPL" NUMBER(11,2), "NONPARTICIPATING_CONST_COMPL" NUMBER(11,2), "PARTICIPATING_CONST_DISB" NUMBER(11,2), "NONPARTICIPATING_CONST_DISB" NUMBER(11,2), "PARTICIPATING_ENG_COMPL" NUMBER(11,2), "NONPARTICIPATING_ENG_COMPL" NUMBER(11,2), "PARTICIPATING_ENG_DISB" NUMBER(11,2), "NONPARTICIPATING_ENG_DISB" NUMBER(11,2), "FED_PARTICIPATE_YN" VARCHAR2(3) DEFAULT 'N', "PROJ_STAGE" NUMBER(1,0), "COMMENTS" VARCHAR2(256)) COMMENT ON COLUMN "JOBPIECE_CLOSING_WORKSHEET"."PARTICIPATING_CONST_COMPL" IS 'Part Code 1 = Participating' COMMENT ON COLUMN "JOBPIECE_CLOSING_WORKSHEET"."NONPARTICIPATING_CONST_COMPL" IS 'Part Code 2 = Non Participating' REM INSERTING into JOBPIECE_CLOSING_WORKSHEET SET DEFINE OFF; Insert into JOBPIECE_CLOSING_WORKSHEET (ID,JOBPIECE,PROJECT_ID,PROJECT_DESC,ADDED_USERID,ADDED_DATE,PREPARED_USERID,PREPARED_DATE,APPROVED_USERID,APPROVED_DATE,STATUS,FED,STATE,LOCAL,OTHER,AC,CONVERTED,SOFTMATCH,PRIVATE,NON_MONEY,CIRB,THIRD_PARTY_NAME,STATE_CONST,STATE_ENG,CIRB_CONST,CIRB_ENG,THIRD_PARTY_CONST,THIRD_PARTY_ENG,RAIL_CONST,RAIL_ENG,FMIS_CONST,FMIS_ENG,PARTICIPATING_CONST_COMPL,NONPARTICIPATING_CONST_COMPL,PARTICIPATING_CONST_DISB,NONPARTICIPATING_CONST_DISB,PARTICIPATING_ENG_COMPL,NONPARTICIPATING_ENG_COMPL,PARTICIPATING_ENG_DISB,NONPARTICIPATING_ENG_DISB,FED_PARTICIPATE_YN,PROJ_STAGE,COMMENTS) values (118,'2329204','NHPPIY -0044-1(098) 000','I-44: P.C. CONCRETE OVERLAY BEG. AT THE RED RIVER BRIDGE & EXTENDING NORTH TO US-70. (OTA PARTICIPATION)','16446C',to_timestamp('04-APR-19 10.16.30.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null,null,null,null,'In Processing',8487491.05,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,8487491.05,283556.57,8487491.05,549391.91,8487491.05,549391.91,0,0,283556.57,18099.36,'Yes',8,null);
There is a Value in Fed Column because I manually Updated it.... Basically looking for a Way to Update the FED Column with the same Value of the Participating_Const_Compl Because they will always be the same
Thanks in Regards
DSteele41
CREATE TABLE "JOBPIECE_CLOSING_WORKSHEET" ("ID" NUMBER(*,0), "JOBPIECE" VARCHAR2(7), "PROJECT_ID" VARCHAR2(40), "PROJECT_DESC" VARCHAR2(150), "ADDED_USERID" VARCHAR2(40), "ADDED_DATE" TIMESTAMP (6) DEFAULT sysdate, "PREPARED_USERID" VARCHAR2(40), "PREPARED_DATE" TIMESTAMP (6), "APPROVED_USERID" VARCHAR2(40), "APPROVED_DATE" TIMESTAMP (6), "STATUS" VARCHAR2(25) DEFAULT 'In Processing', "FED" NUMBER(11,2), "STATE" NUMBER(11,2), "LOCAL" NUMBER(11,2), "OTHER" NUMBER(11,2), "AC" NUMBER(11,2), "CONVERTED" NUMBER(11,2), "SOFTMATCH" NUMBER(11,2), "PRIVATE" NUMBER(11,2), "NON_MONEY" NUMBER(11,2), "CIRB" VARCHAR2(1), "THIRD_PARTY_NAME" VARCHAR2(50), "STATE_CONST" NUMBER(11,2), "STATE_ENG" NUMBER(11,2), "CIRB_CONST" NUMBER(11,2), "CIRB_ENG" NUMBER(11,2), "THIRD_PARTY_CONST" NUMBER(11,2), "THIRD_PARTY_ENG" NUMBER(11,2), "RAIL_CONST" NUMBER(11,2), "RAIL_ENG" NUMBER(11,2), "FMIS_CONST" NUMBER(11,2), "FMIS_ENG" NUMBER(11,2), "PARTICIPATING_CONST_COMPL" NUMBER(11,2), "NONPARTICIPATING_CONST_COMPL" NUMBER(11,2), "PARTICIPATING_CONST_DISB" NUMBER(11,2), "NONPARTICIPATING_CONST_DISB" NUMBER(11,2), "PARTICIPATING_ENG_COMPL" NUMBER(11,2), "NONPARTICIPATING_ENG_COMPL" NUMBER(11,2), "PARTICIPATING_ENG_DISB" NUMBER(11,2), "NONPARTICIPATING_ENG_DISB" NUMBER(11,2), "FED_PARTICIPATE_YN" VARCHAR2(3) DEFAULT 'N', "PROJ_STAGE" NUMBER(1,0), "COMMENTS" VARCHAR2(256))
COMMENT ON COLUMN "JOBPIECE_CLOSING_WORKSHEET"."PARTICIPATING_CONST_COMPL" IS 'Part Code 1 = Participating' COMMENT ON COLUMN "JOBPIECE_CLOSING_WORKSHEET"."NONPARTICIPATING_CONST_COMPL" IS 'Part Code 2 = Non Participating'REM INSERTING into JOBPIECE_CLOSING_WORKSHEETSET DEFINE OFF;Insert into JOBPIECE_CLOSING_WORKSHEET (ID,JOBPIECE,PROJECT_ID,PROJECT_DESC,ADDED_USERID,ADDED_DATE,PREPARED_USERID,PREPARED_DATE,APPROVED_USERID,APPROVED_DATE,STATUS,FED,STATE,LOCAL,OTHER,AC,CONVERTED,SOFTMATCH,PRIVATE,NON_MONEY,CIRB,THIRD_PARTY_NAME,STATE_CONST,STATE_ENG,CIRB_CONST,CIRB_ENG,THIRD_PARTY_CONST,THIRD_PARTY_ENG,RAIL_CONST,RAIL_ENG,FMIS_CONST,FMIS_ENG,PARTICIPATING_CONST_COMPL,NONPARTICIPATING_CONST_COMPL,PARTICIPATING_CONST_DISB,NONPARTICIPATING_CONST_DISB,PARTICIPATING_ENG_COMPL,NONPARTICIPATING_ENG_COMPL,PARTICIPATING_ENG_DISB,NONPARTICIPATING_ENG_DISB,FED_PARTICIPATE_YN,PROJ_STAGE,COMMENTS) values (118,'2329204','NHPPIY -0044-1(098) 000','I-44: P.C. CONCRETE OVERLAY BEG. AT THE RED RIVER BRIDGE & EXTENDING NORTH TO US-70. (OTA PARTICIPATION)','16446C',to_timestamp('04-APR-19 10.16.30.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null,null,null,null,'In Processing',8487491.05,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,8487491.05,283556.57,8487491.05,549391.91,8487491.05,549391.91,0,0,283556.57,18099.36,'Yes',8,null);