Home » SQL & PL/SQL » SQL & PL/SQL » Help me with the PL/SQL code to update the table in 11g (11.2.0.1.0 - 64bit Production, Windows 7)
Help me with the PL/SQL code to update the table in 11g [message #590315] Wed, 17 July 2013 01:57 Go to next message
manubatham20
Messages: 432
Registered: September 2010
Location: Champaign, IL
Senior Member

I have a table extended_values_Test, with the following structure:

CREATE TABLE extended_values_Test
(
MATRL_NUM VARCHAR2(18 BYTE),
PRI_SHIP_LOC CHAR(4 BYTE) NOT NULL,
PLANT CHAR(4 BYTE) NOT NULL,
STD_COST NUMBER,
SNAPSHOT_DATE CHAR(8 BYTE)
)

Note: Snapshot_date is last working date of that month, and is recorded only once in the table for a matrl_num & plant combination.

Inserting records in the table:
Refer to insert_extended_values.sql
Note: This is just a sample table , having records for only two matrl_num.

after inserting records , you can see the records are for two different matrl_num's.

For each matrl_num , we can have multiple values of plant and for each plant we can have different value of std_Cost(eg:matrl_num='0023173556').

Moreover for each matrl_num , I have some records in each month , based on the number of plants(with std_cost). Std_cost for a plant is updated in the table whenever there is a change in it from the previous value.In case of no change in value of std_cost over previous month's value , we have 'Null' std_cost for that matrl_num, plant & month(next month).

I want to update these 'Null' values with latest(last month's 'not null' - std_cost) std_cost for that matrl_num and plant.

Note: Such change in std_cost can occur multiple times , so we need to update the 'Null' std_cost till the time we have encountered a change in std_cost and so on.
Re: Help me with the PL/SQL code to update the table in 11g [message #590317 is a reply to message #590315] Wed, 17 July 2013 02:29 Go to previous messageGo to next message
dariyoosh
Messages: 511
Registered: March 2009
Location: Iran / France
Senior Member
Hi,

Quote:

. . . I want to update these 'Null' values with latest(last month's 'not null' - std_cost) std_cost for that matrl_num and plant . . .

So should we consider that (matrl_num, plant) is the primary key (as you didn't specify any constraint for your table)?

Also if SNAPSHOT_DATE is really a date value why do you use a CHAR(8 BYTE) type instead of using the DATE type which is in fact the dedicated type for date values in oracle?

Moreover, the latest (last month's 'NOT NULL') std_cost is not a correct criterion as there can be multiple std_cost values for the same date (given the fact that there is no constraint/control on your table)

So I think, first you need to Normalize your table properly before one can suggest a solution.

Regards,
Dariyoosh

[Updated on: Wed, 17 July 2013 02:48]

Report message to a moderator

Re: Help me with the PL/SQL code to update the table in 11g [message #590318 is a reply to message #590317] Wed, 17 July 2013 02:48 Go to previous messageGo to next message
manubatham20
Messages: 432
Registered: September 2010
Location: Champaign, IL
Senior Member

Yes, It would automatically come out to be PK combination, But I am not sure if it would of much help. However, in my original table, no PK is defined on these two columns.

We need this SNAPSHOT_DATE column into Char field only.

Please let me know in case you need any further information.

Regards,
Manu

[Updated on: Wed, 17 July 2013 02:48]

Report message to a moderator

Re: Help me with the PL/SQL code to update the table in 11g [message #590320 is a reply to message #590318] Wed, 17 July 2013 02:54 Go to previous messageGo to next message
dariyoosh
Messages: 511
Registered: March 2009
Location: Iran / France
Senior Member
manubatham20 wrote on Wed, 17 July 2013 09:48
We need this SNAPSHOT_DATE column into Char field only.
Why?

manubatham20 wrote on Wed, 17 July 2013 09:48

Please let me know in case you need any further information.

As I said in my previous comment, the latest (last month's 'NOT NULL') std_cost is not a correct criterion and will not work as there can be multiple std_cost values for the same date (given the fact that there is no constraint/control on your table)

Regards,
Dariyoosh
Re: Help me with the PL/SQL code to update the table in 11g [message #590321 is a reply to message #590320] Wed, 17 July 2013 03:01 Go to previous messageGo to next message
manubatham20
Messages: 432
Registered: September 2010
Location: Champaign, IL
Senior Member

For a matrl_num, we can have only one std_cost in one month (latest one) for a plant. So i guess , We need to take each and every matrl_num one by one and then select those records which have 'not null' std_cost with max snapshot_Date. Then use the values of (std_cost & plant) with this snapshot_Date to update the 'NULL' std_cost corresponding to each plant in the coming months, till the time we have next 'not null' std_cost for that matrl_num.

Let me know in case its still not clear.

Regards,
Salil
Re: Help me with the PL/SQL code to update the table in 11g [message #590322 is a reply to message #590321] Wed, 17 July 2013 03:09 Go to previous messageGo to next message
cookiemonster
Messages: 10572
Registered: September 2008
Location: Rainy Manchester
Senior Member
It'd help if your insert statements worked.
You can't insert 'null' into a number column. It should be null (the keyword).
And next time post them inline in code tags - you know how by now.
Re: Help me with the PL/SQL code to update the table in 11g [message #590323 is a reply to message #590321] Wed, 17 July 2013 03:13 Go to previous messageGo to next message
dariyoosh
Messages: 511
Registered: March 2009
Location: Iran / France
Senior Member
I tried your test case, almost half the insert statements were rejected because instead of NULL you write 'NULL' for showing NULL values which doesn't make sense. If a column may hold NULL values, just simply write NULL otherwise you will be in trouble when you apply conditions such as IS [NOT] NULL in your queries.

I didn't test the following (as I didn't have a working test case) but might be close to what you're looking for

UPDATE extended_values_Test t1
SET t1.std_cost = 
(
    SELECT  t2.std_cost
    FROM    extended_values_Test t2
    WHERE   t1.matrl_num = t2.matrl_num   AND
            t1.plant = t2.plant           AND
            t2.std_cost IS NOT NULL       AND
            to_date(t2.snapshot_date, 'YYYYMMDD') = 
                (
                    SELECT  max(to_date(t3.snapshot_date, 
                                        'YYYYMMDD')
                               ) snapshot_date
                    FROM    extended_values_Test t3
                    WHERE   t1.matrl_num = t3.matrl_num     AND
                            t1.plant = t3.plant             AND
                            t3.snapshot_date IS NOT NULL    AND
                            to_date(t3.snapshot_date, 'YYYYMMDD') <
                            to_date(t1.snapshot_date, 'YYYYMMDD')
                )
)
WHERE t1.std_cost IS NULL;


Regards,
Dariyoosh

[Updated on: Wed, 17 July 2013 03:13]

Report message to a moderator

Re: Help me with the PL/SQL code to update the table in 11g [message #590325 is a reply to message #590323] Wed, 17 July 2013 04:00 Go to previous messageGo to next message
salilgupta27
Messages: 4
Registered: July 2013
Location: Noida
Junior Member
Hi Dariyoosh,

The code provided by you works only for the next month and not for all the next months which have null values.

So one needs to run the above as many times as number of months with null std_cost value.

Regards,
Salil
Re: Help me with the PL/SQL code to update the table in 11g [message #590326 is a reply to message #590325] Wed, 17 July 2013 04:05 Go to previous messageGo to next message
salilgupta27
Messages: 4
Registered: July 2013
Location: Noida
Junior Member
You can use the below insert script to populate the table:

insert into extended_values_Test values('0064441008','0IND','0ATL','1.43','20130125');
insert into extended_values_Test values('0064441008','0IND','0DAL','1.43','20130125');
insert into extended_values_Test values('0064441008','0IND','0DEL','1.43','20130125');
insert into extended_values_Test values('0064441008','0IND','0IND','1.43','20130125');
insert into extended_values_Test values('0064441008','0IND','0LEB','1.43','20130125');
insert into extended_values_Test values('0064441008','0IND','0PDC','1.43','20130125');
insert into extended_values_Test values('0064441008','0IND','0SCI','1.43','20130125');
insert into extended_values_Test values('0023173556','0IND','0ATL','47.8','20130125');
insert into extended_values_Test values('0023173556','0IND','0CAM','116.33','20130125');
insert into extended_values_Test values('0023173556','0IND','0DAL','47.8','20130125');
insert into extended_values_Test values('0023173556','0IND','0IND','47.8','20130125');
insert into extended_values_Test values('0023173556','0IND','0ATL','47.8','20130222');
insert into extended_values_Test values('0023173556','0IND','0CAM','116.33','20130222');
insert into extended_values_Test values('0023173556','0IND','0DAL','47.8','20130222');
insert into extended_values_Test values('0023173556','0IND','0IND','47.8','20130222');
insert into extended_values_Test values('0064441008','0IND','0ATL','1.43','20130222');
insert into extended_values_Test values('0064441008','0IND','0DAL','1.43','20130222');
insert into extended_values_Test values('0064441008','0IND','0DEL','1.43','20130222');
insert into extended_values_Test values('0064441008','0IND','0IND','1.43','20130222');
insert into extended_values_Test values('0064441008','0IND','0LEB','1.43','20130222');
insert into extended_values_Test values('0064441008','0IND','0PDC','1.43','20130222');
insert into extended_values_Test values('0064441008','0IND','0SCI','1.43','20130222');
insert into extended_values_Test values('0023173556','0IND','0ATL','47.8','20130329');
insert into extended_values_Test values('0023173556','0IND','0CAM','116.33','20130329');
insert into extended_values_Test values('0023173556','0IND','0DAL','47.8','20130329');
insert into extended_values_Test values('0023173556','0IND','0IND','47.8','20130329');
insert into extended_values_Test values('0064441008','0IND','0ATL','1.43','20130329');
insert into extended_values_Test values('0064441008','0IND','0DAL','1.43','20130329');
insert into extended_values_Test values('0064441008','0IND','0DEL','1.43','20130329');
insert into extended_values_Test values('0064441008','0IND','0IND','1.43','20130329');
insert into extended_values_Test values('0064441008','0IND','0LEB','1.43','20130329');
insert into extended_values_Test values('0064441008','0IND','0PDC','1.43','20130329');
insert into extended_values_Test values('0064441008','0IND','0SCI','1.43','20130329');
insert into extended_values_Test values('0064441008','0IND','0ATL',NULL,'20130426');
insert into extended_values_Test values('0064441008','0IND','0DAL',NULL,'20130426');
insert into extended_values_Test values('0064441008','0IND','0DEL',NULL,'20130426');
insert into extended_values_Test values('0064441008','0IND','0IND',NULL,'20130426');
insert into extended_values_Test values('0064441008','0IND','0LEB',NULL,'20130426');
insert into extended_values_Test values('0064441008','0IND','0PDC',NULL,'20130426');
insert into extended_values_Test values('0064441008','0IND','0SCI',NULL,'20130426');
insert into extended_values_Test values('0023173556','0IND','0ATL',NULL,'20130426');
insert into extended_values_Test values('0023173556','0IND','0CAM',NULL,'20130426');
insert into extended_values_Test values('0023173556','0IND','0DAL',NULL,'20130426');
insert into extended_values_Test values('0023173556','0IND','0IND',NULL,'20130426');
insert into extended_values_Test values('0023173556','0IND','0ATL',NULL,'20130531');
insert into extended_values_Test values('0023173556','0IND','0CAM',NULL,'20130531');
insert into extended_values_Test values('0023173556','0IND','0DAL',NULL,'20130531');
insert into extended_values_Test values('0023173556','0IND','0IND',NULL,'20130531');
insert into extended_values_Test values('0064441008','0IND','0ATL',NULL,'20130531');
insert into extended_values_Test values('0064441008','0IND','0DAL',NULL,'20130531');
insert into extended_values_Test values('0064441008','0IND','0DEL',NULL,'20130531');
insert into extended_values_Test values('0064441008','0IND','0IND',NULL,'20130531');
insert into extended_values_Test values('0064441008','0IND','0LEB',NULL,'20130531');
insert into extended_values_Test values('0064441008','0IND','0PDC',NULL,'20130531');
insert into extended_values_Test values('0064441008','0IND','0SCI',NULL,'20130531');
insert into extended_values_Test values('0064441008','0IND','0ATL',NULL,'20130628');
insert into extended_values_Test values('0064441008','0IND','0DAL',NULL,'20130628');
insert into extended_values_Test values('0064441008','0IND','0DEL',NULL,'20130628');
insert into extended_values_Test values('0064441008','0IND','0IND',NULL,'20130628');
insert into extended_values_Test values('0064441008','0IND','0LEB',NULL,'20130628');
insert into extended_values_Test values('0064441008','0IND','0PDC',NULL,'20130628');
insert into extended_values_Test values('0064441008','0IND','0SCI',NULL,'20130628');
insert into extended_values_Test values('0023173556','0IND','0ATL',NULL,'20130628');
insert into extended_values_Test values('0023173556','0IND','0CAM',NULL,'20130628');
insert into extended_values_Test values('0023173556','0IND','0DAL',NULL,'20130628');
insert into extended_values_Test values('0023173556','0IND','0IND',NULL,'20130628');

Thanks,
Salil
Re: Help me with the PL/SQL code to update the table in 11g [message #590327 is a reply to message #590325] Wed, 17 July 2013 04:10 Go to previous messageGo to next message
dariyoosh
Messages: 511
Registered: March 2009
Location: Iran / France
Senior Member
salilgupta27 wrote on Wed, 17 July 2013 11:00
The code provided by you works only for the next month and not for all the next months which have null values


The code I provided, does an update for each row having std_cost NULL by the most recent non NULL std_cost value for the same primary key. It seems to me that this is the criterion you specified in OP.
Quote:

. . . I want to update these 'Null' values with latest(last month's 'not null' - std_cost) std_cost for that matrl_num and plant . . .

And latest(last month's 'not null' - std_cost) is what I specified in the query:

. . . 
SELECT  max(to_date(t3.snapshot_date, 'YYYYMMDD')) snapshot_date 
. . .
WHERE   
. . . 
to_date(t3.snapshot_date, 'YYYYMMDD') < to_date(t1.snapshot_date, 'YYYYMMDD')
. . .


Regards,
Dariyoosh
Re: Help me with the PL/SQL code to update the table in 11g [message #590329 is a reply to message #590327] Wed, 17 July 2013 04:35 Go to previous messageGo to next message
salilgupta27
Messages: 4
Registered: July 2013
Location: Noida
Junior Member
Yes you are right that your query is taking the latest std_cost to update the null std_cost. But this query is updating only for next month. But we want to recursively update the null std_cost in all the months ahead.

Hope I clarified your doubt.

Thanks,
Salil
Re: Help me with the PL/SQL code to update the table in 11g [message #590331 is a reply to message #590329] Wed, 17 July 2013 04:46 Go to previous messageGo to next message
dariyoosh
Messages: 511
Registered: March 2009
Location: Iran / France
Senior Member
salilgupta27 wrote on Wed, 17 July 2013 11:35
. . . But this query is updating only for next month.
When you read in my query

. . .
to_date(t3.snapshot_date, 'YYYYMMDD') < to_date(t1.snapshot_date, 'YYYYMMDD')
. . .


You still tell me that my query update the next month ??


salilgupta27 wrote on Wed, 17 July 2013 11:35

But we want to recursively update the null std_cost in all the months ahead.

Hope I clarified your doubt.

I don't really understand what do you mean? recursively update the null std_cost in all the months ahead ? How do you want to update values that are not already in your table and may be one day be inserted in the future?

Regards,
Dariyoosh
Re: Help me with the PL/SQL code to update the table in 11g [message #590332 is a reply to message #590331] Wed, 17 July 2013 05:02 Go to previous messageGo to next message
salilgupta27
Messages: 4
Registered: July 2013
Location: Noida
Junior Member
Lets try to understand my case by an example:

Suppose for a matrl_num , I have values in std_cost for the month of march,2013. So as today, I will have std_cost null for three months i.e April,May and June. Now I want to update my all three months null std_cost with the march std_cost.

I saw the condition at the first go only, but it seems that its working only for the next month.

P.S: May be one more subquery etc is required to handle this or may be need to write a program .

Thanks a lot for your patience and help.

Thanks,
Salil
Re: Help me with the PL/SQL code to update the table in 11g [message #590335 is a reply to message #590332] Wed, 17 July 2013 05:30 Go to previous messageGo to next message
dariyoosh
Messages: 511
Registered: March 2009
Location: Iran / France
Senior Member
Ok, I think I understand what do you mean, there was in fact a condition missing in my query. Consider the following test case:

SET SQLBLANKLINES ON;

DROP TABLE extended_values_Test;

CREATE TABLE extended_values_Test
(
    MATRL_NUM VARCHAR2(18 BYTE),
    PRI_SHIP_LOC CHAR(4 BYTE) NOT NULL,
    PLANT CHAR(4 BYTE) NOT NULL,
    STD_COST NUMBER,
    SNAPSHOT_DATE CHAR(8 BYTE)
);


INSERT ALL
    INTO extended_values_Test VALUES
        ('0023173556','0IND','0IND', 1200,'20130306')   -- March
    INTO extended_values_Test VALUES
        ('0023173556','0IND','0IND', NULL, '20130401')  -- April
    INTO extended_values_Test VALUES
        ('0023173556','0IND','0IND', NULL, '20130507')  -- May
    INTO extended_values_Test VALUES
        ('0023173556','0IND','0IND', NULL, '20130612')  -- June
    INTO extended_values_Test VALUES
        ('0023173556','0IND','0IND', NULL, '20130718')  -- July
        
    INTO extended_values_Test VALUES
        ('0023173557','0IND','0IND', 2400,'20130304')   -- April
    INTO extended_values_Test VALUES
        ('0023173557','0IND','0IND', NULL,'20130617')   -- June
    INTO extended_values_Test VALUES
        ('0023173557','0IND','0IND', NULL,'20130713')   -- July
SELECT * FROM dual;


SELECT  *
FROM    extended_values_Test;


MATRL_NUM	   PRI_ PLAN   STD_COST SNAPSHOT
------------------ ---- ---- ---------- --------
0023173556	   0IND 0IND	   1200 20130306
0023173556	   0IND 0IND		20130401
0023173556	   0IND 0IND		20130507
0023173556	   0IND 0IND		20130612
0023173556	   0IND 0IND		20130718
0023173557	   0IND 0IND	   2400 20130304
0023173557	   0IND 0IND		20130617
0023173557	   0IND 0IND		20130713

8 rows selected.


So now we update

UPDATE extended_values_Test t1
SET t1.std_cost = 
(
    SELECT  t2.std_cost
    FROM    extended_values_Test t2
    WHERE   t1.matrl_num = t2.matrl_num   AND
            t1.plant = t2.plant           AND
            t2.std_cost IS NOT NULL       AND
            to_date(t2.snapshot_date, 'YYYYMMDD') = 
                (
                    SELECT  max(to_date(t3.snapshot_date, 
                                        'YYYYMMDD')
                               ) snapshot_date
                    FROM    extended_values_Test t3
                    WHERE   t1.matrl_num = t3.matrl_num                 AND
                            t1.plant = t3.plant                         AND
                            t3.snapshot_date IS NOT NULL                AND
                            to_date(t3.snapshot_date, 'YYYYMMDD') <
                                to_date(t1.snapshot_date, 'YYYYMMDD')   AND
                            t3.std_cost IS NOT NULL 
                )
)
WHERE t1.std_cost IS NULL;


SELECT  *
FROM    extended_values_Test;


6 rows updated.


MATRL_NUM	   PRI_ PLAN   STD_COST SNAPSHOT
------------------ ---- ---- ---------- --------
0023173556	   0IND 0IND	   1200 20130306
0023173556	   0IND 0IND	   1200 20130401
0023173556	   0IND 0IND	   1200 20130507
0023173556	   0IND 0IND	   1200 20130612
0023173556	   0IND 0IND	   1200 20130718
0023173557	   0IND 0IND	   2400 20130304
0023173557	   0IND 0IND	   2400 20130617
0023173557	   0IND 0IND	   2400 20130713

8 rows selected.

SQL> 


But again, even this solution will not work until your table has been normalized by defining properly the primary key, which is currently not the case, as I said earlier for the same date you can have multiple std_cost as there is no control/constraint on your table. If this happens, obviously the subquery in the UPDATE will fail by giving you the error message: ORA-01427: single-row subquery returns more than one row

So, once again, please read Normalization.


Regards,
Dariyoosh

[Updated on: Wed, 17 July 2013 05:34]

Report message to a moderator

Re: Help me with the PL/SQL code to update the table in 11g [message #590337 is a reply to message #590332] Wed, 17 July 2013 05:37 Go to previous message
dariyoosh
Messages: 511
Registered: March 2009
Location: Iran / France
Senior Member
Also a good link to use for your code:

http://www.dpriver.com/pp/sqlformat.htm

Regards,
Dariyoosh
Previous Topic: XML not saving in a particular directory
Next Topic: Build Parametric queries in SQL
Goto Forum:
  


Current Time: Thu Apr 17 09:30:46 CDT 2014

Total time taken to generate the page: 0.10406 seconds