All Forums Tools
NW123 4 posts Joined 03/06
31 Mar 2006
Fastexport help

New to Teradata: any help is appreciated:Thanksgetting the error:22:30:16 UTY8713 RDBMS failure, 3504: Selected non-aggregate values must be part of the associated group.While running the script:===========================SELECT CAST(CHAR_LEN_STRTTRIM(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_EXPRPRDYREXPRPRDQ RTR_PRPRGROUP 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 ;

Barry-1604 176 posts Joined 07/05
31 Mar 2006

You need to make sure that you're grouping on the columns where you don't have an aggregate function. The easy way to do this is to use the column numbers, rather than repeat the column definitions in the "GROUP BY" clause. Here is how you would define your derived table: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_PRPRGROUP BY1,2,3,4,5,6,7,8,9,12,13or, you could copy the column definitions down into the "GROUP BY" clause.Hope this helps.Barry

NW123 4 posts Joined 03/06
31 Mar 2006

Barry ,I tried with 1,2,3...but still getting the same error:UTY8713 RDBMS failure, 3504: Selected non-aggregate values must be part of theassociated group.Do you think there is a problem with the CASE statement or with any other field in the select.Thanks for all your help.

NW123 4 posts Joined 03/06
01 Apr 2006

Hello Barry,Now I am getting the following error: All expressions in a derived table must have an explicit name.SELECT CAST(MAX(CHARACTER_LENGTH(TRIM(X.State_Cd) || '|' ||TRIM(X.Company_Cd) || '|' ||TRIM(SUBSTR(X.Line_Cd,1,1)) || '|' ||TRIM(X.Line_Of_business_Cd) || '|' ||TRIM(X.Ct_Earned_Exposure_PRI) || '|' ||TRIM(X.Am_Earned_Premium_PRI) || '|' ||TRIM(X.Policy_Insurance_Score_Cd) || '|' ||TRIM(X.Farm_Bureau_In) || '|' )) as CHAR(10))FROM (SELECT TRIM(State_Cd),TRIM(Company_Cd),TRIM(SUBSTR(Line_Cd,1,1)),TRIM(Line_Of_business_Cd),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.R03_HMWNR_PRM_200504_PRPRGROUP BY1, 2, 3, 4, 7, 8) X ;

Barry-1604 176 posts Joined 07/05
04 Apr 2006

You have to name each of the columns in your derived table. So, your query should look like this:SELECT CAST(MAX(CHARACTER_LENGTH(TRIM(X.State_Cd) || '|' ||TRIM(X.Company_Cd) || '|' ||TRIM(SUBSTR(X.Line_Cd,1,1)) || '|' ||TRIM(X.Line_Of_business_Cd) || '|' ||TRIM(X.Ct_Earned_Exposure_PRI) || '|' ||TRIM(X.Am_Earned_Premium_PRI) || '|' ||TRIM(X.Policy_Insurance_Score_Cd) || '|' ||TRIM(X.Farm_Bureau_In) || '|' )) as CHAR(10))FROM (SELECTTRIM(State_Cd) AS State_Cd,TRIM(Company_Cd) AS Company_Cd ,TRIM(SUBSTR(Line_Cd,1,1)) AS Line_Cd,TRIM(Line_Of_business_Cd) As Line_of_business_cd,TRIM(SUM(Ct_Earned_Exposure_PRI)) as ct_earned_exposure_PRI,TRIM(SUM(Am_Earned_Premium_PRI)) as 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) as Farm_Bureau_InFROM PRICING_EXTRACT.R03_HMWNR_PRM_200504_PRPRGROUP BY1, 2, 3, 4, 7, 8) X ;I would think that this would work now.Good luck.Barry

Vinay Agarwal 1 post Joined 02/15
17 Feb 2015

I have quetion on FastExport I dont know how to start a new topic in this forum the question is
 
We can use DML statements like delete in fastexport before BEGIN EXPORT statement but why we cannot use it after the END EXPORT statement because when I am trying to delete the data from stage table after exporting its sayign only SELECT can be used I know thats true with FEXPORT we cannot use anything else then SELECT but how to implement my scenario then..
Thanks in advance for your helps.. Really will appreciate your help
And this is my first quetion in this Forum I registered today only :)

You must sign in to leave a comment.