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
 

 

Query regarding modifying a SQL

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

New User


Joined: 14 Apr 2008
Posts: 73
Location: Mysore

PostPosted: Tue Apr 28, 2015 11:35 am    Post subject: Query regarding modifying a SQL
Reply with quote

Hi All,

Greetings.!!

Currently I have a query which extracts data from the DB2 table and sends to GUI. I need to do arithmetic operation in one of the query for the change of requirement. below is the snippet of the query
Code:

SELECT  1
     ,  C_LBL
     ,  Q_CV_RSTR_OPT_1
     ,  Q_CV_RSTR_OPT_2
     ,  Q_CV_RSTR_OPT_3
  FROM  Table1
 WHERE  condition 1
   ...
   and  condition n
   AND C_LBL NOT LIKE :LT-UA

UNION

SELECT  1
     ,  'OTHERS'
     ,SUM(Q_CV_RSTR_OPT_1)
     ,SUM(Q_CV_RSTR_OPT_2)
     ,SUM(Q_CV_RSTR_OPT_3)
  FROM  Table1
 WHERE condition 1
   ...
   and condition n
   AND C_LBL  LIKE :LT-UA


Table data looks something looks below
Code:

     C_LBL             QTY_1                  QTY_2         QTY_3   
---------------- -------------------- --------------------        --------------------
********************************* TOP ****************************   ****************************
BRRW                            0.000                    0.000                  0.000                 
CLNT                            0.000                    0.000         0.000
CRED                            0.000                    0.000         0.000
ISSU                            0.000                    0.000         0.000
SPLT                            0.000                    0.000         0.000
ABC                             0.000                    0.000         0.000
UA-XX                            0.000                    0.000         0.000
UA-YY                            0.000                    0.000         0.000
UA-ZZ                            0.000                    0.000         0.000
UA-AA                           0.000                    0.000         0.000
UA-BB                             0.000                    0.000         0.000
SPLITADJ                             0.000                    0.000         0.000





Now I will get one more record which is SPLITADJ in the C_LBL column. I need to deduct this value from all other UA-% and send to GUI.
Earlier I will just sum all the UA values and send to GUI, but now I need to deduct the SPLITADJ from the UA- records sum and send the results to GUI. Other Label records(other than UA) should be summed as usual

Appreciate your ideas on how to do this ?
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Tue Apr 28, 2015 6:48 pm    Post subject: Reply to: Query regarding modifying a SQL
Reply with quote

I think instead of:
Code:
SELECT  1
     ,  'OTHERS'
     ,SUM(Q_CV_RSTR_OPT_1)
     ,SUM(Q_CV_RSTR_OPT_2)
     ,SUM(Q_CV_RSTR_OPT_3)

You may need to write:
Code:
SELECT  1
     ,  'OTHERS'
     ,(Select SUM(Q_CV_RSTR_OPT_1) from Table1 where C_LBL like UA-% - Select QTY1 from Table1 where C_LBL = 'SPLITADJ')
     ,(Select SUM(Q_CV_RSTR_OPT_2) from Table1 where C_LBL like UA-% - Select QTY2 from Table1 where C_LBL = 'SPLITADJ')
     ,(Select SUM(Q_CV_RSTR_OPT_3) from Table1 where C_LBL like UA-% - Select QTY3 from Table1 where C_LBL = 'SPLITADJ')


This may not be the best possible solution but this is what I can think of
.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Apr 29, 2015 10:48 am    Post subject:
Reply with quote

Hi,

Try below query, this is untested

Code:
SELECT  1
     ,  'OTHERS'
     ,SUM(CASE WHEN C_LBL = 'SPLITADJ' THEN (-1 *  Q_CV_RSTR_OPT_1) ELSE Q_CV_RSTR_OPT_1 END)
     ,SUM(CASE WHEN C_LBL = 'SPLITADJ' THEN (-1 *  Q_CV_RSTR_OPT_2) ELSE Q_CV_RSTR_OPT_2 END)
     ,SUM(CASE WHEN C_LBL = 'SPLITADJ' THEN (-1 *  Q_CV_RSTR_OPT_3) ELSE Q_CV_RSTR_OPT_3 END)
  FROM  Table1
 WHERE condition 1
   ...
   and condition n
   AND ( C_LBL  LIKE :LT-UA OR C_LBL = 'SPLITADJ')



Let us know if anything missing here

Regards,
Chandan
Back to top
View user's profile Send private message
rexx77

New User


Joined: 14 Apr 2008
Posts: 73
Location: Mysore

PostPosted: Wed Apr 29, 2015 12:04 pm    Post subject:
Reply with quote

Thanks Chandan and Rahul for your ideas.. I am able to achieve the desired results. Much appreciated.
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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