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
 

 

Help needed in Conditional statement in DB2 SQL

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

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Tue Sep 15, 2015 10:14 am    Post subject: Help needed in Conditional statement in DB2 SQL
Reply with quote

Hi All,

This is an existing SQL query that is fetching active account with all details (single row).Here we are joining TABLE_1 and TABLE_2 to get Account, Policy No, Total premium and then join TABLE_3 and TABLE_4 to get City and State value, after joining these 2 sets of tables.

Reqt: Without disturbing the existing extracted rows, the clients need Rate value also that is currently mapped under COVERAGE field value '100' in TABLE_1. Both Rate value and COVERAGE is in TABLE_1.


Code:
SELECT B1.ACCT,B1.PLCY,B1.PREMIUM,A1.CITY,A1.STATE
  FROM
  ( SELECT TBL1.ACCT,
           TBL1.PLCY,
           TBL2.RISK,
           TBL2.TERR,
           SUM(TBL1.PREMIUM) AS PREMIUM
      FROM TABLE_1 TBL1
           INNER JOIN
              TABLE_2 TBL2
        ON TBL1.ACCT = TBL2.ACCT
       AND TBL1.PLCY = TBL2.PLCY
       AND TBL1.POL_EFF_DT < CURRENT_DATE
       AND TBL1.POL_EXP_DT >= CURRENT_DATE - 1 DAY
  GROUP BY TBL1.ACCT,TBL1.PLCY,TBL2.RISK,TBL2.TERR)   AS B1

   INNER JOIN

  (SELECT TBL3.NAME,
          TBL3.CITY,
          TBL4.STATE
          TBL4.ZIP_CD
     FROM TABLE_3  TBL3
          INNER JOIN
          TABLE_4 TBL4
       ON TBL3.ACCT = TBL4.ACCT
      AND TBL3.PLCY = TBL4.PLCY
      AND TBL3.POL_EFF_DT < CURRENT_DATE
      AND TBL3.POL_EFF_DT >= CURRENT_DATE - 1 DAY
 GROUP BY TBL3.NAME,TBL3.CITY,TBL4.STATE,TBL4.ZIP_CD)  AS A1

       ON B1.ACCT = A1.ACCT
      AND B1.PLCY = A1.PLCY
      AND B1.POL_EFF_DT = A1.POL_EFF_DT;   



Concern: An account will have lot number of coverages. So if I mention COV check in WHERE clause under B1 select clause as mentioned below, it retrieves multiple rows for single account.

My intention here is to get the Rate value mapped under COVERAGE '100' and displayed across account (single row).


Code:
SELECT B1.ACCT,B1.PLCY,B1.PREMIUM,A1.CITY,A1.STATE,
       CASE WHEN B1.COV = 100 THEN B1.RATE AS RATE
  FROM
  ( SELECT TBL1.ACCT,
           TBL1.PLCY,
           TBL2.RISK,
           TBL2.TERR,
           SUM(TBL1.PREMIUM) AS PREMIUM           
           TBL1.COV
      FROM TABLE 1 TBL1
           INNER JOIN
           TABLE 2 TBL2
        ON TBL1.ACCT = TBL2.ACCT
       AND TBL1.PLCY = TBL2.PLCY
      AND TBL1.POL_EFF_DT < CURRENT_DATE
      AND TBL1.POL_EFF_DT >= CURRENT_DATE - 1 DAY
  GROUP BY TBL1.ACCT,TBL1.PLCY,TBL2.RISK,TBL2.TERR,TBL1.COV)   AS B1

   INNER JOIN

  (SELECT TBL3.NAME,
          TBL3.CITY,
          TBL4.STATE
          TBL4.ZIP_CD
     FROM TABLE_3  TBL3
          INNER JOIN
          TABLE_4 TBL4
       ON TBL3.ACCT = TBL4.ACCT
      AND TBL3.PLCY = TBL4.PLCY
      AND TBL3.POL_EFF_DT < CURRENT_DATE
      AND TBL3.POL_EFF_DT >= CURRENT_DATE - 1 DAY
 GROUP BY TBL3.NAME,TBL3.CITY,TBL4.STATE,TBL4.ZIP_CD)  AS A1

       ON B1.ACCT = A1.ACCT
      AND B1.PLCY = A1.PLCY
      AND B1.POL_EFF_DT = A1.POL_EFF_DT;   


Please help in accomplishing the result.

Thanks
Vinu
Back to top
View user's profile Send private message

Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1218
Location: Israel

PostPosted: Tue Sep 15, 2015 11:51 am    Post subject:
Reply with quote

I can't even think about your question, the only thing I see are these lines:
Code:
      AND TBL1.POL_EFF_DT < CURRENT_DATE
      AND TBL1.POL_EFF_DT >= CURRENT_DATE - 1 DAY
Isn't it the same as
Code:
      AND TBL1.POL_EFF_DT = CURRENT_DATE - 1 DAY
?
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Tue Sep 15, 2015 12:03 pm    Post subject:
Reply with quote

Hi Marso,

Absolutely Sorry for POL_EFF_DT
it is

Code:
 AND TBL1.POL_EFF_DT < CURRENT_DATE
 AND TBL1.POL_EXP_DT >= CURRENT_DATE - 1 DAY


Currently the SQl query retrieves an Account number and corresponding City, State and Total premium associated with it (premium from all individual coverage of an account).

Now what we need is to get the Rate value associated with only Cov 100. If I am keeping COV 100 check in WHERE clause, I wont get the total premium instead I will get the premium of Cov 100. So I need total premium as well as the Rate value of Cov 100.

Thanks
Chidam
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Sep 15, 2015 3:16 pm    Post subject:
Reply with quote

although there is a lot of things wrong with supplied SQL (ex. A1 does not have ACCT and PLCT,.. columns, but these are used in the join )

I think what you need can be accomplished by moving the case inside B1 :

Code:
 ...
( SELECT TBL1.ACCT,
           TBL1.PLCY,
           TBL2.RISK,
           TBL2.TERR,
           SUM(TBL1.PREMIUM) AS PREMIUM           
           max(CASE WHEN B1.COV = 100 THEN B1.RATE ) AS RATE
           TBL1.COV
      FROM TABLE 1 TBL1
           INNER JOIN
           TABLE 2 TBL2
 ...
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Wed Sep 16, 2015 2:24 am    Post subject:
Reply with quote

Quote:
Now what we need is to get the Rate value associated with only Cov 100. If I am keeping COV 100 check in WHERE clause, I wont get the total premium instead I will get the premium of Cov 100. So I need total premium as well as the Rate value of Cov 100.

Think of Union as well (one with existing query and other with Cov 100 condition then you don't lose total premium) . But why are you afraid of using CURSOR though?
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Fri Sep 18, 2015 11:26 am    Post subject:
Reply with quote

Thanks Rohit.
I will try with UNION logic.

Rgds
Vinu
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm
This topic is locked: you cannot edit posts or make replies. Updating value of key using rewrite s... ParminderKumar COBOL Programming 4 Thu Aug 04, 2016 12:46 am
No new posts Help Needed with View Direct shailesh_do CA Products 2 Wed Jul 13, 2016 10:39 am


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