locked
Oracle WHERE clause in biztalk map RRS feed

  • Question

  • Need to use select statement to retrieve some information from oracle database

    Its easy if I will use one condition . But In my case I want to select some information "WHERE clause" Report number and Report Year = some values.

    for example :

    SELECT column 1,column 2,column 3 FROM Reports WHERE Report number = 1 AND Report Year =2015;


    Request schema (Report No and Report Year)

    Response Schema (column 1,column 2,column 3,column 4, ......)

    Current situation : I can use the select if i will use only one WHERE clause .

    How I can use the select statment in the bellow map with more than one WHERE clause

    Monday, August 10, 2015 5:47 AM

Answers

  • Hi Emad ,

    You can have below select statement against table with where clause .

    <Select xmlns="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee">
      <Columns>*</Columns>
      <Query>where Employee_ID=10001</Query>
    </Select>
    

    This is the sample code for SQl select statement against table and same will be applied to Oracle as well .

    Thanks

    Abhishek

    • Proposed as answer by Angie Xu Monday, August 17, 2015 2:14 AM
    • Marked as answer by Angie Xu Thursday, August 20, 2015 2:07 AM
    Tuesday, August 11, 2015 5:15 AM

All replies

  • Hi Emad,

    Please share the exact error message and also let us know if you are using stored procedure or how you are generating schema for this?

    Regards,

    Sharad

    Monday, August 10, 2015 8:22 AM
  • I will publish the orchestration as a web service , So I can use it in another application

    So the first application will send a request contain (Report NO , Report Year) and the second application will retrieve the data from oracle database .

     

    Monday, August 10, 2015 8:55 AM
  • HI,

    You created any store procedure or directly use select statement from BT Schema generate wizard?

    Regards,

    Sharad

    Monday, August 10, 2015 9:10 AM
  • We are directly using select statement from BT Schema generate wizard.

    Is there any functoid to solve this problem  to send a request contain (Report NO , Report Year) to a filter through map

    Monday, August 10, 2015 9:13 AM
  • Hi Emad,

    Can you try defining it using Store Procedure?

    Regards,

    Sharad

    Monday, August 10, 2015 10:44 AM
  • Hi Sharad

    We select from a view and want to pass two parameters for selection. How can we pass it using AND condition in the above Map?

    Emad

    Monday, August 10, 2015 11:29 AM
  • Hi Emad ,

    You can have below select statement against table with where clause .

    <Select xmlns="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee">
      <Columns>*</Columns>
      <Query>where Employee_ID=10001</Query>
    </Select>
    

    This is the sample code for SQl select statement against table and same will be applied to Oracle as well .

    Thanks

    Abhishek

    • Proposed as answer by Angie Xu Monday, August 17, 2015 2:14 AM
    • Marked as answer by Angie Xu Thursday, August 20, 2015 2:07 AM
    Tuesday, August 11, 2015 5:15 AM