This discussion is archived
13 Replies Latest reply: Feb 7, 2013 2:43 AM by user545194 RSS

One value for 2 rows

user545194 Newbie
Currently Being Moderated
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_Arp Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for the solution! That did it.
  • 8. Re: One value for 2 rows
    user545194 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Yes, this returns the desired result.

    Thanks!

Legend

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