Topic
  • 7 replies
  • Latest Post - ‏2014-01-25T22:11:46Z by peachy
SystemAdmin
SystemAdmin
1250 Posts

Pinned topic SQL IN clause in Execute query task

‏2011-02-12T01:20:41Z |

I am trying to assemble a Select query with IN caluse. The number of params is dynamic.

select * from part where  part.number in (...)

Is there a way to achieve this ?

select * from part where number in (?) - results in "Unable to parse query to build input parameters" 







Updated on 2013-01-05T02:18:24Z at 2013-01-05T02:18:24Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1250 Posts

    Example

    ‏2011-02-14T19:59:17Z  
    We use following syntax for query object activity against Salesforce.
    Select Id from CustomObject__c where  contact__c in $varname
    Populate $varname from the variable that stores "number" in your case in map input activity.
  • SystemAdmin
    SystemAdmin
    1250 Posts

    Been There

    ‏2011-02-14T22:00:16Z  
    siddharthw,

    Short answer, no.
    Long answer, https://community.castiron.com/node/2349

    HTH,
    David
  • SystemAdmin
    SystemAdmin
    1250 Posts

    SQL IN clause in Execute query task (DQ LookuP)

    ‏2011-02-21T13:13:43Z  

    Look at the Data Quality Lookup activity which allows you to execute a parameterized query for instances of a recurring element.It also nicey merges the input document with query results.

  • SystemAdmin
    SystemAdmin
    1250 Posts

    Re: Example

    ‏2013-01-04T23:50:32Z  
    We use following syntax for query object activity against Salesforce.
    Select Id from CustomObject__c where  contact__c in $varname
    Populate $varname from the variable that stores "number" in your case in map input activity.
    Hi,
    I'm trying to use IN clause in SOQL query as you mentioned. But salesforce is not returning objects. Below is the query. Index__c is a string and not a number field in salesforce.
    Select Id, index__c FROM CustomObject__c where index__c in ($indexIds)

    I'm passing comma separated string for ex., '111', '112' in the variable $indexIds. Any Idea??

    Thanks
    Velu
  • SystemAdmin
    SystemAdmin
    1250 Posts

    Re: Example

    ‏2013-01-05T02:18:24Z  
    Hi,
    I'm trying to use IN clause in SOQL query as you mentioned. But salesforce is not returning objects. Below is the query. Index__c is a string and not a number field in salesforce.
    Select Id, index__c FROM CustomObject__c where index__c in ($indexIds)

    I'm passing comma separated string for ex., '111', '112' in the variable $indexIds. Any Idea??

    Thanks
    Velu
    If you remove the parentheses from the SOQL query in Studio, i.e.:

    Select Id, index__c FROM CustomObject__c where index__c in $indexIds

    ...then you should be able to map an array of strings--representing the list of index__c fields--to the indexIds input parameter (a recurring element) of the Query Objects activity.

    -Paul
  • RobertoTengan
    RobertoTengan
    8 Posts

    Re: SQL IN clause in Execute query task (DQ LookuP)

    ‏2014-01-24T17:02:35Z  

    Look at the Data Quality Lookup activity which allows you to execute a parameterized query for instances of a recurring element.It also nicey merges the input document with query results.

    Hi, I Look at this activity, but I have some doubts, does internally this activity will do a for each on recurring element one query or it has the capacity to made a Query if a IN clause, being transparent to developer?

  • peachy
    peachy
    60 Posts

    Re: SQL IN clause in Execute query task (DQ LookuP)

    ‏2014-01-25T22:11:46Z  

    Hi, I Look at this activity, but I have some doubts, does internally this activity will do a for each on recurring element one query or it has the capacity to made a Query if a IN clause, being transparent to developer?

    The activity leverages XSLT/JDBC capability. I have not seen actual database requests going over to the database. However, when the Data Quality Lookup was first introduced, it produced a quicker executing orchestration than nesting a Database Execute Query within a For Each. It is especially useful for data enrichment (adding db fields to those already in an XML document).