E9.2 Data Request with filter like for different values?

[alfredorz]

Hi JDEFriends, how are you?

I want to apply filter in a Data Request for a field with starts with different values, I tested with assign starts with a single value and it works fine, but I want with different values.

I present my particular case, I want to get all locations what starts by A, B, C values. It in Oracle SQL is like: ELECT * FROM F4100 WHERE LMLOCN LIKE 'A%' OR LMLOCN LIKE 'B%' OR LMLOCN LIKE 'C%' or resume with a regular expresion SELECT * FROM F4100 LIKE LMLOCN LIKE '[ABC]%', in db2 is more complex SELECT * FROM CRPDTA920P.F4100 WHERE TRIM(LMMCU) = '4800' AND REGEXP_LIKE(LMLOCN, '^[ABC]');

How can to do it in a Data Request??? It's possible in any way? I'll have concate differents DR in a orchestrator?

As workaround I'm thinking create a database view to return result (in this case the value are fixed), and create the same table in JDE and create a data request for this.

Kind regards,
Alfred.

 

[Kevin Long]

I am assuming you do not want to use Match Any, otherwise you can use Match Any and have multiple starts with filters. As a straight data request I think that would be your only option.

Other alternatives:

1. Use a database connector to execute the specific sql query you are hoping to use.
2. Use a logic extension to compile the data
3. Have a look at the data service api. It does appear you can use and/or operators to build your query, but I haven't tried it so I cannot tell you for certain it will work.

 

[alfredorz]

Thanks @Kevin Long

Yeah, I don't watch to use match any because other values it's maching fix (for example business unit in F4100 or F41021).

About point 1, I'll investigate to connect with db2 and get results from query, but I would like something standard.
Point 2: yes I thougt LEX or Groovy string to compile all data and return a json unified.
Point 3: I've investigated data service api, but i found filter (Allowed Values: [ "EQ", "NE", "STARTSWITH", "ENDSWITH", "CONTAINS", "LT", "LE", "GT", "GE" ]) and filterType(Allowed Values: [ "MATCH_ALL", "MATCH_ANY" ]) but I don't find filters with And, Or, etc. (match case only) and complex with parts fixed (parentesis or similar) ( for example
curl -X GET -H "Authorization: Basic SkRFOkpERQ==" "http://ais_server_url/jderest/v2/dataservice/table/F0101?$field=F0101.AN8&%24field=F0101.UPMJ&%24filter=F0101.UPMJ%20GT%2004032019&%24filter=F0101.AN8%20EQ%206001") https://docs.oracle.com/en/applicat...pi/op-v2-dataservice-table-tablename-get.html

Thanks.

 

[Kevin Long]

Thanks @Kevin Long

Yeah, I don't watch to use match any because other values it's maching fix (for example business unit in F4100 or F41021).

About point 1, I'll investigate to connect with db2 and get results from query, but I would like something standard.
Point 2: yes I thougt LEX or Groovy string to compile all data and return a json unified.
Point 3: I've investigated data service api, but i found filter (Allowed Values: [ "EQ", "NE", "STARTSWITH", "ENDSWITH", "CONTAINS", "LT", "LE", "GT", "GE" ]) and filterType(Allowed Values: [ "MATCH_ALL", "MATCH_ANY" ]) but I don't find filters with And, Or, etc. (match case only) and complex with parts fixed (parentesis or similar) ( for example
curl -X GET -H "Authorization: Basic SkRFOkpERQ==" "http://ais_server_url/jderest/v2/dataservice/table/F0101?$field=F0101.AN8&%24field=F0101.UPMJ&%24filter=F0101.UPMJ%20GT%2004032019&%24filter=F0101.AN8%20EQ%206001") https://docs.oracle.com/en/applicat...pi/op-v2-dataservice-table-tablename-get.html

Thanks.

@alfredorz, What I found was under the complexQuery array. Like I said, I haven't used it yet, but it does look interesting.

 

[alfredorz]

Ok! I found! but it's very hidden XD but there aren't a example. I'll try. Thanks.