Integration

Get Involved. Join the Conversation.

Topic

    Karthik Ramachandran
    Run a select clause in ATP database with 'in'...
    Topic posted August 5, 2019 by Karthik RamachandranRed Ribbon: 250+ Points, tagged Database, Integration, PaaS, REST 
    40 Views, 4 Comments
    Title:
    Run a select clause in ATP database with 'in' Operator using OIC ATP Adapter
    Summary:
    Run a select clause in ATP database with 'in' Operator using OIC ATP Adapter
    Content:

    Hi,

    RestAPI structure : 

    {

    'accountNum' : '123456'

    'product' : ['abc','def','ghi']

    }

    I can receive multiple product name in rest api, i need to concatenate the array like this ('abc','def';'ghi') and which i was able to do using XSL concat & create-delimited-string function. i can see the same when i log the same function in logger. But when i pass that variable the ATP adapter to run a query like this.

    Select accountid, accountName, productid, price from producttable where productname in #productname

    it should work similar to below query which i run in ATP db directly

    Select accountid, accountName, productid, price from producttable where productname in ('abc','def';'ghi')

    But it is not retrieving any record. same query work fine in ATP database but it doesn't work from OIC. Do OIC doesn't support 'in' operator in select statement?

    Comment

     

    • Kanchankumar Khedkar

      Hello Karthik,

      In my experience, OIC DB adapter does not support IN statement in DB adapter yet. You can raise a SR to get confirmation though. 

      As a work around, you can write a stored procedure and invoke it to get the work done. 

      Thank you. 

      Regards,

      Kanchan

    • Hemanth Lakkaraju

      Per my understanding, no it won't work! Try giving 

      Select accountid, accountName, productid, price from producttable where productname in (#p1,#p2).

    • Karthik Ramachandran

      I had tried when you type in query and validate it show as success, but it doesn't return the result at all when i use select statement with in queries.

      In one of the scenario i had use the sql procedure to do that. But it is very tough to map the sql procedure output in mapping.

      if we write select statement you can map column to element which is very straight forward. if you write a sql procedure you have to return the output in table type. And then while doing mapping we have to loop through row & then column and compare the @name with column name to get the field value. (see screenshot)

      when you map like 20 + field with everytime having for loop & if condition it is very tiresome. 

      oic5.png (9KB)
    • Kanchankumar Khedkar

      You can try creating the a custom type and return this custom type instead of TABLE_TYPE. 

      But as per my understanding, DB Adapter does not support IN clause.

      You may also refer to below links to see if any of this helps in OIC db adapter as well.

      https://community.oracle.com/thread/4159611

      https://blogs.perficient.com/2017/08/18/implementation-of-in-clause-using-database-adapter-in-osb/