Fastexport help

UDA
Fan

Fastexport help

New to TeraData:Any help is appreciated. Thanks in advance

Trying to run a fastexport with an inner and outer select.
The inner select has aggregate function SUM.

Getting error:
22:30:16 UTY8713 RDBMS failure, 3504: Selected non-aggregate values must be part of the associated group.

Script is :

SELECT CAST(CHAR_LEN_STRT

TRIM(State_Cd) || '|' ||
TRIM(Company_Cd) || '|' ||
TRIM(SUBSTR(Line_Cd,1,1)) || '|' ||
TRIM(Line_Of_business_Cd) || '|' ||
TRIM(Policy_Form_Cd) || '|' ||
TRIM(Policy_Type_Cd) || '|' ||
TRIM(Location_Cd) || '|' ||
TRIM(SUBSTR(WP_Accounting_Yr,1,5)) || '|' ||
TRIM(SUBSTR(WP_Accounting_Qtr,1,2)) ||'|' ||
TRIM(Ct_Earned_Exposure_PRI) || '|' ||
TRIM(Am_Earned_Premium_PRI) || '|' ||

TRIM(CASE WHEN Policy_Insurance_Score_Cd = '^' THEN 0 ELSE Policy_Insurance_Score_Cd END) || '|' ||

TRIM(Farm_Bureau_In) || '|' CHAR_LEN_END

FROM (SELECT

TRIM(State_Cd)
,TRIM(Company_Cd)
,TRIM(SUBSTR(Line_Cd,1,1))
,TRIM(Line_Of_business_Cd)
,TRIM(Policy_Form_Cd)
,TRIM(Policy_Type_Cd)
,TRIM(Location_Cd)
,TRIM(SUBSTR(WP_Accounting_Yr,1,5))
,TRIM(SUBSTR(WP_Accounting_Qtr,1,2))
,TRIM(SUM(Ct_Earned_Exposure_PRI))
,TRIM(SUM(Am_Earned_Premium_PRI))

,TRIM(CASE WHEN Policy_Insurance_Score_Cd = '^' THEN 0 ELSE Policy_Insurance_Score_Cd END) Policy_Insurance_Score_Cd

,TRIM(Farm_Bureau_In)

FROM pricing_extract.RNTNWSTCD_LINECD_PRM_EXPRPRDYREXPRPRDQRTR_PRPR
GROUP BY

TRIM(State_Cd)
,TRIM(Company_Cd)
,TRIM(SUBSTR(Line_Cd,1,1))
,TRIM(Line_Of_business_Cd)
,TRIM(Policy_Form_Cd)
,TRIM(Policy_Type_Cd)
,TRIM(Location_Cd)
,TRIM(SUBSTR(WP_Accounting_Yr,1,5))
,TRIM(SUBSTR(WP_Accounting_Qtr,1,2))
,TRIM(Policy_Insurance_Score_Cd)
,TRIM(Farm_Bureau_In)
) X ;
1 REPLY
Enthusiast

Re: Fastexport help

Remove 'TRIM' from TRIM(Policy_Insurance_Score_Cd) in your Group By.

GROUP BY

TRIM(State_Cd)
,TRIM(Company_Cd)
,TRIM(SUBSTR(Line_Cd,1,1))
,TRIM(Line_Of_business_Cd)
,TRIM(Policy_Form_Cd)
,TRIM(Policy_Type_Cd)
,TRIM(Location_Cd)
,TRIM(SUBSTR(WP_Accounting_Yr,1,5))
,TRIM(SUBSTR(WP_Accounting_Qtr,1,2))
,TRIM(Policy_Insurance_Score_Cd)/*This is the culprit.Remove the 'Trim' */
,TRIM(Farm_Bureau_In)