Syntax for IF inside of WITH structure
Where is the syntax eror in this code. I get "Command not properly ended" on IF iCount statement. Here is the code: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;
Software/Hardware used:
WINXP with PL/SQL against Oracle
Software/Hardware used:
WINXP with PL/SQL against Oracle
ASKED:
August 13, 2009 10:04 PM
UPDATED:
August 14, 2009 1:21 PM
Answer Wiki:
The WITH clause is part of a SELECT statement, and the scope of the named subqueries is local to the SELECT from which they were defined.
It seems that your code has many errors. I remarked some of them, but there could be more.
<pre>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 <b>-- *** MISSING SEMICOLON </b>
IF iCount > 0 THEN
TEMP_OUT AS( <b>-- *** MISSING WITH KEYWORD </b>
SELECT Key1, count(*) as cnt <b>-- *** THIS REQUIRES A GROUP BY CLAUSE</b>
FROM TEMP_ALL), <b>-- *** TEMP_ALL IS OUT OF SCOPE HERE</b>
TempKey AS(
SELECT BarSuid FROM TEMP_OUT
WHERE cnt = (SELECT MAX(cnt) FROM TEMP_OUT)); <b>-- *** SELECT STATEMENT MISSING </b>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;</pre>
I would recommend to make some basic tests with the 'WITH' clause, and once you get it to work, include it in more complex queries.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
