2 thoughts/questions; can you apply a mask to the transform so you know you are getting the same length for all the transformed fields and 2, are there any null values in the fields combined to form the KEY column?
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul Newton Sent: Thursday, February 21, 2019 9:51 AM To: profoxtech@leafe.com Subject: Error building sort key (Error 2186)
Hi all
According to the help: This error is generated when sort key truncation is about to occur, typically during GROUP BY, ORDER BY or other sorting operations. This can happen with use of a sort key that contains an expression, such as a Memo field, whose length is not fixed.
The problem manifests itself when executing the following SQL (sorry about the length of the statement):
Select Str(Nh_Year,4,0) + Str(Nh_Period,2,0)+ Transform(NH_YEAR) + Transform(NH_NACNT) + Transform(NH_PERIOD) As Key, ; Nh_Year, Nh_Period,Str(Nh_Year,4,0) + '/' + Str(Nh_Period,2,0) As YearPeriod,Nh_NType, Nh_NSubt, Nh_Nacnt, Nh_NCntr, ; Nh_Job, Nh_Project, Sum(Nh_PtdDr) As Nh_PtdDr, -1 * Sum(Nh_PtdCr) As Nh_PtdCr, Sum(Nh_Bal) As Nh_Bal,Sum(NH_BUDG) As Nh_Budg, ; 999999999999.99 As Variance, 999999999999.99 As YTDBal, 999999999999.99 As YTDBudg, 999999999999.99 As YTDVar ;
From NHist Where NH_YEAR >= 2019 And NH_YEAR <= 2019 And NH_PERIOD >= 1 And NH_PERIOD <= 3 And NH_YEAR = 2019 ;
And NH_NACNT = 'A110 ' And NH_PERIOD = 1 And Nh_RecType = 1 ; Group By Key Union Select Str(Nh_Year,4,0) + Str(Nh_Period,2,0)+ Transform(NH_YEAR) + Transform(NH_NACNT) + Transform(NH_PERIOD) As Key, ; Nh_Year, Nh_Period,Str(Nh_Year,4,0) + '/' + Str(Nh_Period,2,0) As YearPeriod,; Nh_NType, Nh_NSubt, Nh_Nacnt, Nh_NCntr, Nh_Job, Nh_Project, Sum(Nh_PtdDr) As Nh_PtdDr, -1 * Sum(Nh_PtdCr) As Nh_PtdCr, ; Sum(Nh_Bal) As Nh_Bal,Sum(NH_BUDG) As Nh_Budg, 999999999999.99 As Variance, 999999999999.99 As YTDBal, ; 999999999999.99 As YTDBudg, 999999999999.99 As YTDVar From NHist Where NH_YEAR >= 2019 And NH_YEAR <= 2019 ; And NH_PERIOD >= 1 And NH_PERIOD <= 3 And NH_NACNT = 'A110 ' And Empty(NH_JOB) And Empty(NH_PROJECT) ; And Empty(NH_NCNTR) And Inlist(Nh_RecType,2,3) Group By Key InTo Cursor _5F50SKUYV
Now I know this is a fairly complex query but it does not contain any columns whose length is not fixed. I wonder if anybody has come across this error before. I have had to resort to completely rewriting the code that generates the SQL statement.
Many thanks
Paul Newton