Posted by [IP Address: 66.72.150.195] 'Meera' on May 22, 2001 at 10:05:39 EST:

In Reply to: Re: SQR Select Statement problem posted by [IP Address: 66.72.150.195] 'Rebecca' on May 18, 2001 at 16:38:34 EST:

This is the program from which I earlier copied that part that I thought was the problem.

!*********************************************************

! Test13a.sqr
! Multiple Report Program
!*********************************************************

! Set report column width

#Define col_emplid 11 !Employee Id
#Define col_empl_name 20 !Employee Name
#Define col_plan_type 5 !Plan Type
#Define col_cov_cd 8 !Coverage Code
#Define col_cov_drop_flag 20 !Dependent Coverage Termination
#Define col_effdt 12 !Effective Date
#Define col_dep_id 6 !Department Id
#Define col_dep_name 15 !Department Name
#Define col_sep 1 !Column Separator

!****************
Begin-Setup
!****************

Declare Layout EE_Data
Left-Margin=1
End-Declare

Declare Layout Term_Dependents
Orientation=Landscape
Left-Margin=0.3
End-Declare

Declare Report EE_Data
Layout=EE_Data
End-Declare

Declare Report Term_Dependents
Layout=Term_Dependents
End-Declare

End-Setup

!***************
Begin-Program
!***************
Do Process_Main

End-Program

!***************************************
Begin-Heading 4 For-Reports = (EE_Data)
!***************************************
Print 'List Of Employees by Plan Type'(1) Center
Print 'Page' (,+9)
Print #page-count (+1) edit 999

Print 'EMPLID ' (+1,1,{col_emplid})
Print 'Name ' (0,+1,{col_empl_name})
Print 'Plan ' (0,+{col_sep},{col_plan_type})
Print 'EffDate' (0,+{col_sep},{col_effdt})
Print 'Coverage Type' (0,+{col_sep},{col_cov_cd})
Print 'Dep Cov Terminated' (0,+{col_sep},{col_cov_drop_flag})
Print ' ' (+1,1,{col_emplid})
Print ' ' (0,+1,{col_empl_name})
Print 'Type ' (0,+{col_sep},{col_plan_type})
Print ' ' (0,+{col_sep},{col_effdt})
Print 'Code ' (0,+{col_sep},{col_cov_cd})
Print 'Term ' (0,+{col_sep},{col_cov_drop_flag})
Print '-' (+1,1,60) Fill

End-Heading

!*********************************************
Begin-Heading 4 For-Reports=(Term_Dependents)
!*********************************************
Print 'List of Terminated Dependents'(1) Center
Print 'Page'
Print #page-count
Print 'EMPLID' (+1,1,{col_emplid})
Print 'Name' (0,+{col_sep},{col_empl_name})
Print 'Plan Type' (0,+{col_sep},{col_plan_type})
Print 'EffDt' (0,+{col_sep},{col_effdt})
Print 'Dependent Id' (0,+{col_sep},{col_dep_id})
Print 'Dependent Name (0,+{col_sep},{col_dep_name})
Print '-' (+1,1,650) Fill

End-Heading

!*****************************
Begin-Procedure Process_Main
!*****************************

Begin-Select
A.Emplid
A.Plan_Type
A.Effdt
A.Covrg_Cd
B.Name

Do Check_Dep_Termination
If $Term = 'N'
Let $Term = ' '
End-If
Do Print_EE_Data

From Ps_Health_Benefit A , Ps_Personal_Data B
Where A.Effdt = (Select Max(Effdt) From Ps_Health_Benefit
Where Emplid = A.Emplid
And Plan_Type = A.Plan_Type
And Effdt <= Sysdate)
And A.Coverage_Elect Not In ('T','W')
And A.Emplid = B.Emplid
Order By A.Emplid, A.Plan_Type
End-Select

End-Procedure

!*************************************
Begin-Procedure Check_Dep_Termination
!*************************************

Move 'N' To $Term

Begin-Select
C.Emplid
C.Plan_Type
C.Effdt
D.Dependent_Benef

Move 'Y' To $Term
Do Print_Terminated_Dependents
Show 'Dep Term found =' &D.Dependent_Benef
'for Employee ' &C.Emplid

From Ps_Health_Benefit C, Ps_Health_Dependnt D

Where C.Emplid = A.Emplid
And C.Plan_Type = A.Plan_Type
And C.Emplid = D.Emplid
And C.Plan_Type = D.Plan_Type
And C.Effdt = D.Effdt
And C.Effdt = (Select Max(E.Effdt From Health_Benefit E
Where C.Emplid = E.Emplid
And C.Plan_Type = E.Plan_Type
And C.Effdt < &A.Effdt)
And D.Dependent_Benef Not In
(Select F_Dependent_Benef
From Health_Dependent F
Where C.Emplid = F.Emplid
And C.Plan_Type = F.Plan_Type
And C.Effdt < &A.Effdt
End-Select
End-Procedure

!******************************
Begin-Procedure Print_EE_Data
!******************************

Use-Report EE_Data
Let $EmplID = &A.Emplid
Let $Date_Str = Datetostr(&A.Effdt,'mm/dd/yyyy')
Print &A.Emplid (+1,1,{col_emplid})
On-Break Print = Change/Top-Page
Print &B.Name (0,+{col_sep},{col_empl_name})
On-Break Print = Change/Top-Page
Print &A.Plan_Type (0,+{col_sep},{col_plan_type})
Print &A.Date_Str (0,+{col_sep},{col_effdt})
Print &A.Covrg_Cd (0,+{col_sep},{col_cov_cd})
Print &Term (0,+{col_sep},{col_cov_drop_flag})

Add 1 to #ee_rcds

End-Procedure

!***********************************
Begin-Procedure Print_TerminatedDependent_Name
!***********************************

Let $Dep_Name = ' '
Begin-Select
Name
Move &Name to $Dep_Name
from Dependent_Benef
Where EMPLID=&A.Emplid
and Dependent_Benef = &D.Dependent_Benef
End-Select
End-Procedure

#include 'html.inc'