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

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
    ACCEPTED ANSWER

    Example

    ‏2011-02-14T19:59:17Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Example

      ‏2013-01-04T23:50:32Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Example

        ‏2013-01-05T02:18:24Z  in response to SystemAdmin
        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
  • SystemAdmin
    SystemAdmin
    1250 Posts
    ACCEPTED ANSWER

    Been There

    ‏2011-02-14T22:00:16Z  in response to SystemAdmin
    siddharthw,

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

    HTH,
    David
  • SystemAdmin
    SystemAdmin
    1250 Posts
    ACCEPTED ANSWER

    SQL IN clause in Execute query task (DQ LookuP)

    ‏2011-02-21T13:13:43Z  in response to SystemAdmin

    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.

    • RobertoTengan
      RobertoTengan
      8 Posts
      ACCEPTED ANSWER

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

      ‏2014-01-24T17:02:35Z  in response to SystemAdmin

      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
        54 Posts
        ACCEPTED ANSWER

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

        ‏2014-01-25T22:11:46Z  in response to RobertoTengan

        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).