I am wanting to store a Rownum as a variable rather than use a costly Join. I need to get this from a Select statement as the Rownum will be different on various environments so it cannot be a literal string in the code.

For context, this query is executed on Oracle Siebel CRM schema and it retrieves some products of specific type and attributes.

I tried using the following SQL code in Toad and Oracle SQL Developer, however I am getting the following error:

PLS-00428: an INTO clause is expected in this SELECT statement

Here is the code

 DECLARE PROD_ROW_ID varchar(10) := NULL; BEGIN SELECT ROW_ID INTO VIS_ROW_ID FROM SIEBEL.S_PROD_INT WHERE PART_NUM = 'S0146404'; BEGIN SELECT rtrim(VIS.SERIAL_NUM) || ',' || rtrim(PLANID.DESC_TEXT) || ',' || CASE WHEN PLANID.HIGH = 'TEST123' THEN CASE WHEN to_date(PROD.START_DATE) + 30 > sysdate THEN 'Y' ELSE 'N' END ELSE 'N' END || ',' || 'GB' || ',' || rtrim(to_char(PROD.START_DATE, 'YYYY-MM-DD')) FROM SIEBEL.S_LST_OF_VAL PLANID INNER JOIN SIEBEL.S_PROD_INT PROD ON PROD.PART_NUM = PLANID.VAL INNER JOIN SIEBEL.S_ASSET NETFLIX ON PROD.PROD_ID = PROD.ROW_ID INNER JOIN SIEBEL.S_ASSET VIS ON VIS.PROM_INTEG_ID = PROD.PROM_INTEG_ID INNER JOIN SIEBEL.S_PROD_INT VISPROD ON VIS.PROD_ID = VISPROD.ROW_ID WHERE PLANID.TYPE = 'Test Plan' AND PLANID.ACTIVE_FLG = 'Y' AND VISPROD.PART_NUM = VIS_ROW_ID AND PROD.STATUS_CD = 'Active' AND VIS.SERIAL_NUM IS NOT NULL; END; END; /
6

1 Answer

In PLSQL block, columns of select statements must be assigned to variables, which is not the case in SQL statements.

The second BEGIN's SQL statement doesn't have INTO clause and that caused the error.

DECLARE PROD_ROW_ID VARCHAR (10) := NULL; VIS_ROW_ID NUMBER; DSC VARCHAR (512); BEGIN SELECT ROW_ID INTO VIS_ROW_ID FROM SIEBEL.S_PROD_INT WHERE PART_NUM = 'S0146404'; BEGIN SELECT RTRIM (VIS.SERIAL_NUM) || ',' || RTRIM (PLANID.DESC_TEXT) || ',' || CASE WHEN PLANID.HIGH = 'TEST123' THEN CASE WHEN TO_DATE (PROD.START_DATE) + 30 > SYSDATE THEN 'Y' ELSE 'N' END ELSE 'N' END || ',' || 'GB' || ',' || RTRIM (TO_CHAR (PROD.START_DATE, 'YYYY-MM-DD')) INTO DSC FROM SIEBEL.S_LST_OF_VAL PLANID INNER JOIN SIEBEL.S_PROD_INT PROD ON PROD.PART_NUM = PLANID.VAL INNER JOIN SIEBEL.S_ASSET NETFLIX ON PROD.PROD_ID = PROD.ROW_ID INNER JOIN SIEBEL.S_ASSET VIS ON VIS.PROM_INTEG_ID = PROD.PROM_INTEG_ID INNER JOIN SIEBEL.S_PROD_INT VISPROD ON VIS.PROD_ID = VISPROD.ROW_ID WHERE PLANID.TYPE = 'Test Plan' AND PLANID.ACTIVE_FLG = 'Y' AND VISPROD.PART_NUM = VIS_ROW_ID AND PROD.STATUS_CD = 'Active' AND VIS.SERIAL_NUM IS NOT NULL; END; END; /

References

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.