* The Dynamic SQL commands do not accept working
* variables with hyphens.
MOVE SPACES TO WHOPPERSQL.
* Dynamic Oracle specific SQL is being used.
* Could not get the Oracle Pro*Cobol compiler to
* accept the subselect.
String
"SELECT PR1.PO_ID "
",PR1.LINE_NBR "
",PR1.BUSINESS_UNIT "
",PR1.PROJECT_ID "
",PR1.ACTIVITY_ID "
",PR1.DISTRIB_LINE_NUM "
",PR1.RESOURCE_AMOUNT - NVL ( "
"( SELECT PR2.RESOURCE_AMOUNT "
"FROM PSF_PROJ_RESOURCE PR2 "
"WHERE PR2.PO_ID = PR1.PO_ID "
"AND RTRIM(LTRIM(LTRIM(REPLACE( "
"PR2.RESOURCE_ID,'#'),'CCA'),'CRV')) = "
"RTRIM(LTRIM(REPLACE( "
"PR1.RESOURCE_ID,'#'),'P')) "
"AND PR2.ANALYSIS_TYPE IN ('CCA','CRV') "
"AND PR2.DISTRIB_LINE_NUM = PR1.DISTRIB_LINE_NUM "
"),0) "
"FROM PSF_PROJ_RESOURCE PR1 "
"WHERE PR1.ANALYSIS_TYPE = 'COM' "
"AND TRIM(PR1.PO_ID) IN "
"(SELECT DISTINCT TRIM(PO_ID) "
"FROM TIDYSIAD "
"WHERE PO_ID != ' ' "
")"
"ORDER BY PR1.PO_ID "
",PR1.DISTRIB_LINE_NUM "
DELIMITED BY SIZE
INTO WHOPPERSQL-ARR
* Animate program and look at WHOPPERSQL to get the
* length.
MOVE 700 TO WHOPPERSQL-LEN
* Display 'WHOPPERSQL-LEN=' WHOPPERSQL-LEN
* DISPLAY 'WHOPPERSQL=' WHOPPERSQL
EXEC SQL PREPARE SQLSTMT FROM :WHOPPERSQL END-EXEC.
PERFORM E000-SQLCODE THRU E000-EXIT.
MOVE 'DECLARE' TO SEA-SQL-FUNCTION.
EXEC SQL DECLARE TIVWORK CURSOR FOR SQLSTMT END-EXEC.
PERFORM E000-SQLCODE THRU E000-EXIT.
MOVE 'OPEN' TO SEA-SQL-FUNCTION.
EXEC SQL OPEN TIVWORK END-EXEC.
PERFORM E000-SQLCODE THRU E000-EXIT.