I'm trying to write a program (?procedure etc) to do the following:
1. Extract date from two columns (column1 number(6), column2 varchar2(10))
in a table TABLE01.
2. Column 1 is the serial_no of goods produced in the dates contained in
column2 (althogh this column is varchar2). If 4 goods are produced on the
same day then you have the same serial_no repeated 4 times and the date
also repeated 4 times. If the goods are produced for 5 days then the
serial_no continues for 5 days as well and so on.
3.Populate TABLE02 (column1 number(6), column2 date, column3 date,
Column1 in table02 with contain the same data(eg. serial_no) as in column1
Column2 and column3 in table02 will contain the data from column2 table01
but converted to DATE datatype.
Column4 will contain the the difference between the Serial_start_date and
Column5 will contain the NUMBER of goods within each serial_no manufactured
on the serial_start_date
Column6 will contain the NUMBER of goods within each serial_no manufactured
on the next day and so on until the given serial_no runs out which could be 1
day, 2 days or any number of days.
Column10 will contain other goods manufactured after column9.
I've been trying the work out how to write this (?procedure) for some days
now but I'm now desperately and urgently asking for assistance.
so, you know what data you need to manipulate: TABLE1
you know what table you want to UPDATE: TABLE2
I always like to see if I can ONLY create a select statement that can get me ALL of the data I need. If I can do that, then it is a good starting point for me.
sounds like you need a procedure and function in order to manipulate and pass all this data. Your explaination of the tables and fields is a bit confusing so it is hard to give you more detail that this.
remember: start small. if you accomplish 3 small items of your procedure, then you are probably a good way to the answer.
Also - think about creating a temporary table with the fields you will need to insert into TABLE2.