PL/SQL help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: PL/SQL help

  1. #1
    Join Date
    Nov 2000
    Posts
    178
    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,
    columns4.....10 number)

    Column1 in table02 with contain the same data(eg. serial_no) as in column1
    table01.
    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
    serial_end_date.
    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.

    AC

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking step by step

    think of the problem in small steps.

    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.

    - magnus



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width