DynamicSQL - IN Statement Problem

, ,
1

Hi,

I’m using wm6.1 DynamicSQL adapter to create an SQL query against oracle at runtime. This SQL is in the form of a simple select with a dynamic IN values list passed at runtime.

The problem I am having is there appears to be extra chars appended at the end of the SQL created by the adapter service.

Can anyone help please? I have searched the forum and am at loss now!

pls see code below ---->

This is the dynamicSQL syntax:

select frdtai,frowdi,frowtp,frdtas,frcdec
from dd7334.f9210
where frdtai IN(${items})

This is the input:
‘AN8’,‘ITM’

This is the error output:

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service Jules:dynamicSQLLookup.
[ADA.1.316] Cannot execute the SQL statement "select frdtai,frowdi,frowtp,frdtas,frcdec
from dd7334.f9210
where frdtai IN(‘AN8’, ‘ITM’)". "
FONT=Times New Roman Fail to convert to internal representation"[/font]
Fail to convert to internal representation

--------------

Thanks, Mark

2

I see this existing old thread that might help you,
[URL=“wmusers.com”]wmusers.com

try with this input IN(‘AN8,ITM’)

HTH,
RMG

3

Hi there - thanks for the reply.

Alas I saw this post before I posted my thread.

The problem is that the dynamicSQL service appears to be adding a quote and full stop character at the end of the SQL if there are any single quotation marks in the dynamic SQL

e.g.

…where frdtai IN(‘AN8’, ‘ITM’)".

The solution mentioned by RMG does not work for me due to the quotes and that I am performing an IN select on a text field, not a numeric. The solution mentioned would look for one string in the IN clause as it is treated as onoe string with commas in it.

I would appreciate any help on this matter…

Regards,

Mark

4

Mark,

Try this Syntax

select frdtai,frowdi,frowtp,frdtas,frcdec
from dd7334.f9210
where frdtai IN ${items}

items = (‘AN8’,‘ITM’)

Sai

5

Mark,

Don’t worry about the extra “.” in the error message that you see after your query - that’s how IS tells you that there was a problem with executing the query. Also - whether you use the parentheses in the SQL or in the input parm ${items} it does not matter - in either case, the sql is well formed.

I would pay more attention to the message ‘Fail to convert to internal representation’. Check the data definition of the fields in the table and make sure that you are able to execute the same query through TOAD or SQL+.

HTH,
~Rohit