|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/1/2006 3:05:00 PM
Posts: 4,
Visits: 1
|
|
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 ;
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/20/2008 5:30:17 AM
Posts: 10,
Visits: 5
|
|
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)
|
|
|
|