SQLExecutor does not work when getting a list of values

In case of an 1:M carnality, you should use the DatabaseJoiner transformer instead of the SQLExecutor.

The DatabaseJoiner allows adding multiple records as a list to the feature.

Hope this helps.

@jhu_20 I think it needs to be @Value(API10)

Erik_jan,

Thanks. I checked databasejoiner transformer quickly. The problem with this transformer is that my query is based on joining two tables from the database. This transformer does not allow me to select two tables.

Mark,

I tried your suggested change and still does not work:

FROM [dbo].[MAGNOLIA_AC_PROPERTY] a, [dbo].[AC_DAILY] b

WHERE a.PROPNUM = b.PROPNUM and a.API = '@Value(API10)'

Please note that the query on the database side works correctly if I just use one API to test:

SELECT

a.PROPNUM,

LEFT(a.API,10) as API100,

a.WELL_NAME,

a.AREA AS [MGY_AREA],

a.RSV_CAT,

a.STATE,

a.COUNTY,

a.OPERATOR,

b.[D_DATE],

b.[GAS],

b.[OIL],

b.[WATER],

b.[FTP],

b.[CSG],

b.[CHK]

FROM [dbo].[MAGNOLIA_AC_PROPERTY] a, [dbo].[AC_DAILY] b

WHERE a.PROPNUM = b.PROPNUM and a.[API] = '4247731029'

Can you create a view on the database side, so the DatabaseJoiner can be joined to the view?

That would solve your issue.

Another option would be using a SQLCreator transformer to read the complete result of the SQL statement, followed by a FeatureJoiner (or FeatureMerger) transformer to join the result to the original features.

Probably not the best solution as far as performance goes, but would get the desired result.

My goal is to only query and get database records when a.[API] = '@Value(API10)' and b.[D_DATE] > '@Value(LastFlowDate)'. Essentially I need to pass this list of API and DATE values as part of the WHERE clause to extract records from the two database tables: "[dbo].[MAGNOLIA_AC_PROPERTY] a, [dbo].[AC_DAILY] b". Any help will be greatly appreciated. Thanks.

In that case the only option I see is using the DatabaseJoiner and a view in the database for the mentioned query.

As an addition (not a solution) it would be very nice to have the option to create a list in the SQLExcecutor when the result of the query is more than one record.

If you create an Idea in this forum for it, I will definitely vote for it.

If you want me to create the Idea, let me know.

Eric_jan,

I only have the READ access to the database tables and cannot create view. Thanks.

Please go ahead if this is the current limitation for the transformer. I am still not fully experienced with this transformer. Thanks.

Mark,

Any other suggestion ? Since I am passing a list of API and Date values, I expect the SQLExecutor will take each value set and query against the database tables. Is this how this transformer work ? Thanks.

Idea is added here:

https://knowledge.safe.com/idea/109094/add-list-option-to-sqlexecutor-transformer.html?

It is most likely the WHERE clause that is the problem.

Try: a.API = @Value(API10) instead of a.API = '@Value(API10)' . If WorkBench already has set this as a Type string then it might be parsing the query as instead as a literal string inside the ' ' characters, and teh expression is ending up as ''4247731029'' instead of '4247731029'

A safer way to do this is:

WHERE .... a.API = CAST(@Value(API10) AS varchar)

bwn,

Thanks for your suggestion. I tried and it still does not work. In fact, now it says @Value is not defined.

The following is Safe documented example. I tried to use the similar format and still does not work.

SQLExecutor [SQLExecutor]

SQL Statement:

SELECT * FROM fmedata2015."PostalAddress" WHERE "PostalAddress" = '@Value(Address)'

Second SQL query:

SELECT a.* , b."CivicNo"

FROM fmedata2015."PostalAddress" a, "fmedata2015"."AddressPoints" b

WHERE "a"."PostalAddress" = '@Value(Address)'

AND a."AddressId" = b."AddressId";

Hi @jhu_20,

I think you may have gotten a little further since your last post.

I'll add a few more comments,

1) Looking at the output ports of the SQLExecutor - can you connect an Inspector transformer to the output port called "Results" and see if that shows up the records?

2) Also, if you have "Enable Feature Caching" turned on, try disabling that and running again in case this is affecting your results. If you do see a difference here - this might be an issue with Feature Caching feature, please report this if that is what you find.

I hope you've been able to progress this with the help provided to you.

Do update your post if you have resolved it.