Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Display the emp of eath claims where the date < 10 YRs

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

New User


Joined: 18 Apr 2005
Posts: 67

PostPosted: Sat Nov 18, 2006 3:59 pm    Post subject: Display the emp of eath claims where the date < 10 YRs
Reply with quote

I want to display the emp names of death claims where the date of death is within 10 years of the policy start date. Details:


crD-tnc,(Date of Death) column belongs r_deth table

Crd_PLCY_start ,(Policystart Date)column belongs r-plcy table
emp name belongs to r_edetails table
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Sat Nov 18, 2006 8:41 pm    Post subject:
Reply with quote

Hey Rambabu,

Please tell that how we relate r_edetails ,r_deth ,r-plcy table. means what 's the common column between these table.

Hope u understood what I m asking means the relationship between this table like the foreign key ...
Back to top
View user's profile Send private message
rambabu
Currently Banned

New User


Joined: 18 Apr 2005
Posts: 67

PostPosted: Sun Nov 19, 2006 6:14 pm    Post subject: Re: Display the emp of eath claims where the date < 10 Y
Reply with quote

claim id is cmmun field u will give the query
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Sun Nov 19, 2006 7:58 pm    Post subject:
Reply with quote

Hi there,


Code:
Select A.crD-tnc,
          B.Crd_PLCY_start
          C.emp_name

From r_deth A,r-plcy B,r_edetails C
where A.claim_id = B.claim_id
    and B.claim_id = C.claim_id
    and Year ( date(A.crD-tnc) - Date(B.Crd_PLCY_start ))<= 10;

Hope It will helpful
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Mon Nov 20, 2006 10:59 am    Post subject: Re: Display the emp of eath claims where the date < 10 Y
Reply with quote

Rambabu,

Try this... It'll compare differences of dates for exactly 10 years...
Code:
SELECT   A.CRD-TNC,
        B.CRD_PLCY_START,
        C.EMP_NAME
FROM    R_DEATH A,
   R_PLCY B,
   R_EDETAILS C
WHERE    B.CLM_ID = A.CLM_ID
    AND B.CLM_ID = C.CLM_ID
    AND A.CRD-TNC - B.CRD_PLCY_START <= 100000;


Note: Here "100000" correspond to 10 Yrs, 00 Months, 00 Days.
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 display the sign only if the value is... useit DFSORT/ICETOOL 3 Wed Oct 04, 2017 5:53 pm
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Binary to display characters Roy Ware SYNCSORT 3 Tue Sep 19, 2017 8:46 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts DB2 NULL Values display V S Amarendra Reddy DB2 3 Thu Sep 07, 2017 6:59 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us