Using Servoy globals in an SQL Slect statment.

Classic Servoy
1

Can ayone help please.

I need to be able to use 2 Servoy global variables in an SQL statement. The original SQL statment is as:

var queryStatement = "SELECT * FROM companies where (select COUNT(*) FROM contacts WHERE companies.companyID = contacts.company_id) > 5  order by companies.company_name;";

I need to be able to use a global variable in place of the operand (>) and numeric (5) in above SQL statement.

I have sucessfully tested replacing the numeric(5) with a Servoy global, as:

var queryStatement = "SELECT * FROM companies where (select COUNT(*) FROM contacts WHERE companies.companyID = contacts.company_id) > '" + globals.findCompanyContactCount + "'  order by companies.company_name;";

But I can’t seem to find the correct syntax to replace the operand (>) as well as the globals.findCompanyContactCount

2

I won’t try to decipher your SQL, but just treat the whole query as a string. Don’t quote the SQL table’column references and put all the servoy fields, globals, etc that you want to replace in the string outside of the quotes. To see what I mean, try this:

var op = ">"
var queryStatement = "SELECT * FROM companies WHERE (SELECT COUNT(*) FROM contacts WHERE companies.companyID = contacts.company_id) " + op +" "+ globals.findCompanyContactCount + " ORDER BY companies.company_name;
3

Worked a treat.

Thanks very much Bob. Easy when you know how.