Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 
DB2 Query to add matching class column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
senthamizh

New User


Joined: 27 Apr 2009
Posts: 22
Location: mumbai

PostPosted: Fri Jan 15, 2016 8:32 pm    Post subject: DB2 Query to add matching class column
Reply with quote

Hi,

I have the below Table
Code:

TABLE1,
REG     CODE       Class         Amount
1      abc            A          10
1      abc            B          2
1      abc            D          2
2      abc            A          2
1         XYZ                L             10


The requirement is to add matching class column Amount and update for REG 2 for Code = abc.

after update the table TABLE1 should look as below,

TABLE1
Code:
REG   CODE   Class      Amount
1   abc               A      10
1   abc              B       2
1   abc              D      2
2   abc              A     12
1      XYZ              L      10


Can some one help me on this?

Thanks,
Sen

Code'd, for what it was worth
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1848
Location: UK

PostPosted: Sat Jan 16, 2016 3:55 pm    Post subject:
Reply with quote

Use SQL to get the data from your table, use your program to play with your data, use SQL to update your table.

If you have large amounts of data and values to change you may have to unload the table and use the unloaded data then reload it.
Back to top
View user's profile Send private message
senthamizh

New User


Joined: 27 Apr 2009
Posts: 22
Location: mumbai

PostPosted: Sat Jan 16, 2016 4:37 pm    Post subject: Reply to: DB2 Query to add matching class column
Reply with quote

Is this possible in Spufi? with out using program.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1848
Location: UK

PostPosted: Sat Jan 16, 2016 8:00 pm    Post subject:
Reply with quote

Is this a production table? How many rows have to be changed?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 386
Location: USA

PostPosted: Sun Jan 17, 2016 4:28 am    Post subject: Reply to: DB2 Query to add matching class column
Reply with quote

Will it always be the REG '2' that needs to be updated i.e. the second occurrence of code and class combination? I assume there will Not be any REG 3, 4, 5 etc.

Will you have more code and class combination that needs update under a single REG Or multiple combinations need to be updated?

Please provide bigger sample of data.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1746
Location: NY,USA

PostPosted: Fri Jan 29, 2016 2:44 am    Post subject:
Reply with quote

Code:
    update table  c

      set c.Amount =
       (select tbl1.aa 
       from table  b,
       (select b.Class   bb , sum(b.Amount )   aa 
           from table   b  group by  b.Class   ) as tbl1     
       where b.REG= 2  and
       tbl1.bb = b.Class )
       
       where c.REG= 2
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 5 Sat Aug 19, 2017 1:51 am
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us