IF in WITH structure

Question

Could you tell what is the problem with this code. It gets error Compilation errors for

Error:

PL/SQL: ORA-00933: SQL command not properly ended

Text:

IF iCnt > 0 THEN


  WITH S600 AS (


Prod_KEY    NUMBER; 
iCount      NUMBER;                                             

BEGIN
  WITH TEMP_All AS
            (SELECT * FROM TEMP1
             UNION ALL
             SELECT * FROM TEMP2
             UNION ALL
             SELECT * FROM TEMP3) 

             SELECT COUNT(*) 
             INTO iCount
             FROM TEMP_ALL 

             IF iCount > 0 THEN     
                     TEMP_OUT AS( 
                              SELECT Key1, count(*) as cnt 
                              FROM TEMP_ALL),  

                      TempKey AS(
                      SELECT BarSuid FROM TEMP_OUT
                      WHERE cnt = (SELECT MAX(cnt) FROM TEMP_OUT));
              ELSE
                   TempKey AS(
                   SELECT Key1 FROM PRODUCT 
                   WHERE Key1  NOT IN ( SELECT Key1  FROM PRODUCT_DET)); 
              END IF;              

               SELECT  key2
                  INTO PROD_KEY 
                  FROM PRODUCT,TempKey
                  WHERE PRODUCT.Key1 = TempKey.Key1 
                AND TempKey.Key1 IS NOT NULL;

EXCEPTION
    WHEN OTHERS
    THEN
        dbms_output.put_line(SQLCode);

    END;

Answer 1

"WITH S600 AS (" starts a SQL statement, but what follows ("Prod_KEY NUMBER; iCount NUMBER;") is PL/SQL. Also, WITH isn't used to declare a SQL statement - you were probably thinking of an explicit cursor, which is declared using "CURSOR xxx IS SELECT ...;".

Your PL/SQL block needs to start with DECLARE. e.g.:

DECLARE
    Prod_KEY NUMBER; iCount NUMBER;
BEGIN
    SELECT COUNT(*) 
    INTO iCount
    FROM (SELECT * FROM TEMP1
          UNION ALL SELECT * FROM TEMP2
          UNION ALL SELECT * FROM TEMP3);
    IF iCount > 0 THEN ...

I think you should be able to take it from there.

Answer 2

I don't know if this is just a part of a bigger procedure or something, the markup in your question is kinda confusing.

Anyway, what I was able to notice is that you forgot a semicolon (;) at the end of your first SELECT. It should be:

SELECT COUNT(*) 
INTO   iCount
FROM   TEMP_ALL;