|
View previous topic :: View next topic
|
| Author |
Message |
rexx77
New User
Joined: 14 Apr 2008
Posts: 78
Location: Mysore
|
|
|
|
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 |
|
 |
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
rexx77
New User
Joined: 14 Apr 2008
Posts: 78
Location: Mysore
|
|
|
|
| Thanks Chandan and Rahul for your ideas.. I am able to achieve the desired results. Much appreciated. |
|
| Back to top |
|
|
|
|
