Multi WHERE Clause in SQL?

Classic Servoy
1

Having been turored on the syntax for the WHERE Clause ‘sucessfully’ (thanks Guys :wink: ) I find I need to write SQL to join several tables. In my SQL tools (Navicat) its easy to generate the SQL - but since it uses the JOIN predicates I cant use it in my Servoy code. Instead I’m using the WHERE Clause with AND. It seems like this SQL should return multiple rows - but in fact it returns null (no error and the variable is proven):

“SELECT A.cd_id,B.cd_id, C.area_id, C.area_code FROM component_detail A, comp_area_link_table B, areas C WHERE A.cd_id = B.cd_id AND B.areas_id = C.area_id AND C.area_code = '” + vQF_area + “'”

I think the alias settings and other criteria are correct but I’m obviously missing something. I’ve been ‘round the houses’ on this alread for a good few hours today so if anyone can spot where I’ve gone off track it would be appreciated!

2

Hi,

Where are you using this statement ? in a method ?
If so, in what context : getDatasetByQuery or ??

Can you show the code ?

Regards,

Hans

3

Hans Nieuwenhuis:
Hi,

Where are you using this statement ? in a method ?
If so, in what context : getDatasetByQuery or ??

Can you show the code ?

Regards,

Hans

Hans I’m using this with databaseManagenr.getDataSetByQuery() like this:

var sqlCD_IDs = “SELECT A.cd_id,B.cd_id, C.area_id, C.area_code FROM component_detail A, comp_area_link_table B, areas C WHERE A.cd_id = B.cd_id AND B.areas_id = C.area_id AND C.area_code = '” + vQF_area + “'”
var values = databaseManager.getDataSetByQuery(controller.getServerName(),sqlCD_IDs,null,-1)
valuesArray = values.getColumnAsArray(1)
application.output(valuesArray)
vSuccess = databaseManager.addTableFilterParam(controller.getServerName(),‘component_detail’,‘cd_id’,‘IN’,valuesArray,‘tfpArea’)

Unfortunately the resultant array is empty.

4

Hi,

try this

var args = new Array()
var sqlCD_IDs = 'SELECT A.cd_id,B.cd_id, C.area_id, C.area_code FROM component_detail A, comp_area_link_table B, areas C WHERE A.cd_id = B.cd_id AND B.areas_id = C.area_id AND C.area_code = ?'
args[0] = vQF_area
var values = databaseManager.getDataSetByQuery(controller.getServerName(),sqlCD_IDs,args,-1)
valuesArray = values.getColumnAsArray(1)
application.output(valuesArray)
vSuccess = databaseManager.addTableFilterParam(controller.getServerName(),'component_detail','cd_id','IN',valuesArray,'tfpArea')

if this does not work, try the sql statement directly on your database, substituting ? with the correct value.

Regards,

Hans

5

Hans - that returns a null array too.

I tried my SQL in the DB directly and it returns the correct number (37 records).

Seems its the way Servoy looks at this SQL string? :?

Any other suggestions Bud?

6

Hans - Appologies my friend - on further investigation :idea: it seems this variable is being set as numeric instead of text - and thats missing the majority of the string.

Not found the problem yet but its obviously not the SQL.

Thanks for your efforts. :D

7

Kahuna:
In my SQL tools (Navicat) its easy to generate the SQL - but since it uses the JOIN predicates I cant use it in my Servoy code

Kahuna,

Why can’t you use the ansi join queries?

Rob

8

rgansevles:

Kahuna:
In my SQL tools (Navicat) its easy to generate the SQL - but since it uses the JOIN predicates I cant use it in my Servoy code

Kahuna,

Why can’t you use the ansi join queries?

Rob

Rob - perhaps I can - I assumed (after reading some very old posts) that the likely cause of getting a null array here was because of the Join. Turns out that it was something else with my var that caused the problem.

However - I was originally using an ‘Inner Join’ type of syntax - as output from my Navcicate SQL Builder. Is this acceptable in Servoy too now?

9

Yes, databaseManager.getDataSetByQuery() should work those.

Rob