"Select IN" query

Posted by bal on 03-Dec-2014 16:45

Is it possible to use a "select in" query in a object script trigger. See example below:

var arr = rbv_api.selectQuery("SELECT id FROM app_user where R5027463 IN (?)", 20000, '{!R5080297}' )

{!R5080297} returns: '5025370,5025371,5025372'

I want to use this query without the quotes around it, like: 

SELECT id FROM app_user where R5027463 IN (5025370,5025371,5025372)

in stead of:

SELECT id FROM app_user where R5027463 IN ('5025370,5025371,5025372')

Does Rollbase support this?

All Replies

Posted by pvorobie on 03-Dec-2014 21:48

Yes, IN  SQL clause is supported.

Posted by bal on 04-Dec-2014 03:24

Do you have any idea how we can embed the id's correctly in the query, without the quots around it?

Posted by Harrie Kuijper on 04-Dec-2014 05:34

Try this:

var arr = rbv_api.selectQuery("SELECT id FROM app_user where R5027463 IN ({!R5080297})", 20000);

Posted by bal on 04-Dec-2014 08:04

I've tried that earlier. {!R5080297} contains multiple values (like 5025370,5025371,502537). So Rollbase is marking that as string. You need to put the quotes around it. Im searching for an easy way to get the id's (eg with getrelatedid's API) and to put it in the selectquery. Any other idea's?

Posted by Harrie Kuijper on 04-Dec-2014 08:17

That's weird. Because it's working here:

Code:
var result = rbv_api.selectQuery("SELECT name FROM order WHERE id IN ({!R211781})",100);
rbv_api.printArr(result);

Parsed code:
001 var rbv_api = new Packages.com.rb.core.services.api.ServerSideAPI(122903, 163723);
002
003 var result = rbv_api.selectQuery("SELECT name FROM order WHERE id IN (211775,211823)",100);
004 rbv_api.printArr(result);

Debug result:
{ { OT1400008 - ordertest1,  }, { OT1400009 - ordertest2,  },  }

Posted by pvorobie on 04-Dec-2014 09:51

Please use Query debugger (separate from Formula debugger), that may be helpful.

Posted by bal on 04-Dec-2014 15:36

Harrie, thanks it worked. Very strange that the validate formula button gives an error, but in the debug window there is no error...

This thread is closed