Fastexport help

Tools
N/A

Fastexport help

New to Teradata: any help is appreciated:Thanks

getting 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_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 ;

5 REPLIES

Re: Fastexport help

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_PRPR
GROUP BY
1,2,3,4,5,6,7,8,9,12,13

or, you could copy the column definitions down into the "GROUP BY" clause.

Hope this helps.

Barry
N/A

Re: Fastexport help

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 the
associated 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.
N/A

Re: Fastexport help

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_PRPR
GROUP BY
1, 2, 3, 4, 7, 8
) X ;

Re: Fastexport help

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 (SELECT
TRIM(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_In
FROM PRICING_EXTRACT.R03_HMWNR_PRM_200504_PRPR
GROUP BY
1, 2, 3, 4, 7, 8
) X ;

I would think that this would work now.

Good luck.

Barry

Re: Fastexport help

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 :)