Skip to Content
0
Former Member
Apr 09, 2009 at 02:17 AM

Record selection without a sub report

26 Views

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"