Re: SQR-USERS Digest - 31 May 2002 to 3 Jun 2002 (#2002-131)

[kirthi ajad <mymvs999@YAHOO.COM>]

Here is my select statement in SQR
 Begin-Select
 Alter-Printer Font=5 Point-Size=10
MEPE.PDPD_ID () On-Break Set=1 Level=1 Before=BeforeProduct After=AfterProduct 
Print=never Save=$OldProductID
GRGR.GRGR_ID () On-Break Set=1 Level=2 Before=BeforeGroup Print=never
SGSG.SGSG_ID () On-Break Set=1 Level=3 Before=BeforeSubGroup Print=never
SBSB.SBSB_ID &SubscriberID
SBSB.SBSB_FIRST_NAME &FirstName
SBSB.SBSB_MID_INIT &MidInit
SBSB.SBSB_LAST_NAME &LastName
 Do FormatName
 Do PrintInfo
   Print-Direct printer=html '%%ResetColor'
 Next-Listing  SkipLines=2 Need=12
>From  {reportenv}..CMC_SBSB_SUBSC SBSB,
      {reportenv}..CMC_GRGR_GROUP GRGR,
      {reportenv}..CMC_SGSG_SUB_GROUP SGSG,
      {reportenv}..CMC_SBSG_RELATION SBSG,
      {reportenv}..CMC_MEME_MEMBER MEME,
      {reportenv}..CMC_MEPE_PRCS_ELIG MEPE
Where GRGR.GRGR_CK = [$Condition]
 AND  SBSB.SBSB_CK = SBSG.SBSB_CK
 AND  SBSG.SGSG_CK = SGSG.SGSG_CK
 AND  GRGR.GRGR_CK = MEME.GRGR_CK
 AND  SBSB.SBSB_CK = MEME.SBSB_CK
 AND  MEME.MEME_CK = MEPE.MEME_CK
 AND  $EligibilityDate BETWEEN MEPE.MEPE_EFF_DT AND MEPE.MEPE_TERM_DT
 AND  MEPE.MEPE_ELIG_IND = 'Y'
 AND  MEPE.CSPD_CAT = 'M'
 AND  MEME.MEME_SFX = 00
[$OrderBy]
  kirthi ajad <mymvs999@yahoo.com> wrote:
Hi All,

I am passing 3 input paramters in my SQR i.e GroupId, SubGroupID, 
EligibilityDate

GroupId and Eligibility date are required. SubGroupId is not required.

The problem is when I enter all 3 fields I am getting correct data. When I left 
blank(Leave blank for all records) subGroupID I am getting duplicate records in 
my report with same data whose records are same under multiple GroupIds.

Also the same records are existing under different GroupIDs.. Why this is 
happending?
Could anyone please help me in this regard.

Here is my SQL statement.

Select distinct
MEPE.PDPD_ID,
GRGR.GRGR_ID,
SGSG.SGSG_ID,
SBSB.SBSB_ID,
SBSB.SBSB_FIRST_NAME ,
SBSB.SBSB_MID_INIT,
SBSB.SBSB_LAST_NAME

>From  CMC_SBSB_SUBSC SBSB,
      CMC_GRGR_GROUP GRGR,
      CMC_SGSG_SUB_GROUP SGSG,
      CMC_SBSG_RELATION SBSG,
      CMC_MEME_MEMBER MEME,
      CMC_MEPE_PRCS_ELIG MEPE
Where GRGR.GRGR_CK = SBSB.GRGR_CK
 AND  SBSB.SBSB_CK = SBSG.SBSB_CK
 AND  SBSG.SGSG_CK = SGSG.SGSG_CK
 AND  GRGR.GRGR_CK = MEME.GRGR_CK
 AND  SBSB.SBSB_CK = MEME.SBSB_CK
 AND  MEME.MEME_CK = MEPE.MEME_CK
 AND  $EligibilityDate BETWEEN MEPE.MEPE_EFF_DT AND MEPE.MEPE_TERM_DT
 AND  MEPE.MEPE_ELIG_IND = 'Y'
 AND  MEPE.CSPD_CAT = 'M'
 AND  MEME.MEME_SFX = 00
order by MEPE.PDPD_ID, GRGR.GRGR_ID, SGSG.SGSG_ID, SBSB.SBSB_LAST_NAME

Thanks



---------------------------------
Do You Yahoo!?
Sign-up for Video Highlights of 2002 FIFA World Cup