Where ?=? using execSQL

, ,
1

Let’s say I have the next query

select myTable1.col1,myTable2.col2
from myTable1,myTable2
where ?=?
and myTable1.ID=myTable2.ID

I like to have one query that can select data
with different search argument in the where clause.
I tried to use the ‘?=?’ in the where clause. Somehow the query does not work well.
Do you know how can I resolve this problem ?

Thanks,
Quoi

2

Hi Quoi,

The problem lies in your SQL statement,
select myTable1.col1,myTable2.col2
from myTable1,myTable2
where ?=?
and myTable1.ID=myTable2.ID

If you try to execute the statement, the first ‘?’ will be not be treated as column name . Instead it wil be treated as ‘string’. So no matter whatever you pass, it will never return you a correct result. For example you give “mytable.col2” and “456” as the value. In your view- the sQL should be
“select myTable1, myTable2 where mytable1.col2=456 and myTable1.ID =myTable2.ID”.

But thast not true, your SQL is forming as
select myTable1, myTable2 where ‘mytable1.col2’=‘456’ and myTable1.ID =myTable2.ID

Which will never be true.
I think you wanted to pass the column name as a query parameter.

3

Define a variable say newColName and populate it with the column name at run time before calling the select service.
In the select service define this variable as
…
where %newColName% = %value%.

Hope this works.
-RC

4

Hello Ramendra,

Could you please give more detail how I have to define the variable %newColName% and how can I asssign the value to the variable ?

I have tried as underneath. But it does not work.
%newColName = ?%
%value = ?%

Define a variable say newColName and populate it >with the column name at run time before calling >the select service.
In the select service define this variable as
…
where %newColName% = %value%.

Hope this works.
-RC

Thanks,
Q