Record selection without a sub report

[Former Member]

I have written the following SQL statement to select records. In one of the tables "CI_SA_RS_HIST" I need to only bring in the lastest effective dated record that is not greater than todays date. Any suggestions without going to a subreport? I have done the subreport option but with over a million records in any one table a subreport will be too slow.

SELECT

"CI_ACCT_PER"."ACCT_ID", "CI_ACCT_PER"."ACCT_REL_TYPE_CD", "CI_ACCT_PER"."PER_ID", "CI_ACCT_PER"."MAIN_CUST_SW", "CI_ACCT_PER"."FIN_RESP_SW", "CI_ACCT"."ACCT_ID",

"CI_PER"."PER_OR_BUS_FLG", "CI_PER"."CITY", "CI_PER"."POSTAL", "CI_PER"."GEO_CODE", "CI_PER"."STATE", "CI_PER"."COUNTRY", "CI_PER_NAME"."PER_ID", "CI_PER_NAME"."ENTITY_NAME", "CI_PER_NAME"."NAME_TYPE_FLG", "CI_PER_NAME"."PRIM_NAME_SW",

"CI_SA"."SA_ID", "CI_SA"."SA_TYPE_CD", "CI_SA"."SA_STATUS_FLG", "CI_SA_REL"."SA_REL_ID", "CI_SA_REL"."SA_ID", "CI_SA_REL"."SA_REL_STATUS_FLG", "CI_SA_REL"."SA_REL_USAGE_FLG", "CI_SA_REL"."SPR_CD", "CI_SA_REL"."SA_REL_TYPE_CD", "CI_SA"."CHAR_PREM_ID",

"CI_SPR"."SA_ID", "CI_SPR"."PER_ID",

"CI_PREM"."ADDRESS1", "CI_PREM"."PREM_ID",

"CI_SA_RS_HIST"."EFFDT", "CI_SA_RS_HIST"."RS_CD"

FROM ((((((("CISADM"."CI_SA" "CI_SA" LEFT OUTER JOIN "CISADM"."CI_ACCT" "CI_ACCT" ON "CI_SA"."ACCT_ID"="CI_ACCT"."ACCT_ID")

LEFT OUTER JOIN "CISADM"."CI_SA_REL" "CI_SA_REL" ON "CI_SA"."SA_ID"="CI_SA_REL"."SA_ID")

LEFT OUTER JOIN "CISADM"."CI_PREM" "CI_PREM" ON "CI_SA"."CHAR_PREM_ID"="CI_PREM"."PREM_ID")

LEFT OUTER JOIN "CISADM"."CI_SA_RS_HIST" "CI_SA_RS_HIST" ON "CI_SA"."SA_ID"="CI_SA_RS_HIST"."SA_ID")

LEFT OUTER JOIN "CISADM"."CI_ACCT_PER" "CI_ACCT_PER" ON "CI_ACCT"."ACCT_ID"="CI_ACCT_PER"."ACCT_ID")

LEFT OUTER JOIN "CISADM"."CI_PER" "CI_PER" ON "CI_ACCT_PER"."PER_ID"="CI_PER"."PER_ID")

LEFT OUTER JOIN "CISADM"."CI_PER_NAME" "CI_PER_NAME" ON "CI_PER"."PER_ID"="CI_PER_NAME"."PER_ID")

LEFT OUTER JOIN "CISADM"."CI_SPR" "CI_SPR" ON "CI_SA_REL"."SPR_CD"="CI_SPR"."SPR_CD"

WHERE "CI_PER_NAME"."PRIM_NAME_SW"='Y'

AND "CI_ACCT_PER"."MAIN_CUST_SW"='Y'

AND "CI_ACCT_PER"."FIN_RESP_SW"='Y'

AND "CI_SA_REL"."SA_REL_STATUS_FLG"='A'

and "CI_SA_RS_HIST"."EFFDT"<date(2009,1,1)

ORDER BY "CI_SA_REL"."SPR_CD", "CI_SA"."SA_ID", "CI_ACCT_PER"."ACCT_ID", "CI_ACCT_PER"."PER_ID"

[Former Member · Accepted solution]

Hi,

Are you expecting this one :

replace "CI_SA_RS_HIST"."EFFDT"<date(2009,1,1) with

"CI_SA_RS_HIST"."EFFDT" = SELECT MAX(DISTINCT EFFDT) FROM "CI_SA_RS_HIST" WHERE

"CI_SA_RS_HIST"."EFFDT"< current date

or

if you are using your existing query you can suppress your rows which is less than maximum date. for eg: section expert > supress>X-2--> DATEFIELDNAME<MAX(DATEFIELNAME).

Regards,

Sathish

[Former Member · Accepted solution]

Both solutions are great. The first solution is the way I am looking at however my problem is that i need to do it grouped by SA_ID. For each SA_ID record I want only the most current RS_CD determined by the effective date..