Skip to main content
  • EN

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

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

 

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

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

Reply


Community Stats

30,122
Posts
114,332
Replies
37,751
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.