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
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
Richard Good suggestion - I'll give it a try. Many thanks Paul Newton
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Richard Kaye Sent: 21 February 2019 15:08 To: profoxtech@leafe.com Subject: RE: Error building sort key (Error 2186)
Sent by an external sender ------------------------------
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
[excessive quoting removed by server]
Maybe the result length of the GROUP BY is longer than 120 char? That's the maximum key length in an index, which VFP uses to sort the intermediate results.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Paul Newton Gesendet: Donnerstag, 21. Februar 2019 15:51 An: 'profox@leafe.com' profox@leafe.com Betreff: 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
[excessive quoting removed by server]
Thanks Juergen - I'll check that Paul Newton
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of juergen@wondzinski.de Sent: 21 February 2019 15:09 To: profoxtech@leafe.com Subject: AW: Error building sort key (Error 2186)
Sent by an external sender ------------------------------
Maybe the result length of the GROUP BY is longer than 120 char? That's the maximum key length in an index, which VFP uses to sort the intermediate results.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Paul Newton Gesendet: Donnerstag, 21. Februar 2019 15:51 An: 'profox@leafe.com' profox@leafe.com Betreff: 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
[excessive quoting removed by server]
That wasn't the problem - but Richard's suggestion was spot on - Thanks Richard
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of juergen@wondzinski.de Sent: 21 February 2019 15:09 To: profoxtech@leafe.com Subject: AW: Error building sort key (Error 2186)
Sent by an external sender ------------------------------
Maybe the result length of the GROUP BY is longer than 120 char? That's the maximum key length in an index, which VFP uses to sort the intermediate results.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Paul Newton Gesendet: Donnerstag, 21. Februar 2019 15:51 An: 'profox@leafe.com' profox@leafe.com Betreff: 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
[excessive quoting removed by server]
Not often I get to an answer before wOOdy. 😊 You're welcome, Paul. BTW which was it; nulls or forcing the length to be the same?
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul Newton Sent: Thursday, February 21, 2019 10:49 AM To: profoxtech@leafe.com Subject: RE: Error building sort key (Error 2186)
That wasn't the problem - but Richard's suggestion was spot on - Thanks Richard
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of juergen@wondzinski.de Sent: 21 February 2019 15:09 To: profoxtech@leafe.com Subject: AW: Error building sort key (Error 2186)
Sent by an external sender ------------------------------
Maybe the result length of the GROUP BY is longer than 120 char? That's the maximum key length in an index, which VFP uses to sort the intermediate results.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Paul Newton Gesendet: Donnerstag, 21. Februar 2019 15:51 An: 'profox@leafe.com' profox@leafe.com Betreff: 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
[excessive quoting removed by server]
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/MN2PR02MB5920538268C74CEE2DF2DAB3A17E... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Report [OT] Abuse: http://leafe.com/reportAbuse/MN2PR02MB5920538268C74CEE2DF2DAB3A17E0@MN2PR02M...
Try:
GROUP BY Nh_Year,Nh_Period,NH_YEAR,NH_NACNT,NH_PERIOD
And any existing indexes using those columns will be used for a much faster result.
On Thu, Feb 21, 2019 at 10:56 AM Richard Kaye rkaye@invaluable.com wrote:
Not often I get to an answer before wOOdy. 😊 You're welcome, Paul. BTW which was it; nulls or forcing the length to be the same?
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul Newton Sent: Thursday, February 21, 2019 10:49 AM To: profoxtech@leafe.com Subject: RE: Error building sort key (Error 2186)
That wasn't the problem - but Richard's suggestion was spot on - Thanks Richard
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of juergen@wondzinski.de Sent: 21 February 2019 15:09 To: profoxtech@leafe.com Subject: AW: Error building sort key (Error 2186)
Sent by an external sender
Maybe the result length of the GROUP BY is longer than 120 char? That's the maximum key length in an index, which VFP uses to sort the intermediate results.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Paul Newton Gesendet: Donnerstag, 21. Februar 2019 15:51 An: 'profox@leafe.com' profox@leafe.com Betreff: 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
[excessive quoting removed by server]
Hi Ted Well if I were writing it from scratch I would probably do that. However this was legacy code from 15 years ago and the powers that be insist on the lightest touch approach to fixing bugs because of the risk of destabilization/unforeseen consequences. Anyway Richard's suggestion worked just fine. Funny, in 35 years of working with Fox etc I have never come across this one. Paul Newton -----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Ted Roche Sent: 21 February 2019 16:12 To: profoxtech@leafe.com Subject: Re: Error building sort key (Error 2186)
Sent by an external sender ------------------------------
Try:
GROUP BY Nh_Year,Nh_Period,NH_YEAR,NH_NACNT,NH_PERIOD
And any existing indexes using those columns will be used for a much faster result.
On Thu, Feb 21, 2019 at 10:56 AM Richard Kaye rkaye@invaluable.com wrote:
Not often I get to an answer before wOOdy. 😊 You're welcome, Paul. BTW which was it; nulls or forcing the length to be the same?
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul Newton Sent: Thursday, February 21, 2019 10:49 AM To: profoxtech@leafe.com Subject: RE: Error building sort key (Error 2186)
That wasn't the problem - but Richard's suggestion was spot on - Thanks Richard
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of juergen@wondzinski.de Sent: 21 February 2019 15:09 To: profoxtech@leafe.com Subject: AW: Error building sort key (Error 2186)
Sent by an external sender
Maybe the result length of the GROUP BY is longer than 120 char? That's the maximum key length in an index, which VFP uses to sort the intermediate results.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Paul Newton Gesendet: Donnerstag, 21. Februar 2019 15:51 An: 'profox@leafe.com' profox@leafe.com Betreff: 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
[excessive quoting removed by server]
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CACW6n4uEFNw0=E2NMXUxd786sPVVShrQQ-9M... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. Report [OT] Abuse: http://leafe.com/reportAbuse/CACW6n4uEFNw0=E2NMXUxd786sPVVShrQQ-9MXdsyKszQq1...
It was forcing the length to be the same (which is what was hinted at in the help) - Thanks again Paul
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Richard Kaye Sent: 21 February 2019 15:56 To: profoxtech@leafe.com Subject: RE: Error building sort key (Error 2186)
Sent by an external sender ------------------------------
Not often I get to an answer before wOOdy. 😊 You're welcome, Paul. BTW which was it; nulls or forcing the length to be the same?
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul Newton Sent: Thursday, February 21, 2019 10:49 AM To: profoxtech@leafe.com Subject: RE: Error building sort key (Error 2186)
That wasn't the problem - but Richard's suggestion was spot on - Thanks Richard
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of juergen@wondzinski.de Sent: 21 February 2019 15:09 To: profoxtech@leafe.com Subject: AW: Error building sort key (Error 2186)
Sent by an external sender ------------------------------
Maybe the result length of the GROUP BY is longer than 120 char? That's the maximum key length in an index, which VFP uses to sort the intermediate results.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Paul Newton Gesendet: Donnerstag, 21. Februar 2019 15:51 An: 'profox@leafe.com' profox@leafe.com Betreff: 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
[excessive quoting removed by server]
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/MN2PR02MB5920538268C74CEE2DF2DAB3A17E... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Report [OT] Abuse: http://leafe.com/reportAbuse/MN2PR02MB5920538268C74CEE2DF2DAB3A17E0@MN2PR02M...
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/BN6PR10MB145809F360F26520252526C7D27E... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. Report [OT] Abuse: http://leafe.com/reportAbuse/BN6PR10MB145809F360F26520252526C7D27E0@BN6PR10M...
-
juergen＠wondzinski.de -
Paul Newton -
Richard Kaye -
Ted Roche