0

I am new to Oracle and maybe use the wrong approach here.

Current result:

enter image description here

Expected result:

enter image description here

The problem is that in my case the text "Items" appears instead of the first item for each ID instead of above it.

My query (simplified): 

SELECT
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY sd.ID ORDER BY sd.ITEM) = 1 THEN sd.ID
        ELSE '' 
    END AS ID
    , CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY sd.ID ORDER BY sd.ITEM) = 1 THEN 'Items'
        ELSE sd.ITEM
    END AS Details
    , CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY sd.ID ORDER BY sd.ITEM) = 1 THEN TO_CHAR((sd.SUBTOTAL + sd.SUBTOTAL_TAX), 'FM9,990.00')
        ELSE '' 
    END AS Total_Price
FROM
    SHIP_DETAILS sd
/* ... */

Note: Total price in the screenshots above is just a placeholder to explain the content, it would just be a sum amount.

Can someone tell me what I am doing wrong here ?

Many thanks in advance for any help,
Mike

Mike
  • 155
  • 3
  • 14
  • Remove the single quotes. You have defined a string, not a column reference. – Gordon Linoff Jun 21 '17 at 12:22
  • @GordonLinoff: Thanks, Gordon. I want the actual string for the first row of each ID and the column values only for the other rows. I added screenshots to explain what I mean. Does that make sense ? – Mike Jun 21 '17 at 12:24

1 Answers1

1

This is too long for a comment.

The case statement does not generate new rows. So, you have a choice between 'item' or the first item.

I suspect that you want to use grouping sets (or with rollup). Here is an article that explains these group by modifiers.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks again, Gordon ! I get your point. I think ROLLUP is what I need here as I am trying to show the subtotals not next to the first item for each ID but instead on a separate row. Will look into the article. – Mike Jun 21 '17 at 12:32