WITH cntr AS ( SELECT LEVEL AS n FROM ( SELECT MAX (cnt) AS max_cnt FROM product ) CONNECT BY LEVEL <= max_cnt ) SELECT p.id , 1 AS cnt FROM product p JOIN cntr c ON c.n <= p.cnt ORDER BY p.id ;
976056 wrote:... I have tried using WITH RECURSIVE clause but it is not working for meIt's hard to say what you're doing wrong when we don't know what you're doing. Please post your code.
(Getting some syntax error).The error messages often give valuable informtion about what the problem is. Always post the error message. Even if you don't understand it, maybe somebody who wants to help you can understand it and explain it for you.
WITH r (id, cnt) AS ( SELECT id , cnt FROM product UNION ALL SELECT id , cnt - 1 FROM r WHERE cnt > 1 ) SELECT id , 1 AS cnt FROM r ;
976056 wrote:How do you know that it's a syntax error? Always post the complete error message.
Thanks for the reply.
I have used following query but it is giving syntax error without any specific mention of which line might be incorrect.
WITH RECURSIVE queryy (PID, qty ) ASReveiw the syntax in the SQL Language manual.
(Both q and b have a column called qty, so you need the qualify qty with the table alias. (Even if qty occurred in only 1 table, it would still be a tgood idea to say which table that was. Always qualify all columns in a multi-table query.)
SELECT A.PID, A.qty
FROM product A
SELECT B.PID, Q.qty - 1 AS qty
FROM queryy Q , product B
AND qty > 0
)If you make all those changes, and format your query to make it readable, then you'll have something like:
SELECT PID, 1 from queryy
ORDER BY 1;
which is basically what I posted earlier.
WITH -- You don't have to say it's RECURSIVE queryy (PID, qty ) AS ( SELECT A.PID , A.qty FROM product A UNION ALL SELECT Q.PID , Q.qty - 1 AS qty FROM queryy Q -- , product B -- B isn't contributing anything WHERE -- Q.PID= B.PID -- ... so this comparison -- AND -- ... is not needed q.qty -- qty exists in both tables, so it must be qualified > 1 -- not 0 ) SELECT PID , 1 from queryy ORDER BY 1 ;