Question

SQL grouping issue

  • 10 March 2018
  • 2 replies
  • 3 views
kartemann

I'm attempting the below query in an SQL Creator. I get a "malformed statement" error. How should I format this to run? It works outside of FME... Thanks! 

 

SELECT
field1,
field2, field3  FROM table1 INNER JOIN table2 ON table1.ID = table2.ID  GROUP BY field1,field2

2 replies

austinh
Rank
Badge

try calling out your fields with the table name Such as “table2.field1” throughout your statement.

takashi
Rank
Userlevel 2
Badge +17

Hi @adamradel, the SQL syntax and error handlings may be different depending on database engines. I found general issues as below, but in the actual database format you are using, these may not cause any error or there could also be other issues.

  • "field3" cannot be select here since the GROUP BY clause doesn't contain it. If you intend to aggregate the records grouping by all the three columns - field1, field2, and field3, add "field3" to the GROUP BY clause. Or, if you intend to apply an aggregate function (e.g. SUM, MIN, MAX, ... etc.) to "field3", use the function correctly.
  • If one of field1, field2, and field3 is common to the two tables, the field name should be qualified by an appropriate table name to which the field belongs, as @austinh suggested.

Reply


Community Stats

28,669
Posts
109,361
Replies
36,531
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.