Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-6062

Salesforce: COUNT with a WHERE ... IN clause returns no value

XMLWordPrintable

    • Hide

      1. add salesforce resource adapter in standalone-teiid.xml:

                      <resource-adapter id="salesforce-34">
                    <module slot="main" id="org.jboss.teiid.resource-adapter.salesforce-34"/>
                    <connection-definitions>
                        <connection-definition class-name="org.teiid.resource.adapter.salesforce.SalesForceManagedConnectionFactory" jndi-name="java:/salesforce34" enabled="true" use-java-context="true" pool-name="sfDS34">
                           <config-property name="URL">
                              https://login.salesforce.com/services/Soap/u/34.0
                           </config-property>
                           <config-property name="username">
                              xxxxx
                           </config-property>
                           <config-property name="password">
                              xxxxx
                           </config-property>
                        </connection-definition>
                    </connection-definitions>
                </resource-adapter>

      2. add sf34 model:

          <model visible="true" name="sf34">
        <source name="sf34" translator-name="mySalesforce-34" connection-jndi-name="java:/salesforce34"/>    
    </model>
    <translator name="mySalesforce-34" type="salesforce-34">
        <property name="SupportsNativeQueries" value="true"/>
    </translator>

      3. run the following queries:

      -- Try counting with WHERE ... IN clause
-- FAILURE: no results
select count(*) from sf34.Account where id in ('0011Y00002FKq4PQAT', '0011Y00002ICumkQAD') ;;
-- Try counting with WHERE ... NOT IN clause
-- Success, returns some rows
select count(*) from sf34.Account where id not in ('0011Y00002FKq4PQAT', '0011Y00002ICumkQAD') ;;

      though we have rows with '0011Y00002FKq4PQAT' and '0011Y00002ICumkQAD' identifiers:

      select * from sf34.Account where id = '0011Y00002FKq4PQAT' ;;
select * from sf34.Account where id = '0011Y00002ICumkQAD' ;;

      A note: you can use for the workflow different indetifiers as you can not have the same ones on your test salesforce database.

      Show
      1. add salesforce resource adapter in standalone-teiid.xml: <resource-adapter id= "salesforce-34" > <module slot= "main" id= "org.jboss.teiid.resource-adapter.salesforce-34" /> <connection-definitions> <connection-definition class-name= "org.teiid.resource.adapter.salesforce.SalesForceManagedConnectionFactory" jndi-name= "java:/salesforce34" enabled= "true" use-java-context= "true" pool-name= "sfDS34" > <config-property name= "URL" > https://login.salesforce.com/services/Soap/u/34.0 </config-property> <config-property name= "username" > xxxxx </config-property> <config-property name= "password" > xxxxx </config-property> </connection-definition> </connection-definitions> </resource-adapter> 2. add sf34 model: <model visible= "true" name= "sf34" > <source name= "sf34" translator-name= "mySalesforce-34" connection-jndi-name= "java:/salesforce34" /> </model> <translator name= "mySalesforce-34" type= "salesforce-34" > <property name= "SupportsNativeQueries" value= "true" /> </translator> 3. run the following queries: -- Try counting with WHERE ... IN clause -- FAILURE: no results select count (*) from sf34.Account where id in ( '0011Y00002FKq4PQAT' , '0011Y00002ICumkQAD' ) ;; -- Try counting with WHERE ... NOT IN clause -- Success, returns some rows select count (*) from sf34.Account where id not in ( '0011Y00002FKq4PQAT' , '0011Y00002ICumkQAD' ) ;; though we have rows with '0011Y00002FKq4PQAT' and '0011Y00002ICumkQAD' identifiers: select * from sf34.Account where id = '0011Y00002FKq4PQAT' ;; select * from sf34.Account where id = '0011Y00002ICumkQAD' ;; A note: you can use for the workflow different indetifiers as you can not have the same ones on your test salesforce database.
    • Undefined

      The COUNT with a WHERE ... IN clause returns no value for Salesforce though COUNT with a WHERE ... IN works:

      -- Try counting with WHERE ... IN clause
-- FAILURE: no results
select count(*) from sf34.Account where id in ('0011Y00002FKq4PQAT', '0011Y00002ICumkQAD') ;;
-- Try counting with WHERE ... NOT IN clause
-- Success, returns some rows
select count(*) from sf34.Account where id not in ('0011Y00002FKq4PQAT', '0011Y00002ICumkQAD') ;;

              rhn-engineering-shawkins Steven Hawkins
              dalex005 Dmitrii Pogorelov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: