MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Help with Query

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Apr 29, 2008 11:07 am    Post subject: Help with Query Reply with quote
How to substract data from Query based on condition?

The query igees like
Select master_id,payment_amt, (payment_amt - temp_fee)
from master_table

Now, temp_fee record may or may not be present in the table.

If it exist, then i need to subtract or else dont substract.

Can anyone help me with this situation?

Thanks,
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Apr 29, 2008 11:19 am    Post subject: Reply with quote
rajen wrote:
Now, temp_fee record may or may not be present in the table.


You mean it is has null value. You can use the COALESCE or VALUE scalar function to make it a zero when the column is null.

try this

Code:


Select master_id
      ,payment_amt
      ,payment_amt - COALESCE(temp_fee,0)
from master_table
;


or
Code:

Select master_id
      ,payment_amt
      ,payment_amt - VALUE(temp_fee,0)
from master_table
;


The scalar functions COALESCE and VALUE are explained here

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQJ12/3.2.15?DT=20050325102208

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQJ12/3.2.117?DT=20050325102208

Hope this helps...
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Apr 29, 2008 11:50 am    Post subject: Reply with quote
Hi,
Forgot to mention few important parts required.

The actual query is
Code:

SELECT T1.ASSOCID, T1.ASSOCTP,(T1.ASPYAMT - T1.ASPROFEE),
            T2.LSTNAM,T2.FSTNAM,
            T4.LSTNAM,T4.FSTNAM
FROM ASSOC_MASTER_PYT T1 ,                                       
         ASSOC_ASS_NAMES T2 ,                                     
     (SELECT * FROM ASSOC_ASS_NAMES WHERE NAMCDE = '1') T4
WHERE (T1.ASSOCID = T2.ASSOCID)
 AND    (T1.ASSOCID = T4.ASSOCID)
 AND (T1.CKISSUEST = 'H' AND T1.CKPYDTE = '2008-04-18')           
AND T2.NAMCDE = '2'


This query given above presents unique ASSOCID along with selected columns.
For Example currently this results in result set with 5 records.

Now, I need to add one more fee component in (T1.ASPYAMT - T1.ASPROFEE) which makes it to be (T1.ASPYAMT - T1.ASPROFEE - T5.MISCFEE). The updated query would be as below.
Code:

SELECT T1.ASSOCID, T1.ASSOCTP,(T1.ASPYAMT - T1.ASPROFEE - T5.MISCFEE),
            T2.LSTNAM,T2.FSTNAM,
            T4.LSTNAM,T4.FSTNAM
FROM ASSOC_MASTER_PYT T1 ,                                       
         ASSOC_ASS_NAMES T2 ,                                     
     (SELECT * FROM ASSOC_ASS_NAMES WHERE NAMCDE = '1') T4
     (SELECT * FROM ASSOC_MISC_FEE WHERE CKPYDTE = '2008-04-18') T5

WHERE (T1.ASSOCID = T2.ASSOCID)
 AND    (T1.ASSOCID = T4.ASSOCID)
*****EXTRA CONDITION GOES HERE*************
 AND    (T1.ASSOCID = T5.ASSOCID)
*****EXTRA CONDITION ENDSHERE*************
 AND (T1.CKISSUEST = 'H' AND T1.CKPYDTE = '2008-04-18')           
AND T2.NAMCDE = '2'     

Newly included table may or may not have record for ASSOCID. When I run this query this gives me only 1 record. This is the record for which table T5 has entry.

So Basically I need to subtract based on condition of existence here.

I tried below mentioned case statement but seems like its missing some other condition as well..
Code:

SELECT T1.ASSOCID, T1.ASSOCTP,
CASE T5.MISCFEE
               WHEN (SELECT COUNT(*) FROM JISPRO.FCS_DRA_T                   
                    WHERE T1.FCCASENO = T5.FCCASENO) > 0   
                      THEN 1                                     
              WHEN (SELECT COUNT(*) FROM JISPRO.FCS_DRA_T                   
                  WHERE T1.FCCASENO = T5.FCCASENO) = 0   
                     THEN 0
END AS MISCFEE,
(T1.ASPYAMT - T1.ASPROFEE - T5.MISCFEE),
 T2.LSTNAM,T2.FSTNAM,
 T4.LSTNAM,T4.FSTNAM

Rest as it ...

Can anyone help me out with this situation?

Thanks,
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Apr 29, 2008 11:53 am    Post subject: Reply with quote
Thanks for the reply,
I did try COALESCE function but it ain't working. It results in 1 record only for which misc_fee table has entry.
COALESCE is also missing the records for which entry in MISC_FEE is not found.

Thanks,
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Apr 29, 2008 12:16 pm    Post subject: Reply with quote
Below is the COALESCE query which i tried.
Code:

SELECT T1.ASSOCID, T1.ASSOCTP,
           (T1.ASPYAMT - T1.ASPROFEE - COALESCE(T5.MISCFEE,0)),
            T2.LSTNAM,T2.FSTNAM,
            T4.LSTNAM,T4.FSTNAM
FROM ASSOC_MASTER_PYT T1 ,                                       
         ASSOC_ASS_NAMES T2 ,                                     
     (SELECT * FROM ASSOC_ASS_NAMES WHERE NAMCDE = '1') T4
WHERE (T1.ASSOCID = T2.ASSOCID)
 AND    (T1.ASSOCID = T4.ASSOCID)
 AND (T1.CKISSUEST = 'H' AND T1.CKPYDTE = '2008-04-18')           
AND T2.NAMCDE = '2'

Here, I am not changing WHERE condition but making changes only to SELECT Statement for inclution of COALESCE.

This gives me result set of 10 records. It is always double number of records than original result set. I believe the reason would be it runs loops twice. Once with MISC_FEE Amount and second without MISC_FEE amount.
However I am not quite sure on why this would happen.

Thanks,
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Wed Apr 30, 2008 7:02 pm    Post subject: Reply with quote
rajen,

Since you are having fetching the fee column from a different table , it may or may not have a corresponding column to the key column table 1. You need to pad zero for every non matching record from ASSOC_MISC_FEE table. In order to do that you can try something like like (untested code)

Code:

 (SELECT A.FCCASENO
      ,B.MISCFEE  AS MISCFEE
  FROM ASSOC_MASTER_PYT A
      ,ASSOC_MISC_FEE   B
 WHERE B.CKPYDTE  = '2008-04-18'
   AND A.FCCASENO = B.FCCASENO
UNION
SELECT A.FCCASENO
      ,INT(0)     AS MISCFEE
  FROM ASSOC_MASTER_PYT A
      ,ASSOC_MISC_FEE   B
 WHERE B.CKPYDTE   = '2008-04-18'
   AND A.FCCASENO <> B.FCCASENO)  T5



The first query fetches all the rows which have a matching record in both ASSOC_MASTER table and ASSOC_MISC_FEE table

and the second query fetches all the rows which do NOT have a match in both tables. with a value of integer 0 as fee (change it decimal(0) if the fee column is decimal) and we join these 2 results using an union.

Now you can store this resultant table as a another table and join it to the main table.

Code:

SELECT T1.ASSOCID,
      ,T1.ASSOCTP
      ,(T1.ASPYAMT - T1.ASPROFEE - T5.MISCFEE),
      ,T2.LSTNAM
      ,T2.FSTNAM,
      ,T4.LSTNAM
      ,T4.FSTNAM
 FROM ASSOC_MASTER_PYT T1
     ,ASSOC_ASS_NAMES T2                                     
     (SELECT * FROM ASSOC_ASS_NAMES WHERE NAMCDE = '1') T4
     (SELECT A.FCCASENO
            ,B.MISCFEE  AS MISCFEE
        FROM ASSOC_MASTER_PYT A
            ,ASSOC_MISC_FEE   B
       WHERE B.CKPYDTE  = '2008-04-18'
         and A.FCCASENO = B.FCCASENO
       UNION
     SELECT A.FCCASENO
           ,INT(0)     AS MISCFEE
        FROM ASSOC_MASTER_PYT A
            ,ASSOC_MISC_FEE   B
       WHERE B.CKPYDTE   = '2008-04-18'
         and A.FCCASENO <> B.FCCASENO)  T5

all your  conditions here

....


Hope this helps...

Cheers
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group