Ranking Data 1

[tommyboyau]

Hi All,
I have the following SQL script:

SELECT DISTINCT
  DIM_PMD_SUB_REASON.REASON_DESC,
  sum(BAS_PMD_SA_EVENT_DATA.STOPS_COUNT),
  ROW_NUMBER() OVER (ORDER BY sum(BAS_PMD_SA_EVENT_DATA.STOPS_COUNT) DESC) "TOP10"

FROM
  DIM_DAY,
  DIM_PMD_SUB_REASON,
  BAS_PMD_SA_EVENT_DATA,
  V_DIM_PMD_SHIFT_CREW
WHERE
	TOP10 <=10 AND
  ( DIM_PMD_SUB_REASON.PRODUCTION_LINE_CODE = BAS_PMD_SA_EVENT_DATA.PRODUCTION_LINE_CODE  AND  DIM_PMD_SUB_REASON.SUB_REASON_CODE = BAS_PMD_SA_EVENT_DATA.SUB_REASON_CODE  )
  AND  ( BAS_PMD_SA_EVENT_DATA.CALENDAR_DATE=DIM_DAY.CALENDAR_DATE  )
  AND  ( V_DIM_PMD_SHIFT_CREW.PRODUCTION_LINE_CODE=BAS_PMD_SA_EVENT_DATA.PRODUCTION_LINE_CODE AND V_DIM_PMD_SHIFT_CREW.SHIFT_START_TIME = BAS_PMD_SA_EVENT_DATA.SHIFT_START_TIME  )
  AND  (
  ( V_DIM_PMD_SHIFT_CREW.SHIFT_START_TIME BETWEEN trunc(sysdate)-10 AND trunc(sysdate)  )
  )
GROUP BY
DIM_PMD_SUB_REASON.REASON_DESC;

SELECT DISTINCT
  DIM_PMD_SUB_REASON.REASON_DESC,
  sum(BAS_PMD_SA_EVENT_DATA.STOPS_COUNT)


FROM
  DIM_DAY,
  DIM_PMD_SUB_REASON,
  BAS_PMD_SA_EVENT_DATA,
  V_DIM_PMD_SHIFT_CREW
WHERE
  ( DIM_PMD_SUB_REASON.PRODUCTION_LINE_CODE = BAS_PMD_SA_EVENT_DATA.PRODUCTION_LINE_CODE  AND  DIM_PMD_SUB_REASON.SUB_REASON_CODE = BAS_PMD_SA_EVENT_DATA.SUB_REASON_CODE  )
  AND  ( BAS_PMD_SA_EVENT_DATA.CALENDAR_DATE=DIM_DAY.CALENDAR_DATE  )
  AND  ( V_DIM_PMD_SHIFT_CREW.PRODUCTION_LINE_CODE=BAS_PMD_SA_EVENT_DATA.PRODUCTION_LINE_CODE AND V_DIM_PMD_SHIFT_CREW.SHIFT_START_TIME = BAS_PMD_SA_EVENT_DATA.SHIFT_START_TIME  )
  AND  (
  ( V_DIM_PMD_SHIFT_CREW.SHIFT_START_TIME BETWEEN trunc(sysdate)-10 AND trunc(sysdate)  )
  )
GROUP BY
DIM_PMD_SUB_REASON.REASON_DESC;

Which generates the following data:
BW250 7
Beer Supply 2
Changeover 29
Crowner 7
Depal 11
Depal A 3
Filler 45
Jones Packer 5
Labeller 60
Lid Feeder 12
Line Not Scheduled To Run 13
Packer 52
Packer - Dimac 6
Packer - Kister 20
Packer - Ocme 11
Palletiser 70
Palletiser A 2
Palletiser B 1
Pasteuriser 9
Rinser 15
Seamer 2
Shrink Wrapper B 1
Shrinkwrap 46
Unaccounted 10
Work Area 17
ZUNK 2711

How do I rank the data so it displays only the top 10 results (highest to lowest) as shown below:
ZUNK 2711
Palletiser 70
Labeller 60
Packer 52
Shrinkwrap 46
Filler 45
Changeover 29
Packer - Kister 20
Work Area 17
Rinser 15

I've tried ranking but can't seem to get it to work.
Your help is appreciated.

Thanks,

Tom

 

[SantaMufasa]

Tom,

Cause your code to become an "in-line view" to another SELECT (as I have done to your code, below). (I have emboldened the additional code.) The new, outer query asks for just the "Top 10" rows (as a result of my new WHERE clause, in bold at the bottom).:

