• EN
Solved

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

  • 15 August 2016
  • 4 replies
  • 5 views
P
Badge

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

icon

Best answer by nielsgerrits 15 August 2016, 14:20

View original

4 replies

nielsgerrits
Rank
Userlevel 6
Badge +33

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
Rank
Userlevel 3
Badge +17

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
Rank
Userlevel 5

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
Badge

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

Reply


Community Stats

28,818
Posts
109,853
Replies
36,633
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.