View previous topic :: View next topic
|
Author |
Message |
ajay_udl
New User
Joined: 10 Oct 2006 Posts: 29
|
|
|
|
Hi All,
I'm running some Queries via QMF.
SELECT
CUST_ID,
SUM(CASE TRAN_TYPE_CD WHEN 'D' THEN TRAN_AMT END) AS D_AMT,
SUM(CASE TRAN_TYPE_CD WHEN 'C' THEN TRAN_AMT END) AS C_AMT
FROM TP1.QUERY11_OUT
WHERE CUST_ID IN( SELECT CUST_ID FROM TP1.QUERY15_OUT)
GROUP BY CUST_ID
In the above query I'm getting the amount for D_AMT and C_AMT.
I wanted to find the net of both in a new column
ie; D_AMT - C_AMT = NET.
Is it possible to achieve in the same query or do I need to save this Query and then write another one to achieve this?
How do i achieve this if I'm writing a new one?
thanks in advance!!
Regards
Ajay. |
|
Back to top |
|
|
Dsingh29
Active User
Joined: 16 Dec 2008 Posts: 132 Location: IBM
|
|
|
|
Assuming that your query was
SELECT KEYFIELD, VALUE1, VALUE2 FROM TABLENAME
and you wanted a 4th column containing Value1 less Value2, you could try
SELECT KEYFIELD, VALUE1, VALUE2, VALUE1-VALUE2 AS DIFFERENCE FROM TABLENAME.
If your question meant something else, please clarify. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Code: |
SELECT D_AMT, C_AMT, D_AMT - C_AMT AS NET FROM
(
SELECT
CUST_ID,
SUM(CASE TRAN_TYPE_CD WHEN 'D' THEN TRAN_AMT END) AS D_AMT,
SUM(CASE TRAN_TYPE_CD WHEN 'C' THEN TRAN_AMT END) AS C_AMT
FROM TP1.QUERY11_OUT
WHERE CUST_ID IN( SELECT CUST_ID FROM TP1.QUERY15_OUT)
GROUP BY CUST_ID
) AS A ;
|
|
|
Back to top |
|
|
|