[b]col a heading "Reason" format a20
col b heading "Stops|Count" format 99,999
SELECT a, b
from ([/b]
SELECT DISTINCT
  DIM_PMD_SUB_REASON.REASON_DESC [b]a[/b],
  sum(BAS_PMD_SA_EVENT_DATA.STOPS_COUNT) [b]b[/b]
FROM
  DIM_DAY,
  DIM_PMD_SUB_REASON,
  BAS_PMD_SA_EVENT_DATA,
  V_DIM_PMD_SHIFT_CREW
WHERE
  ( DIM_PMD_SUB_REASON.PRODUCTION_LINE_CODE = BAS_PMD_SA_EVENT_DATA.PRODUCTION_LINE_CODE  AND  DIM_PMD_SUB_REASON.SUB_REASON_CODE = BAS_PMD_SA_EVENT_DATA.SUB_REASON_CODE  )
  AND  ( BAS_PMD_SA_EVENT_DATA.CALENDAR_DATE=DIM_DAY.CALENDAR_DATE  )
  AND  ( V_DIM_PMD_SHIFT_CREW.PRODUCTION_LINE_CODE=BAS_PMD_SA_EVENT_DATA.PRODUCTION_LINE_CODE AND V_DIM_PMD_SHIFT_CREW.SHIFT_START_TIME = BAS_PMD_SA_EVENT_DATA.SHIFT_START_TIME  )
  AND  (
  ( V_DIM_PMD_SHIFT_CREW.SHIFT_START_TIME BETWEEN trunc(sysdate)-10 AND trunc(sysdate)  )
  )
GROUP BY
DIM_PMD_SUB_REASON.REASON_DESC[b])
where rownum <= 10;[/b]

Let us know if this worked well for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tommyboyau]

That script outputs the following:
"BW250",7
"Beer Supply",2
"Changeover",29
"Crowner",7
"Depal",11
"Depal A",3
"Filler",45
"Jones Packer",5
"Labeller",60
"Lid Feeder",12

I would like it ordered showing the highest value through to the lowest (but only top 10).

Also, I am implementing this code in Business Objects in a universe. I don't think this will allow for the 2 select statements. Can you offer any advice?

Thanks,

Tom

 

[SantaMufasa]

Sorry, Tom, it was late when I posted earlier and my eyesight is particularly bad when I haven't my specticles: When I saw the "DESC" at the end of "DIM_PMD_SUB_REASON.REASON_DESC", I mistook it for the "DESC" (descending) of an "ORDER BY" clause. Thinking that you had already finished the original SELECT with an "ORDERY BY...DESC", I left it as it was. Since there is not, in fact, and ORDER BY, please add this one, below, to your code, and try it all again:

...
GROUP BY
DIM_PMD_SUB_REASON.REASON_DESC
[b]ORDER BY B DESC[/b])
where rownum <= 10;

Let us know how this slight enhancement works for you. (Since it is approaching midnight here, I'm hoping you can post your findings before I soon turn it for the night.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tommyboyau]

The output worked fine (as shown below) - thankyou.
I do have another question. Is there any way of acheiving this with one select statement? I am trying to get this to work in Business Objects which I don't think will allow for two select statements.

Thanks,

Tom

Output is as follows:
ZUNK 2711
Palletiser 70
Labeller 60
Packer 52
Shrinkwrap 46
Filler 45
Changeover 29
Packer - Kister 20
Work Area 17
Rinser 15

 

[SantaMufasa]

Tom,

Frankly, from the beginning, I didn't pay may (any) attention to your first query...I didn't take the time to evaluate the differences between the two queries. If they are returning data that could/should be in one result set, then I suggest trying Oracle's UNION operator to shuffle together the results of the two queries. As a structural model, try:

Current outer SELECT
FROM 
(SELECT 1
UNION
SELECT 2)
where rownum <= 10;

Does this approach seem reasonable? If I misundertand the logical meanings of your result sets, perhaps the concept can provide you with a light at the end of the tunnel. Try it and see what you think. Let us know your findings/feelings about it.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tommyboyau]

The dataset it's returning is correct.
I was just hoping I could do this in one select statement as I am trying to implement it in Business Objects which I think only allows me to use one select statement.
Thanks for your help with all this. I will speak to some work mates.

Thanks,

Tom

 

[SantaMufasa]

Tom,

My example, above, is one select per se: One SELECT whose FROM is an in-line VIEW made up of a two-SELECT UNION. Does Business Objects not allow such a simple SELECT construct? If not, I would say that BO has serious limitations that would make it tool of questionable value in my book.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tommyboyau]

I will see how I go a bit later on today and post my results.
There isn't a direct limiation with Business Objects.
The problem is I am trying to create a "Dimension" within business objects which allows for one select statement per dimension. If I use two I think it will make the code go screwy.

I will let you know how i go.

Thanks,

Tom