Try giving an alias for this selection.

$g->select_command = "SELECT DISTINCT(FIELD) as f FROM TABLE";

That's what I normally do – but it was failing, so I simplified it as much as possible. Same error.

I have used this method successfully with odbc_mssql_native – but I didn't see that option in the drivers folders for this package.

Thanks for the quick response.

Mike

Try setting:

$g = new jqgrid($db);
…
$g->con->debug = 1; // changed from 0 to 1
$g->debug = 1;

This will show exact reason for why query is failing.

I have tried numerous combinations of "type" and "server" (see examples below) all with the same results – as soon as I put-in the Distinct Operator, the query fails.

Thanks again for any help you can offer.

$db_conf["type"] = "odbc";
$db_conf["type"] = "odbc_mssql";
$db_conf["type"] = "odbc_mssql_n";

$db_conf["server"] = "Driver={SQL Server}
$db_conf["server"] = "Driver={SQL Server Native Client 10.0}
$db_conf["server"] = "Driver={SQL Server Native Client 11.0}

I didn't think about turning-on error logging (I even had it on earlier to solve another issue).

The problem seems to be with the way the records are being returned with the select top (1+0) at the beginning . . . that makes the command invalid.

(odbc_mssql): select top (1+0) DISTINCT(field) as id from TABLE WHERE 1=1

Thanks for the continued support.

Mike Sheppard

Try using the main query as subquery:

$g->select_command = "SELECT * FROM ( SELECT DISTINCT(FIELD) as f FROM TABLE ) as s";

Ref: http://stackoverflow.com/a/20279145/385377

That was a good work-around – it appears to be working for my needs – I still need a bit of testing on the more complex query – but it does appear to have solved the issue. Thanks for the great tip and the very rapid and accurate support.

Mike