Any limitations to SQL in getDataSetByQuery()?

Classic Servoy
1

LoadRecords() only accepts a specific subset of SQL.
Does getDataSetByQuery() have similar limitations?

Have some queries which work fine from 3rd party tools, but not inside Servoy. Here is one example:

(SELECT clients.client_name, sum(invoices.invoice_amount), (CASE WHEN clients.client_new_fy_id = 4  THEN 'new' ELSE 'old' END) as isnew FROM invoices, clients 
WHERE invoice_fy_id = 4 
AND invoices.invoice_client_id = clients.client_id
GROUP BY isnew,client_name ORDER BY isnew,client_name)

PostgreSQL 8, Servoy 2.2rc3, Mac OS X 10.3.8

2

It is working now. Maybe there was a return character in my query?

3

Javascript sees returns as codeline separators so, yes, if there are returns in your sql it could not work. When something doesn’t work I always recommend turning the debugger on and reading in the variables what’s really going on. Great way to find bugs in little time. If you want to split your statement accross multilines try something like:

var theSql = “select name from users "
theSql += " where category = ‘sud’”
theSql += " etc"

4

You can also use the concatenation “+” symbol to continue a line like:

var query = "SELECT first, last" +
  " FROM customers" +
  " WHERE id IN (101,102,104)" +
  " ORDER BY last, first"
5

Thank you for the advice.

The reason I ended up with returns in the queries was that I defined a database of reports with a query field. Once I have worked out a query in DBVisualiser or Aqua Data Studio I paste it into a new record in my reports database and I have a new report :slight_smile: