Syntax for IF inside of WITH structure

10 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following