This discussion is archived
6 Replies Latest reply: Dec 11, 2012 8:47 AM by Frank Kulash RSS

Opposite functionality for Group By..?

979059 Newbie
Currently Being Moderated
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).

Can anyone please help me out to solve the problem and get the desired result?

Edited by: 976056 on Dec 10, 2012 10:53 AM
  • 1. Re: Opposite functionality for Group By..?
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Opposite functionality for Group By..?
    Frank Kulash Guru
    Currently Being Moderated
    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
    ;
     

    I hope this answers your question.
    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
    Added recursive WITH solution.
  • 3. Re: Opposite functionality for Group By..?
    979059 Newbie
    Currently Being Moderated
    Hi Frank,
    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 ) 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..?
    Hoek Guru
    Currently Being Moderated
    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..?
    979059 Newbie
    Currently Being Moderated
    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..?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    976056 wrote:
    Hi Frank,
    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.
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points