[SOLVED] Problem getting Grid Grouping feature to work when MVC coding used

[ScottLamb]

When I try to add Grouping feature to my code I get a SQL Error from vvcall.

{"SUCCESS":"0","MSG":"ERROR OCCURED-SQL prepare failed (SQLSTT=42601) - SQL0104 - Token [ was not valid. Valid tokens: ( + - ? : DAY INF NAN RID ROW RRN C."}

I also get a error logged in the error monitor:
Program In Error VVSRVPGM
Module In Error VVOUT
Procedure In Error VVOUT_EXECSQLTOJSON
Message ID VV00007
Statement Number N/A
Message Text The value of the statement is: SELECT XH_SOLD, XH_ITEMC, XH_ITEMCUM, XH_ACTUM, XH_PRSTDT,
XH_PRENDT, XH_PRDESC, XH_POPF, XH_PRLOW, XH_PRHI, XH_CUSTPR FROM
RSL/EDPCIXRHD order by upper([{"propert)
Valence User RSL
IBM i User RSL
Timestamp 2013-06-21-11.07.25.185000
Job Number 924875
Calling Program EDR0400
Call Stack VVCALL[VVCALL]-->EDR0400[EDR0400]-->EDR0400[SENDBACKRECORDS]-->VVSRVPGM{VVOUT}
[VVOUT_EXECSQLTOJSON]-->VVSRVPGM{VVOUT}[EXECSQLTOJSONINSENSITIVE]


I added the following code in my store for grouping:

remoteSort: true,
sorters: [{
property: 'XH_SOLD',
direction: 'ASC'
}],
groupField: 'XH_SOLD',
fields : [ 'XH_SOLD', 'XH_ITEMC', 'XH_ITEMCUM', 'XH_ACTUM', 'XH_PRSTDT', 'XH_PRENDT', 'XH_PRDESC', 'XH_CUSTPR',
'XH_PRLOW', 'XH_PRHI','XH_POPF' ]

I added the following code in my View for grouping:
features: [ {
ftype:'grouping',
groupHeaderTpl : '{[values.groupValue?"Not in":"In"]} Group'
} ],


Without Grouping everything works fine. Any ideas?

[robert.swanson]

On the RPG side, are you using vvOut.applySorters='1' or are you constructing your SQL statement with an "order by" clause in it?

[ScottLamb]

I'm using vvOut.ApplySorters='1'

[robert.swanson]

Which version of Valence are you using?

I'm thrown off a bit by the "upper" that appears to be going into your order by clause. It might be helpful if you could attach a screenshot of the query string parameters being sent to VVCALL (i.e., from the "Network" tab of the console).

[ScottLamb]

Version 3.2

vvcall query string parameters.jpg

[robert.swanson]

That should be working then. If you add the same grouping concept to the example grid app (Page by SQL, method 2) it works fine --- the query string parms look essentially the same.

Are you sure you're not putting some sort of "order by upper(" text in your SQL statement? To my knowledge there's nothing in VVOUT that would do that.

[ScottLamb]

The RPG was generated by Autocode and did have the upper code in it.
I commented out the following code and grouping is now working. Thanks!

if post_SortField<>*blanks;
post_SortDir=vvIn_char('dir');
stmt+=' order by upper('+%trim(post_SortField)+') '+
%trim(post_SortDir);
else;
stmt+=' ORDER BY UPPER(XH_SOLD) ASC';
endif;