Skip to main content
Solved

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

  • August 15, 2016
  • 4 replies
  • 36 views
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")

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

nielsgerrits
VIP
Forum|alt.badge.img+62
  • nielsgerritsVIP
  • 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")

Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...
takashi
Celebrity
  • takashiCelebrity
  • 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")

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.

 

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
david_r
Celebrity
  • david_rCelebrity
  • 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")

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

Community Stats

32,154
Posts
122,047
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.