How to pass the array of Account Number for my account service to get info...

[pmthirumaran]

I would like to know how to pass the array or list account number to my service

My input parameters are

1. accountnumber in a array   like array[123,345, 678,987]

2. startdate

3. enddate

http://localhost:8080/odata4/account/account/detail?$filter= accountnumber array [1, 2, 3] and startdt xxxx between enddt xxxxx

at ebl layer

Select * from account where accountnumber =  array[123,345, 678,987]  (from input) and start date xxxx between endate xxxxx

please help me.

Thank you

[rareddy]

You have to use OR operator like

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 or accountnumber eq 2 or accountnumber eq 3

A later version of OData added "In" operator, but Teiid does not have support for it yet.

[shawkins]

The uri syntax for this could be something like:

accountnumber eq [123,456,789]

However odata does not really have the concept of an array comparison.  The json array in a uri is actually representing a collection.  And the spec does not seem to cover collection comparison.  You can see that our Olingo library does not support a direct comparison between collection values - olingo-odata4/ExpressionParser.java at 3395683b5a68629a2bf12d3ed577a82e04909c5a · apache/olingo-odata4 · GitHub

[pmthirumaran]

Hi All,

Thank you so much for your response and I have tried all possible scenarios and I cannot pass more than one accountnumeber from the url. Please refer the following and advise me.

http://localhost:8080/odata4/accountvdb/accountview/getaccountnumber(accountnumber=4742383)

above url is work

http://localhost:8080/odata4/accountvdb/accountview/getaccountnumber(accountnumber=4742292,accountnumber=4742383)

above url throws the <message>The key value 'accountnumber' is invalid.</message>

http://localhost:8080/odata4/accountvdb/accountview/getaccountnumber(accountnumber=4742292 and accountnumber=4742383)

above url throws the <message>The URI is malformed.</message>

http://localhost:8080/odata4/accountvdb/accountview/getaccountnumber(accountnumber=4742383 or accountnumber=4742292)

above url throws the <message>The URI is malformed.</message>

My query is from the view

BEGIN

SELECT * FROM account_EBL.account_ebl;

END

[shawkins]

If accountnumber is an array/collection type, it's not currently possible to use it as a parameter or directly in a filter comparison.

If accountnumber is a simple type and you just want to test for multiple values, then do what Ramesh showed and use a filter with ORs.

What you are showing in the next post appears to be specifying the accountnumber as the identifier for the entity - only one value is allowed as you show in your first url.

[pmthirumaran]

Hi Ramesh and Steven,

The following option is working fine

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 or accountnumber eq 2 or accountnumber eq 3 and Startdate eq '01-01-2019' and enddate eq '04-04-2019'

We have a column called "periodDate" this date will be passed from URL like "startdate" and "enddate" and assigened it to the "periodDate". it will be like "periodDate" >= "Startdate" between"periodDate" >= "enddate"

How to create Startdate and endate column and assing it perioddate

Thank you

[rareddy]

It is a little confusing to me the way you explained it, but why can't you do

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 or accountnumber eq 2 or accountnumber eq 3 and perioddate gt '01-01-2019' and perioddate lt '04-04-2019'

[pmthirumaran]

Running some issues and pulling more records if add or accountnumber eq 2 and pulling unwated records for accountnumber eq 1 which has perioddate gt '02-02-2019'

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 and perioddate gt '01-01-2019' and perioddate lt '04-04-2019'

it is working fine and pulling exact number of records

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 or accountnumber eq 2 and perioddate gt '01-01-2019' and perioddate lt '02-02-2019'

it is pulling unwated records for accountnumber eq 1 which has perioddate gt '02-02-2019'

Please help me.

[pmthirumaran]

also how to >= and <= sign in the url

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 or accountnumber eq 2 and perioddate >= '01-01-2019' <= perioddate lt '02-02-2019'

[pmthirumaran]

Running some issues and pulling more records if add or accountnumber eq 2 and pulling unwated records for accountnumber eq 1 which has perioddate gt '02-02-2019'

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 and perioddate gt '01-01-2019' and perioddate lt '04-04-2019'

it is working fine and pulling exact number of records

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 or accountnumber eq 2 and perioddate gt '01-01-2019' and perioddate lt '02-02-2019'

it is pulling unwated records for accountnumber eq 1 which has perioddate gt '02-02-2019'

[rareddy]

See this for odata help OData Version 4.01. Part 2: URL Conventions

[shawkins]

> Running some issues and pulling more records if add or accountnumber eq 2 and pulling unwated records for accountnumber eq 1 which has perioddate gt '02-02-2019'

You'll need some nesting for the predicates:

http://localhost:8080/odata4/account/account/detail?$filter=(accountnumber eq 1 or accountnumber eq 2) and perioddate gt '01-01-2019' and perioddate lt '02-02-2019'

> also how to >= and <= sign in the url

Those are ge and le.

[pmthirumaran]

the following woring perfectly good

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 and perioddate ge '01-01-2019' le perioddate lt '02-02-2019'

when I add additional paramenter "or accountnumber eq 2" in my URL and my result set is messed up. How to resolve this problem

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 or accountnumber eq 2 and perioddate ge '01-01-2019' le perioddate lt '02-02-2019'

[pmthirumaran]

Running some issues and pulling more records if add or accountnumber eq 2 and pulling unwanted records for accountnumber eq 1 which has perioddate gt '02-02-2019'

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 and perioddate gt '01-01-2019' and perioddate lt '04-04-2019'

it is working fine and pulling exact number of records

http://localhost:8080/odata4/account/account/detail?$filter=accountnumber eq 1 or accountnumber eq 2 and perioddate gt '01-01-2019' and perioddate lt '02-02-2019'

it is pulling unwanted records for accountnumber eq 1 which has perioddate gt '02-02-2019'

Are there any way to pass array or collection of account numbers via url? (That is the business requirement. Please help me out)

[shawkins]

See my previous comment.