6 Replies Latest reply on Dec 11, 2012 4:47 PM by Frank Kulash

# Opposite functionality for Group By..?

Hi,

I have a source table as following:
PRODUCT
PID  | QTY
P1  |             5
P2  |             1
P3   |            3

I want expected output as following:_

PID  | QTY
P1              | 1
P1              | 1
P1              | 1
P1              | 1
P1              | 1
P2              | 1
P3              | 1
P3              | 1
P3              | 1

You can see in output table P1 should be repeated 5 times, P2 1 time and P3 3 times because of their respective values from QTY column in source table.

I have tried using WITH RECURSIVE clause but it is not working for me (Getting some syntax error).

Edited by: 976056 on Dec 10, 2012 10:53 AM
• ###### 1. Re: Opposite functionality for Group By..?
How do I ask a question on the forums?
SQL and PL/SQL FAQ
• ###### 2. Re: Opposite functionality for Group By..?
Hi,

Welcome to the forum!

Here's one way:
``````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 me
It'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.

Here's a solution using a recursive WITH clause:
``````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
;``````

If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
Explain, using specific examples, how you get those results from that data.
Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
See the forum FAQ {message:id=9360002}

Edited by: Frank Kulash on Dec 10, 2012 2:01 PM
• ###### 3. Re: Opposite functionality for Group By..?
Hi Frank,

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 ) AS
(
SELECT A.PID, A.qty
FROM product A
UNION ALL
SELECT B.PID, Q.qty - 1 AS qty
FROM queryy Q , product B
WHERE Q.PID=B.PID
AND qty > 0
)
SELECT PID, 1 from queryy
ORDER BY 1;

I am thus not sure which part of the query needs correction.
• ###### 4. Re: Opposite functionality for Group By..?
Have you checked your database version and/or SQL*Plus version (or whatever tool you're using)?
You cannot use the recursive WITH clause if you're below version 11.2.0.x
• ###### 5. Re: Opposite functionality for Group By..?
Thanks for the update. This seems the problem. I re-ran the query on Online SQL editor and it worked fine. Cheers!!
• ###### 6. Re: Opposite functionality for Group By..?
Hi,
976056 wrote:
Hi Frank,

I have used following query but it is giving syntax error without any specific mention of which line might be incorrect.
How do you know that it's a syntax error? Always post the complete error message.
What front end are you using? It probably has an option for dispalying decent error messages, including line numbers. If not, switch to some from end that does give you usable error messages.
WITH RECURSIVE queryy (PID, qty ) AS
Reveiw the syntax in the SQL Language manual.
The compiler will figure out that this is a recursive WITH clause; you don't have to use the keyword RECURSIVE. (In fact, you're not allowed to.)
(
SELECT A.PID, A.qty
FROM product A
UNION ALL
SELECT B.PID, Q.qty - 1 AS qty
FROM queryy Q , product B
WHERE Q.PID=B.PID
AND qty > 0
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.)

If you make those 2 changes, then your query will run (assuming you have Oracle 11.2, of course. This shows one reason why you should always say which version of Oracle you're using.)

Do you really need to join the tables here? What is b supplying that is not already in q?

Say product.qty = 1. The 1st branch of the UNION will include that row, and the 2nd branch will also include the same pid, but with qty = 1 - 1= 0. I think you want to 2nd branch of the UNION to fail immediately if the original qty is not greater than 1 (as opposed to not greater than 0).
)
SELECT PID, 1 from queryy
ORDER BY 1;
If you make all those changes, and format your query to make it readable, then you'll have something like:
``````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
;``````
which is basically what I posted earlier.