Skip to main content
Solved

how to get the value of the returned data of a SQLExecutor

P
Forum|alt.badge.img

I us a SQLEXEcutor in a workflow. The SQL statement - with an alias - is something like:

SELECT

 

string_agg("History"."History_name", ';') AS lstHistory

 

FROM

 

public."Pois_lines",

 

public."Pois_lines_history",

 

public."History"

 

WHERE

 

"Pois_lines"."Pois_lines_id" = "Pois_lines_history"."Pois_id" AND

 

"Pois_lines_history"."History_id" = "History"."History_id" AND

 

"Pois_lines"."Pois_lines_id" = @Value("Pois_lines_id")

In the last line I use a value of an attribute in the workflow.

I want to pickup the value of the returned dataset and put it in an attribute. For that purpose I defined an alias, which name I define as an exposed attribute in the SQLExecutor. However the attribute remains empty .... The SQL statement is valid so it should return results.

Am I overlooking something or doing something wrong?

Kind regards,

Pim Verver

Best answer by nielsgerrits

This is caused by the use of uppercase characters for the alias without double quotes.

This should work:

SELECT  string_agg("History"."History_name", ';') AS "lstHistory" FROM  public."Pois_lines",  public."Pois_lines_history",  public."History" WHERE  "Pois_lines"."Pois_lines_id" = "Pois_lines_history"."Pois_id" AND "Pois_lines_history"."History_id" = "History"."History_id" AND "Pois_lines"."Pois_lines_id" = @Value("Pois_lines_id")

And this too:

SELECT  string_agg("History"."History_name", ';') AS lsthistory FROM  public."Pois_lines",  public."Pois_lines_history",  public."History" WHERE  "Pois_lines"."Pois_lines_id" = "Pois_lines_history"."Pois_id" AND "Pois_lines_history"."History_id" = "History"."History_id" AND "Pois_lines"."Pois_lines_id" = @Value("Pois_lines_id")

View original
Did this help you find an answer to your question?

4 replies

nielsgerrits
VIP
Forum|alt.badge.img+52
  • nielsgerritsVIP
  • VIP
  • Best Answer
  • August 15, 2016

This is caused by the use of uppercase characters for the alias without double quotes.

This should work:

SELECT  string_agg("History"."History_name", ';') AS "lstHistory" FROM  public."Pois_lines",  public."Pois_lines_history",  public."History" WHERE  "Pois_lines"."Pois_lines_id" = "Pois_lines_history"."Pois_id" AND "Pois_lines_history"."History_id" = "History"."History_id" AND "Pois_lines"."Pois_lines_id" = @Value("Pois_lines_id")

And this too:

SELECT  string_agg("History"."History_name", ';') AS lsthistory FROM  public."Pois_lines",  public."Pois_lines_history",  public."History" WHERE  "Pois_lines"."Pois_lines_id" = "Pois_lines_history"."Pois_id" AND "Pois_lines_history"."History_id" = "History"."History_id" AND "Pois_lines"."Pois_lines_id" = @Value("Pois_lines_id")

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • August 15, 2016
nielsgerrits wrote:

This is caused by the use of uppercase characters for the alias without double quotes.

This should work:

SELECT  string_agg("History"."History_name", ';') AS "lstHistory" FROM  public."Pois_lines",  public."Pois_lines_history",  public."History" WHERE  "Pois_lines"."Pois_lines_id" = "Pois_lines_history"."Pois_id" AND "Pois_lines_history"."History_id" = "History"."History_id" AND "Pois_lines"."Pois_lines_id" = @Value("Pois_lines_id")

And this too:

SELECT  string_agg("History"."History_name", ';') AS lsthistory FROM  public."Pois_lines",  public."Pois_lines_history",  public."History" WHERE  "Pois_lines"."Pois_lines_id" = "Pois_lines_history"."Pois_id" AND "Pois_lines_history"."History_id" = "History"."History_id" AND "Pois_lines"."Pois_lines_id" = @Value("Pois_lines_id")

Agree. PostgreSQL treats field names (including aliases) in lowercase unless you surround them with double quotations, so I think the statement has created "listhistory" (not "listHistory"). Try checking the result with the Logger.

 

david_r
Evangelist
  • david_rEvangelist
  • August 15, 2016
nielsgerrits wrote:

This is caused by the use of uppercase characters for the alias without double quotes.

This should work:

SELECT  string_agg("History"."History_name", ';') AS "lstHistory" FROM  public."Pois_lines",  public."Pois_lines_history",  public."History" WHERE  "Pois_lines"."Pois_lines_id" = "Pois_lines_history"."Pois_id" AND "Pois_lines_history"."History_id" = "History"."History_id" AND "Pois_lines"."Pois_lines_id" = @Value("Pois_lines_id")

And this too:

SELECT  string_agg("History"."History_name", ';') AS lsthistory FROM  public."Pois_lines",  public."Pois_lines_history",  public."History" WHERE  "Pois_lines"."Pois_lines_id" = "Pois_lines_history"."Pois_id" AND "Pois_lines_history"."History_id" = "History"."History_id" AND "Pois_lines"."Pois_lines_id" = @Value("Pois_lines_id")

This is correct, you will need to quote all your object names if case carries meaning. Object names can be schemas, tables, views, fields, aliases, etc.

 

 

If you do not specifically quote object names, it is up to the database how it handles it. Examples:

 

  • Oracle will assume UPPER case for all unquoted names
  • PostgreSQL will assume lower case for all unquoted names
In the case of Oracle:

 

Alias in queryAttribute name to expose in FME"lstHistory"lstHistorylstHistoryLSTHISTORY
P
Forum|alt.badge.img
  • pimverver
  • Author
  • August 15, 2016

It worked! Thank you! Somewhere in the back of my head I heard it before....

Reply


Most helpful members this week

Community Stats

30,846
Posts
116,934
Replies
38,667
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.