Oracle Decode Question

[THEGREEK]

Hi,

I was just wondering if someone could help me out...I can't get by this roadblock.

This is a sample of my source data:
(Note this is just a sample..my real data has like thousands & thousands of records to work with)

AccntNum OrderNum Param ParamValue
-------- -------- ----- ----------
10000 10001 Cable 7894/AXV
10000 10001 Pair 4466
10000 10002 Cable 7987/VXV
10000 10002 Pair 8899
10050 10051 Pair 8855
10050 10051 Cable 4878/BXV

Now what I'm trying to do is achieve a Transform/Pivot type dataset (like ms access crosstab) but here's the trick, I can't use stored proc or create table...i can only use select sql statement, is this even possible?...I heard that you can use the DECODE function to do something similar, but I'm out to lunch when it comes to the DECODE function
Anyways, if anyone can help i'm trying to get the dataset above to look like this below.

AccntNum OrderNum Cable Pair
-------- -------- ----- ----
10000 10001 7894/AXV 4466
10000 10002 7987/VXV 8899
10050 10051 4878/BXV 8855

Thank you in advance,

 

[jimbopalmer]

Assuming ordernum is unique over all accountnums

Select c.AccntNum, c.OrderNum, c.ParamValue, p.ParamValue
from source_data c, source_data p where c.OrderMum = p.OrderNum and c.Param = 'Cable' and p.Param = 'Pair' order by 1,2,3;


I tried to remain child-like, all I acheived was childish.

 

[THEGREEK]

Thank you,

This looks like it works...but what is the

order by 1,2,3 ? mean?

Thanks a lot,
THEGREEK

 

[SantaMufasa]

El Greco,

When you ORDER BY in Oracle, you have four options to specify sort order:

1) explicit column/expression (example:
"Select c.AccntNum, c.OrderNum, c.ParamValue, p.ParamValue
from source_data c, source_data p where c.OrderMum = p.OrderNum and c.Param = 'Cable' and p.Param = 'Pair' Order by c.AccntNum, c.OrderNum, c.ParamValue&quot

2) SELECT-expression alias (example:
"Select c.AccntNum AcNum, c.OrderNum OID, c.ParamValue CPVal, p.ParamValue PPVAL
from source_data c, source_data p where c.OrderMum = p.OrderNum and c.Param = 'Cable' and p.Param = 'Pair' order by AcNum, OID, CPVAL;&quot

3) SELECT-expression ordinal position (example:
"Select c.AccntNum AcNum, c.OrderNum OID, c.ParamValue CPVal, p.ParamValue PPVAL
from source_data c, source_data p where c.OrderMum = p.OrderNum and c.Param = 'Cable' and p.Param = 'Pair' order by 1,2,3;&quot

4) Any combination of the above. (example:
"Select c.AccntNum AcNum, c.OrderNum OID, c.ParamValue CPVal, p.ParamValue PPVAL
from source_data c, source_data p where c.OrderMum = p.OrderNum and c.Param = 'Cable' and p.Param = 'Pair' order by AcNum, 2, c.ParamValue;&quot

Cheers,

Dave
Sandy, Utah, USA (8 inches deep in fresh powder snow) @ 20:27 GMT, 13:27 Mountain Time

 

[jimbopalmer]

I wrote that assuming that for every Cable record there would be one and only one Pair record, if this is a bad assumption, I need to do more work.

Will every OrderNum have one Cable record and one Pair record?

I tried to remain child-like, all I acheived was childish.

 

[sem]

Another way accessing table only once:

select AccntNum,OrderNum
, max(decode(Param,'Cable',ParamValue,null)) Cable
, max(decode(Param,'Pair',ParamValue,null)) Pair
group by AccntNum,OrderNum

It makes no assumptions regarding existance and uniqueness of Pair but rather selects the maximum value if more than 1 exist or null if none.

Regards, Dima

 

[THEGREEK]

hi guys,

thanx for all the responses much appreciated, I still have a problem all methods posted above seem to work..but I think the problem lies in that the table that I'm querying has like 40+ million records

so I thik the any group by(distinct query) will take 7000 years to complete ehhe

I guess i'm gonna have to request the IT dept to make a few more indexes on that table so i can query it using the jimbopalmer suggestion. (because any group by on that many recs will still take forever.

Let me know if I will be taking the right approach in going this way.

Thanks all,
THEGREEK

 

[jimbopalmer]

For DIma's method, a cocatenated index on AccntNum,OrderNum should speed it up, for my method a concatenated index on OrderNum and Param may help.

Questions that I have that would make my advice more informed are
:
Are Ordernums unique, or only unique for a given Accountnum

Will every OrderNum always have a Cable record and a Pair record, or are either of those optional?

The desired result shows AccountNums and OrderNums in accending order, so I ordered that way, did you want all orders by customer A together or ordernums in order?

I tried to remain child-like, all I acheived was childish.

 

[sem]

40+ million records? 7000 years ? Why do you think about Oracle so bad? Do you run it on i286 ?

Regards, Dima

 

[THEGREEK]

hi jimbopalmer,

ordernums are unique only for each accntnum
cable and/or pair are optional, but I must show them if they are.

Order by is not necessary, as i will use ms access to further manipulate the file.

Thanks,
THEGREEK

 

[jimbopalmer]

Dima's method will definitely be better than mine then, as I could only make one of Cable or Pair optional and I need a where clause to make p.accountnum = c.accountnum that I did not add.

I tried to remain child-like, all I acheived was childish.