A quick one (hopefully) before leaving and get back later

can I use something like:

select id_field, sum(cast(qty as int)) as total from table group by if_field


I've simplified the above syntax, the point is that, my query works in the Windows SQL export tool and the same query triggers an error in ASQL and before dig into this, I'm confirming if it's only a matter of ASQL not accepting such syntax.

Thanks

I think that should work Jorge, i've not came across any that have not...yet! I do the same as you and try them first in sql management studio

btw, Sorry Jorge I missed your BBS 20th Anniversary back in June!

Wow, I missed that too! Jorge hardly seems old enough to have so many BBS years under his belt. Hopefully we can find another Pen to properly acknowledge his accomplishment at our next Conference!

Going back to the syntax problem, I will admit that I sometimes have to resort to trial-and-error to adjust queries that work in SQL Management Studio but not in ASQL. The issue is virtually always the fact that SQL Management Studio is using a more direct interface (bypassing ODBC), so is able to take advantage of SQL Server-specific syntax variations. But as long as the syntax is valid in the context of ODBC (or common SQL), it should work. (ASQL is not doing any syntax analysis, filtering, or other manipulation; your query is passed directly to the SQLExecDirect() function.

I'll try to play with it myself later today to see if I can come up with some good examples.

Ok, I tried a similar query here ...


... and it seems to work the same in SQLTEST2 as it does in SQL Management Studio. The one issue that I see is that there is a bug in the input routine for the querystring whereby it is truncating the last character. In some cases you can just add a space but that didn't seem to work in this query so I eventually just changed the querystring from s,0 to s,2048 and that problem disappeared. (Apparently I need to review where there is an issue with ordinary INPUT into dynamic strings - maybe nobody has reported it because nobody uses that?)

Anyway, I'm not sure if you were doing your query with SQLTEST2 for testing or in your real app, but if the latter, you might try it in SQLTEST2 to see the errors messages that it generates on failed queries, and/or add that logic to yours since maybe the error message will help clarify whether the issue is something simple like a typo.

Hi,
Confirmed that ASQL was not the problem, the error was SQL not accepting all columns together with the GROUP BY clause.
Everything is running now.

Again, thanks

Glad to hear that. And following up on my previous comment about a truncation bug, the issue turned out not to have anything to do with INPUT - it was strictly an old version of the libashodbc connector (1.4.109 instead of the current 1.4.113) that was causing it. Make sure you have the 1.4.113 version, and if not, pull it down from the dist/other/asql directory.

I was on the 112, the one you sent me, but besides it whould be fine, I've updated now with the 113.

Thanks