JDBC : key : compareOperation

[Former Member]

hi All

I am using the SQL query in the JDBC receiver side using the action SQL_QUERY.

I have to specify the attribute compareOperation for more than two keys . The key part is as follows :

Key

.....Field1

............compareOperation

....Field2

............compareOperation

And query is

Select * from table1 where Field1 = '$Field1$' and Field2 = '$Field2$'

Instead of Equal-To(=), I want to use the operationg specified in the "_compareOperation_"

Is it possible to specify the compareOperation in the SQL query ?

[justin_santhanam · Accepted solution]

Hi,

If the query becomes complicated, it's always good to write the below structure,

<StatementName6>

<anyName action=” SQL_QUERY” | “SQL_DML”>

<access>SQL-String with optional placeholder(s)</access>

<key>

<placeholder1>value1</placeholder1>

<placeholder2>value2<placeholder2>

</key>

</anyName >

</StatementName6>

In the access tag write whatever SQL query u want.

raj.

[Former Member · Accepted solution]

Hi Raj,

I am already using the Access tag to write the query. I want to know how to use the compareOperation in the SQL query.

[Former Member · Accepted solution]

I have seen the weblogs as well as the SAP documentation, But I dont see anything over there.

I am using multiple tables in the SQL query so I cant use the Table parameter.

[justin_santhanam · Accepted solution]

Sorry for the wrong interpretation. Could you tell us the exact reqmt? Wht comparison do u want to do between the two fields?

grater than, lesser than?? is it?

raj.

[Former Member · Accepted solution]

Thanks Raj for your response. In the final requirement I have four key fields and each one of them could have any of six values, based on the user selection :

LT, EQ, LTEQ, GT, LIKE, NEQ.

I am joining multiple tables to fetch the data , so I am using SQL query to fetch the data using the UDF. I am able to access the key values in the select query; but I dont know how to use the ComparisionOperation in the UDF access SQL query

[justin_santhanam · Accepted solution]

Hello,

You can write like the below query isn't it?

Select * from table1 where Field1 LT '$Field1$' and Field2 LT '$Field2$'

Did the above query doesn't work?

raj

[Former Member · Accepted solution]

Hi Raj,

It would work, but in my case I dont know which comparison operator is coming from the sender side.

e.g in one case I might need Field1 EQ '$Field1' and in next call user might want to find the records which satisfies Field1 NE '$Field1'.

In short I want to know how can we access the compareOperation attribute in the SQL query at runtime.

I cant hard code the operator in the UDF and I have to use the multiple table which forces me to use the SQL_QUERY.

[justin_santhanam · Accepted solution]

Hi,

Got it!

Pass the field which contains the operator to the UDF.

Operator field----->UDF--->Access

in UDF let say the parameter name is opr

return "Select * from table1 where Field1 "+ opr +" '$Field1$' and Field2 "+ opr + "'$Field2$' ";

raj.

[Former Member · Accepted solution]

You can try the option given by Raj. The other option is you can write "stored procedure" where you pass the "comparison operators" and other field values as variables and do the rest of the business logic in the stored procedure.

[Former Member · Accepted solution]

Thanks Raj

[justin_santhanam · Accepted solution]

You welcome

raj.