Build the conditional where clause

Java SDK
1

Hi I need to build dynamic Where based on oncoming webRequest using statement.

Can, someone please help?

{

seType=“Account”

deValue=“TOK15Y454”

}

Ex:

If devalue field is empty then where clause should build like this

FROM PREF_INT t

WHERE t. seType =" Account "

And if both value is exist then where clause should be built like blow

FROM PREF_INT t

WHERE t. seType =" Account " AND t. devalue=“TOK15Y454”

2

You can do that in application or SDKs and execute query.

Also you can try

If there is decvalue pass that if no value pass as JSON NULL

SELECT * 
FROM PREF_INT t
WHERE t. seType = " Account " AND  (t. devalue= $val OR $val IS NULL);
3

can not do from application side , can you please explain how i can perform using SDK s

4

or Can you please provide the example ?

5

You need to do that where you are forming the query. If you are using JAVA SDK you can form string query by concat based on the conditions and execute query

6

Yes, currently i am doing the same thing based on the received request i am building individual queries.
Ex If devalue field then building one statement and if both are exist then building another statement, but in this case i might ended up with 6 or 7 queries where so many common query statement is being replicated.

Hence need to build where clause in dynamic way which can only take the fields which is not null

7

Do you think case statement will work here ?

8

I provided query earlier you can try that.

CASE WHEN $val IS NULL THEN true ELSE t.devalue = $val END

You can try single query vs different queries based on your requirement ( single query means each time more predicates needs to evaluate even if not needed, index selection can make complex, which can reflect in performance etc)

9

Data in the repository
{

“sxonType”: “NosAccount”

“ResearchRecords”: [
{
“cat”: “DH”,
" type ": “02”,
“accNum”: “0000255255”,
“endDate”: “2019-07-01”,
},
{
“cat”: “HH”,
" type ": “01”,
“accNum”: “0000255255”,
“endDate”: “2019-07-01”,
}
],

}

Request

{
sxonType = “NosAccount “
cat = “DH”
}

I need array list where based on above example only one record should be retrieve, currently throw an exception

SELECT ARRAY token FOR token IN t. ResearchRecords
CASE
WHEN token.cat=“HH” IS NOT NULL
THEN token.category = “HH”
END as SuppAlertList
FROM PREF_INT t
WHERE t. sxonType =" NosAccount "
AND ANY v IN t. ResearchRecords s SATISFIES v.cat=“HH” END

Actual working query

SELECT ARRAY token FOR token IN t. ResearchRecords
WHEN token.cat = “HH” AND token.type=“01” END as SuppAlertList
FROM PREF_INT t
WHERE t. sxonType =" NosAccount "
AND ANY v IN t. ResearchRecords SATISFIES v.cat=“SH” END

10

You did not use proper syntax, Not sure whaat you are trying to do here

ARRAY token FOR token IN t. ResearchRecords WHEN ( condition ) END
If you want case expression use case syntax described earlier in place of condition

11

syntax error - at CASE",

query

ARRAY token FOR token IN t. ResearchRecords
CASE
WHEN token.cat=“HH” IS NOT NULL
THEN token.category = “HH” as notifiSuppAlertList END
FROM PREF_INT t
WHERE t. sxonType =“NosAccount”
AND ANY v IN t. ResearchRecords SATISFIES v.cat=“SH”
END

12

i want to build the dynamic where clause based on the received request , hence trying some option