13 Replies Latest reply: Feb 7, 2013 4:43 AM by user545194 RSS

    One value for 2 rows

    user545194
      Hi,

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

      Desired output: To show the service price only once, but both service tasks.
      1     Maintenance 1     Maintenance 2     125,6
      1     Maintenance 3     Maintenance 4     
      DDL
      CREATE TABLE "XXX"."SERVICE_PCK" 
         (     "SID" NUMBER NOT NULL ENABLE, 
           "SERVICE_TASK_1" VARCHAR2(200 BYTE) NOT NULL ENABLE, 
           "SERVICE_TASK_2" VARCHAR2(200 BYTE) NOT NULL ENABLE, 
            CONSTRAINT "SERVICE_PCK_PK" PRIMARY KEY ("SID", "SERVICE_TASK_1", "SERVICE_TASK_2")
      CREATE TABLE "XXX"."SERVICE_PRICE" 
         (     "SID" NUMBER NOT NULL ENABLE, 
           "SERVICE_PRICE" NUMBER(6,2), 
            CONSTRAINT "SERVICE_FEE_PK" PRIMARY KEY ("SID")
      From a business standpoint not good, because 2 tasks cost only 1 price.

      Query showing price in both rows:
      SELECT SERVICE_PCK.SID,
        SERVICE_PCK.SERVICE_TASK_1,
        SERVICE_PCK.SERVICE_TASK_2,
        SERVICE_PRICE.SERVICE_PRICE
      FROM SERVICE_PCK
      INNER JOIN SERVICE_PRICE
      ON SERVICE_PCK.SID = SERVICE_PRICE.SID
      GROUP BY SERVICE_PCK.SID,
        SERVICE_PCK.SERVICE_TASK_1,
        SERVICE_PCK.SERVICE_TASK_2,
        SERVICE_PRICE.SERVICE_PRICE
      ORDER BY SERVICE_PCK.SERVICE_TASK_1
      1     Maintenance 1     Maintenance 2     125,6
      1     Maintenance 3     Maintenance 4     125,6
      Thanks for your help!
        • 1. Re: One value for 2 rows
          _Karthick_
          This looks like a reporting requirement, this must not be done in SQL. What is the reporting tool that you are using? It must be don in that.
          • 2. Re: One value for 2 rows
            chris227
            You might try
            SELECT SERVICE_PCK.SID,
              SERVICE_PCK.SERVICE_TASK_1,
              SERVICE_PCK.SERVICE_TASK_2,
            case
            when
            lag( SERVICE_PCK.SID) over (
                order by SERVICE_PCK.SID, SERVICE_PCK.SERVICE_TASK_1, SERVICE_PCK.SERVICE_TASK_2
            ) != SERVICE_PCK.SID
            then SERVICE_PRICE.SERVICE_PRICE
            end SERVICE_PRICE
            FROM SERVICE_PCK
            INNER JOIN SERVICE_PRICE
            ON SERVICE_PCK.SID = SERVICE_PRICE.SID
            GROUP BY SERVICE_PCK.SID,
              SERVICE_PCK.SERVICE_TASK_1,
              SERVICE_PCK.SERVICE_TASK_2,
              SERVICE_PRICE.SERVICE_PRICE
            ORDER BY SERVICE_PCK.SERVICE_TASK_1
            Edited by: chris227 on 01.02.2013 02:09

            Edited by: chris227 on 01.02.2013 02:10

            Edited by: chris227 on 01.02.2013 02:11

            Edited by: chris227 on 01.02.2013 02:14
            • 3. Re: One value for 2 rows
              user545194
              Your query returns:
              1     Maintenance 1     Maintenance 2     125,6
              1     Maintenance 3     Maintenance 4     125,6
              • 4. Re: One value for 2 rows
                chris227
                no wait i should spent a second on it ;-)

                Edited by: chris227 on 01.02.2013 02:10
                • 5. Re: One value for 2 rows
                  chris227
                  ok, here we go, i corrected it. The meaning of the case clause is: Every time the SERVICE_PCK.SID changes regarding to the given order, the price will be displayed.
                  Hehe, i am a moron, didnt execute it, because the insert statements were missing, just wrote it down, last correction on it, sorry.

                  Edited by: chris227 on 01.02.2013 02:14
                  • 6. Re: One value for 2 rows
                    Colectionaru
                    Hi,

                    Since you have
                    CONSTRAINT "SERVICE_PCK_PK" PRIMARY KEY ("SID", "SERVICE_TASK_1", "SERVICE_TASK_2")
                    and
                    CONSTRAINT "SERVICE_FEE_PK" PRIMARY KEY ("SID")
                    you dont need the group by clause
                    Just include
                    (case row_number() over (partition by SERVICE_PCK.SID order by SERVICE_PCK.SID,SERVICE_PCK.SERVICE_TASK_1)
                      when 1 then SERVICE_PRICE.SERVICE_PRICE
                    else 
                      null
                      end)    as price 
                    Regards,
                    Colectionaru
                    • 7. Re: One value for 2 rows
                      user545194
                      Thanks for the solution! That did it.
                      • 8. Re: One value for 2 rows
                        user545194
                        Your modified query returned:
                        1     Maintenance 1     Maintenance 2     null
                        1     Maintenance 3     Maintenance 4     null
                        Thanks anyway for your help.
                        • 9. Re: One value for 2 rows
                          user545194
                          Hi,

                          I modified the query according to your suggestion, but an additional row is being returned:
                          1     Maintenance 1     Maintenance 2         0
                          1     Maintenance 1     Maintenance 2     125,6
                          1     Maintenance 3     Maintenance 4         0
                          Why is that?
                          • 10. Re: One value for 2 rows
                            chris227
                            Because the statement stated above that you can leave out the group by clause is false.
                            You cant since you have serveral rows in your first table with the same id. It would only be possible if the sid was the solely part of the key, but it isnt.
                            If you provide some valid testdata (with testdata as (... ), we wil could find a solution very quickly.
                            • 11. Re: One value for 2 rows
                              user545194
                              Here is some test data:
                              INSERT INTO "XXX"."SERVICE_PCK" (SID, SERVICE_TASK_1, SERVICE_TASK_2) VALUES ('2', 'Main 5', 'Main 6')
                              INSERT INTO "XXX"."SERVICE_PCK" (SID, SERVICE_TASK_1, SERVICE_TASK_2) VALUES ('2', 'Main 7', 'Main 8')
                              INSERT INTO "XXX"."SERVICE_PCK" (SID, SERVICE_TASK_1, SERVICE_TASK_2) VALUES ('3', 'Main 9', 'Main 10')
                              INSERT INTO "XXX"."SERVICE_PCK" (SID, SERVICE_TASK_1, SERVICE_TASK_2) VALUES ('3', 'Main 11', 'Main 12')
                              INSERT INTO "XXX"."SERVICE_PCK" (SID, SERVICE_TASK_1, SERVICE_TASK_2) VALUES ('4', 'Main 13', 'Main 14')
                              INSERT INTO "XXX"."SERVICE_PCK" (SID, SERVICE_TASK_1, SERVICE_TASK_2) VALUES ('4', 'Main 15', 'Main 16')
                              INSERT INTO "XXX"."SERVICE_PRICE" (SID, SERVICE_PRICE) VALUES ('2', '456,3')
                              INSERT INTO "XXX"."SERVICE_PRICE" (SID, SERVICE_PRICE) VALUES ('3', '712,9')
                              INSERT INTO "XXX"."SERVICE_PRICE" (SID, SERVICE_PRICE) VALUES ('4', '960,2')
                              • 12. Re: One value for 2 rows
                                chris227
                                That shuold work as Colectionaru suggested. (I was wrong with my last statement btw.)
                                SELECT
                                  SERVICE_PCK.SID,
                                  SERVICE_PCK.SERVICE_TASK_1,
                                  SERVICE_PCK.SERVICE_TASK_2,
                                case
                                when
                                row_number() over (partition by  SERVICE_PCK.SID
                                    order by SERVICE_PCK.SERVICE_TASK_1, SERVICE_PCK.SERVICE_TASK_2
                                ) = 1
                                then SERVICE_PRICE.SERVICE_PRICE
                                end SERVICE_PRICE
                                FROM SERVICE_PCK
                                INNER JOIN SERVICE_PRICE
                                ON SERVICE_PCK.SID = SERVICE_PRICE.SID
                                order by sid, SERVICE_TASK_1, SERVICE_TASK_2
                                • 13. Re: One value for 2 rows
                                  user545194
                                  Yes, this returns the desired result.

                                  Thanks!