Q:
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:
Aug 13 2009 10:04 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
It seems that your code has many errors. I remarked some of them, but there could be more.
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 -- *** MISSING SEMICOLON
IF iCount > 0 THEN
TEMP_OUT AS( -- *** MISSING WITH KEYWORD
SELECT Key1, count(*) as cnt -- *** THIS REQUIRES A GROUP BY CLAUSE
FROM TEMP_ALL), -- *** TEMP_ALL IS OUT OF SCOPE HERE
TempKey AS(
SELECT BarSuid FROM TEMP_OUT
WHERE cnt = (SELECT MAX(cnt) FROM TEMP_OUT)); -- *** SELECT STATEMENT MISSING 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;
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.
