Question

SQLExecutor does not work when getting a list of values

jhu_20
Rank
Badge +4

I am trying to pass a list of API and Date values from a StatisticsCalculator to a SQLExecutor to execute a joint query from a SQL Server database, and it does not work for me. The following is my query inside SQLExecutor, and I cannot get any results. Please help. Thanks in advance.

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 = 'value@(API10)'

17 replies

erik_jan
Rank
Userlevel 2
Badge +16

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.

M
Badge +2

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

jhu_20
Rank
Badge +4

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.

jhu_20
Rank
Badge +4

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'

 

erik_jan
Rank
Userlevel 2
Badge +16

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.

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

That would solve your issue.

erik_jan
Rank
Userlevel 2
Badge +16

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.

jhu_20
Rank
Badge +4

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.

erik_jan
Rank
Userlevel 2
Badge +16

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.

erik_jan
Rank
Userlevel 2
Badge +16

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.

jhu_20
Rank
Badge +4

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.

jhu_20
Rank
Badge +4

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.

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

jhu_20
Rank
Badge +4

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

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.

erik_jan
Rank
Userlevel 2
Badge +16

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

Idea is added here:

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

B
Badge +3

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)

jhu_20
Rank
Badge +4

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.

jhu_20
Rank
Badge +4

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";

steveatsafe
Rank
Badge +11

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.

SteveatSafe

Reply


Community Stats

28,710
Posts
109,487
Replies
36,559
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.