Hi everyone,
I am trying to concatenate several tables with the same fields and would like to have the final table ordered by one of the fields that changes, "SPCode". When I put "order by SPCode" under the resident lines, it will not run as I am calculating that field in the load statement, but I can't figure out how to get the final table to be ordered by SPCode. Can you recommend a way to order each individual table being concatenated by SPCode or is there a way to order the final table after it has been concatenated? Thanks in advance!
ACCIDENT_STAT_MORT:
NOCONCATENATE
LOAD [!1],
SPCode,
RATE_SERIES_AC,
PLAN_CODE,
SORCE_POLCY_ID,
AS_OF_DATE,
WELLNESS_BENEFIT,
PLAN_TYPE,
PLAN_LEVEL,
COMPANY,
MARKET_SEGMENT,
SEX,
UNITS,
COVG_IND,
ISSUE_ST,
AGE_AT_ENTRY,
ENTRY_YEAR,
ENTRY_MONTH,
ENTRY_DAY,
ANN_PREM_PU,
COHORT_CD,
INIT_DAC_EXPNS_VOL,
INIT_DAC_CMMSN_VOL
FROM
[$(vDIR_MP_FILE)\HVAC_P.CLA$(vMONTH)$(vYEAR).txt]
(txt, utf8, embedded labels, delimiter is ',', msq);
Concatenate
LOAD DISTINCT /*Year 1*/
[!1],
SPCode,
RATE_SERIES_AC,
PLAN_CODE,
SORCE_POLCY_ID,
AS_OF_DATE,
WELLNESS_BENEFIT,
PLAN_TYPE,
PLAN_LEVEL,
COMPANY,
MARKET_SEGMENT,
SEX,
UNITS,
COVG_IND,
ISSUE_ST,
AGE_AT_ENTRY,
ENTRY_YEAR,
ENTRY_MONTH,
ENTRY_DAY,
ANN_PREM_PU,
COHORT_CD,
INIT_DAC_EXPNS_VOL,
INIT_DAC_CMMSN_VOL
RESIDENT ACCIDENT_STAT_MORT3
;
Concatenate
LOAD DISTINCT /*Year 2*/
[!1],
(SPCode+1000) AS SPCode,
RATE_SERIES_AC,
PLAN_CODE,
SORCE_POLCY_ID,
AS_OF_DATE,
WELLNESS_BENEFIT,
PLAN_TYPE,
PLAN_LEVEL,
COMPANY,
MARKET_SEGMENT,
SEX,
UNITS,
COVG_IND,
ISSUE_ST,
AGE_AT_ENTRY,
(ENTRY_YEAR + 1) AS ENTRY_YEAR,
ENTRY_MONTH,
ENTRY_DAY,
ANN_PREM_PU,
COHORT_CD,
INIT_DAC_EXPNS_VOL,
INIT_DAC_CMMSN_VOL
RESIDENT ACCIDENT_STAT_MORT3
;
CONCATENATE
LOAD DISTINCT /*Year 3*/
[!1],
(SPCode+1000) AS SPCode,
RATE_SERIES_AC,
PLAN_CODE,
SORCE_POLCY_ID,
AS_OF_DATE,
WELLNESS_BENEFIT,
PLAN_TYPE,
PLAN_LEVEL,
COMPANY,
MARKET_SEGMENT,
SEX,
UNITS,
COVG_IND,
ISSUE_ST,
AGE_AT_ENTRY,
(ENTRY_YEAR + 1) AS ENTRY_YEAR,
ENTRY_MONTH,
ENTRY_DAY,
ANN_PREM_PU,
COHORT_CD,
INIT_DAC_EXPNS_VOL,
INIT_DAC_CMMSN_VOL
RESIDENT ACCIDENT_STAT_MORT3
;
CONCATENATE
LOAD DISTINCT /*Year 4*/
[!1],
(SPCode+1000) AS SPCode,
RATE_SERIES_AC,
PLAN_CODE,
SORCE_POLCY_ID,
AS_OF_DATE,
WELLNESS_BENEFIT,
PLAN_TYPE,
PLAN_LEVEL,
COMPANY,
MARKET_SEGMENT,
SEX,
UNITS,
COVG_IND,
ISSUE_ST,
AGE_AT_ENTRY,
(ENTRY_YEAR + 1) AS ENTRY_YEAR,
ENTRY_MONTH,
ENTRY_DAY,
ANN_PREM_PU,
COHORT_CD,
INIT_DAC_EXPNS_VOL,
INIT_DAC_CMMSN_VOL
RESIDENT ACCIDENT_STAT_MORT3
;
CONCATENATE
LOAD DISTINCT /*Year 5*/
[!1],
(SPCode+1000) AS SPCode,
RATE_SERIES_AC,
PLAN_CODE,
SORCE_POLCY_ID,
AS_OF_DATE,
WELLNESS_BENEFIT,
PLAN_TYPE,
PLAN_LEVEL,
COMPANY,
MARKET_SEGMENT,
SEX,
UNITS,
COVG_IND,
ISSUE_ST,
AGE_AT_ENTRY,
(ENTRY_YEAR + 1) AS ENTRY_YEAR,
ENTRY_MONTH,
ENTRY_DAY,
ANN_PREM_PU,
COHORT_CD,
INIT_DAC_EXPNS_VOL,
INIT_DAC_CMMSN_VOL
RESIDENT ACCIDENT_STAT_MORT3
;
DROP TABLE ACCIDENT_STAT_MORT3;
DROP TABLE MIN_STAT_MORT;
