Query to generate a table with a new column form a given table 1

[ianoctdec]

I have a table Orders (order_id, line_no, part_no).
Ex:
OrderID Line_no Part_no
Order1 1 abc
Order1 2 abc
Order1 3
Order1 4
Order1 5 abc
Order1 6 abc

I need to eliminate lines 3 and 4 (null Part_no), and recreate the table as:
OrderID Line_no New_Line_no Part_no
Order1 1 1 abc
Order1 2 2 abc
Order1 5 3 abc
Order1 6 4 abc

How do I write a query to do this?

Thank you for any help,
Daniella

 

[SantaMufasa]

Daniella,

We are happy to help you refine the work you have done to this point to resolve your need. Please post the code you have tried thus far.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Turkbear]

Hi Daniella,
As Santa stated, seeing what you have tried will give a better understanding of what you want to accomplish..
For Instance, how is the new_line_no derived from the old_line_no?


Let us help...







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ianoctdec]

Dave and Turkbear, thank you for your replies.

Let me clarify: it doesn't have to be any relation between line_no and new_line_no. What I need to do is practicaly to renumber the lines starting with 1 for each order, after eliminating lines with empty part from orders table. I don't need the line_no anymore, just thought it would be nice to keep it.
I tried the query below (following Dave's solution from a similar thread):

select A.CUST_ORDER_ID,row_number() over (partition by A.CUST_ORDER_ID order by A.LINE_NO) NEW_LINE_NO
from
(SELECT COL.CUST_ORDER_ID, COL.LINE_NO FROM CUST_ORDER_LINE COL, CUSTOMER_ORDER CO WHERE CO.ORDER_TYPE = 'abcd' AND COL.PART_ID IS NOT NULL AND CO.ID = COL.CUST_ORDER_ID) A

I write the query as a SQL command in Crystal Reports 10, and I have Oracle 8i.

I got an error stating: Query Engine error: 'ORA-00049: feature not enabled OLAP WINDOW functions'.

Please advise.

Thank you,
Daniella

 

[karluk]

You must have typed the error number incorrectly. It looks as if your error is ORA-00439, not ORA-00049. According to Metalink note 185228.1 you can get this error if you are running in Oracle server standard edition and try to execute a query that uses analytic functions. Analytic functions were only enabled in Oracle 8.1.7 if you were running Oracle server enterprise edition.

 

[taupirho]

This might give you some pointers

SQL> select * from tom;

ORDERID LINE_NO PAR
---------- ---------- ---
Order1 1 abc
Order1 2 abc
Order1 3
Order1 4
Order1 5 abc
Order1 6 abc
Order2 1 abc
Order2 2 xyz
Order2 3

9 rows selected.


SELECT
a.orderid,
DECODE(ROWNUM-min_sno,0,1,rownum+1-min_sno) sno,
a.part_no
FROM
(
SELECT *
FROM tom
where part_no is not null
ORDER BY orderid,part_no
) a,
(
SELECT
orderid,
MIN(rownum) min_sno
FROM
(
SELECT *
FROM tom
where part_no is not null
ORDER BY orderid,part_no
)
GROUP BY orderid
) b
WHERE a.orderid=b.orderid

SQL> /

ORDERID SNO PAR
---------- ---------- ---
Order1 1 abc
Order1 2 abc
Order1 3 abc
Order1 4 abc
Order2 1 abc
Order2 2 xyz

6 rows selected.






In order to understand recursion, you must first understand recursion.

 

[ChrisHunt]

Maybe I'm missing something, but the solutions posted so far seem really convoluted. How about this? :

SELECT orderid,
       line_no,
       ROWNUM   new_line_no,
       part_no
FROM   orders
WHERE  part_no IS NOT NULL

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[taupirho]

Chris I suspect the the OP's real table has slightly more orderid's than is shown in the sample. Whilst your solution will work if there is only one distinct orderid it will fail if not.




In order to understand recursion, you must first understand recursion.

 

[ianoctdec]

Thank you all for your replies.

Karluk, you're right, I typed the error incorectly, it was ORA 00439, indeed. And, yes we have Oracle standard edition, so I cannot use analytic functions.
I will try taupirho code(thank you for taking the time to post it).
ChrisHunt, I had already tried your solution, it is not working. There are a lot of diferent orders in Orders table.

Thank you, guys, you are great!
Daniella

 

[ianoctdec]

taupirho,

Your code didn't give me just some pointers, but the whole solution. It works beautifuly! Thank you again.

Indeed, in order to understand recursion, you must first understand recursion!


Daniella

 

[SantaMufasa]

Taupirho...It works beautifuly! Thank you again.

I'll bet you intended to click "[Thank taupirho for this valuable post!]". Contributors always appreciate

s for contributions that assist the original poster.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ianoctdec]

I have certantly intended that. I was so impressed of the quick response and the beauty of its solution, I forgot that is a way to properly thank him for it. He deserves all the stars!

Daniella