xxx 2

[mirogak]

Hello everyone,

Not sure if you can help but I am stuck with this query.

I have a view that looks like below. I hope you can read this table.

ITEM_ID|STAGE |COUNT|TRIM_SIZE|BINDING
2083109|BUDGET |512 |8 X 10||
2083109|P.N. |464 |8 X 10|Perf Bnd|
2083109|R.T. |432 |8 X 10||
2083109|TRANS |544 |8 X 10||

The data shows different page count values for the same item at different stages. I want to show this info into one row by using the SUM and DECODE functions, like this:

SELECT nt1.inventory_item_id,
sum(DECODE (nt1.stage, 'BUDGET', nt1.COUNT, 0)) b_count,
sum(DECODE (nt1.stage, 'TRANS', nt1.COUNT, 0)) t_count,
sum(DECODE (nt1.stage, 'P.N.', nt1.COUNT, 0)) pn_count,
sum(DECODE (nt1.stage, 'R.T.', nt1.COUNT, 0)) rt_count,
nt1.trim_size
FROM scorecard_inner_v nt1
GROUP BY nt1.inventory_item_id,
nt1.trim_size

I can get it in one line if I select only up to the field TRIM_SIZE. But I also want to include the last field BINDING. If I use just the decode function then I end with up with two rows and not one. If use the SUM and DECODE function together like with the other fields, then it doesnt work. Any suggestions.

PS: by the way, I want the value 'Perf Bnd' to appear for the BINDING field in that one row that I want outputted.

Thanks,
Mirgoak

 

[SantaMufasa]

Mirogak/Mirgoak,

(BTW, which is the correct spelling for your screen handle?)

I can get it in one line if I select only up to the field TRIM_SIZE

It seems like this is you main problem, correct?...That your output line is wrapping if your SELECT includes "trim_size", right?


Prior to your SELECT statement, issue the SQL*Plus command:

SET LINESIZE 2000

...And if you want 'Perf Bnd' to appear on your one line of output, how about if you change your SELECT statement to read:

SELECT   nt1.inventory_item_id,
         sum(DECODE (nt1.stage, 'BUDGET', nt1.COUNT, 0)) b_count,
         sum(DECODE (nt1.stage, 'TRANS', nt1.COUNT, 0)) t_count,
         sum(DECODE (nt1.stage, 'P.N.', nt1.COUNT, 0)) pn_count,
         sum(DECODE (nt1.stage, 'R.T.', nt1.COUNT, 0)) rt_count,
         nt1.trim_size,
         [B][I]max(nvl(binding,' ')) Binding [/I][/B]
FROM     scorecard_inner_v nt1
GROUP BY nt1.inventory_item_id,
           nt1.trim_size

Let us know if I've misunderstood something about your inquiry.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[mirogak]

Mufasa,

You rock buddy ... 'max(nvl(binding,'')) Binding' is exactly what I needed.

Thanks a bunch ...

and my handle is 'mirogak' ... I was just so frustrated last night.

Ciao,
Mirogak

 

[kskid]

Mirogak,

It's always nice to acknowledge your appreciation with a star.

-l1