Question

SQL works everywhere but in FME transformers where a SQL statement is accepted

J

This SQL works in Toad, plsqlDeveloper, sqlplus etc.. but not FME transformers that accept SQL statements.

SELECT permit, mon, quantity FROM isf UNPIVOT (quantity FOR mon IN (jan, feb, mar));

I tried this SQL with an Access database, and connections to a non spatial Oracle database. Connections to the data with simple SQL work just fine. Must be that "UNPIVOT" is not supported. Error message is

Provider error `(-2147217900) Syntax error in FROM clause.

Any help, or workaround is greatly appreciated

link to example SQL to try in FME:

http://www.orafaq.com/wiki/UNPIVOT

10 replies

david_r
Rank
Userlevel 4

FME doesn't parse your SQL at all, it simply passes it on to the backend database, so I don't think that's the issue here.

However, try removing the semicolon at the end of your statement.

J

Hi David, thanks for the response, I have tried it without the semi colon and have the same results. Do you know if all SQL commands, clauses, arguments etc... are accepted in FME transformers that accept SQL statements? Or, is it limited to something like a SQLLite portable library?

david_r
Rank
Userlevel 4

I'm not sure I understand where this SQL statement is used in your workspace? But it ought to be valid in either the SQLCreator or the SQLExecutor, which both just pass the SQL statements on to the backend -- there is no parsing involved in FME.

There are some restrictions, notably the InlineQuerier which uses SQLite as a backend where any Oracle-specific queries wouldn't work.

J

Trial scenarios where said SQL fails:

1. using FME Microsoft Access reader with SQL argument: Fail

2. FME SQLExecuter connected to Oracle non-spatial database with SQL argument: Fail

3. FME SQLCreator connected to Oracle non-spatial database with SQL argument: Fail

4. FME InlineQuerier in data stream: Fail

No other transformers are included in the test, just possible results sent directly to an inspector. Same error message as stated in original post for all cases occurs in the transformer with the SQL statement. To make sure it is not something else like the database connection, I try simple and more complex SQLs make sure they work, and they do. Then I try the one in this thread and it Fails.

austinh
Rank
Badge

I have had experience with the SQL Executor where some parts of an SQL statement will not run, however it will run in the native sql manager. One example is using “GO” with SQL Server. This needs to be commended out in FME. SQL languages have small differences between platforms and there may be a small portion of your script specific to Access.

J

My bad, I got the SQL working in the SQLExecuter and SQLCreator. However, it does not work in the Microsoft Access FME reader, which of course is where I need it. I am using Micro Soft Access 2013 on my machine, must be that UNPIVOT is not supported in that reader?

J

I found a workaround for UNPIVOT that may not be available when using a FME Microsoft Access reader. One caveat, the FME Microsoft Access reader requires exposing desired attributes it reads from the Access Database. In order to be able to see the data I need, I could not use an attribute name unless it already exist in the Access DB. So, I choose the name of an attribute in the DB I am not using, represented in the SQL as "ExistingAttributeNameInDB". Something to work with for now.

SELECT Permit, [Jan] AS ExistingAttributeNameInDB FROM isf

UNION ALL

SELECT Permit, [Feb] AS ExistingAttributeNameInDB FROM isf

UNION ALL

SELECT Permit, [Mar] AS ExistingAttributeNameInDB FROM isf

takashi
Rank
Userlevel 2
Badge +17

My bad, I got the SQL working in the SQLExecuter and SQLCreator. However, it does not work in the Microsoft Access FME reader, which of course is where I need it. I am using Micro Soft Access 2013 on my machine, must be that UNPIVOT is not supported in that reader?

SQL syntax may be different depending on database engine. As far as I know, the UNPIVOT function is supported by Oracle but is not supported by MS Access.

 

Therefore, if you need to execute an SQL statement on an Access database, you will have to write the statement conforming to the SQL syntax specific to Access. Or, if you want to use the UNPIVOT function, you will have to select a database engine which supports the function.

 

If you use the Oracle Non-Spatial reader, I think the SQL statement containing UNPIVOT function could work as expected.

 

david_r
Rank
Userlevel 4
SQL syntax may be different depending on database engine. As far as I know, the UNPIVOT function is supported by Oracle but is not supported by MS Access.

 

Therefore, if you need to execute an SQL statement on an Access database, you will have to write the statement conforming to the SQL syntax specific to Access. Or, if you want to use the UNPIVOT function, you will have to select a database engine which supports the function.

 

If you use the Oracle Non-Spatial reader, I think the SQL statement containing UNPIVOT function could work as expected.

 

Agree, that's most probably the answer.
david_r
Rank
Userlevel 4

Trial scenarios where said SQL fails:

1. using FME Microsoft Access reader with SQL argument: Fail

2. FME SQLExecuter connected to Oracle non-spatial database with SQL argument: Fail

3. FME SQLCreator connected to Oracle non-spatial database with SQL argument: Fail

4. FME InlineQuerier in data stream: Fail

No other transformers are included in the test, just possible results sent directly to an inspector. Same error message as stated in original post for all cases occurs in the transformer with the SQL statement. To make sure it is not something else like the database connection, I try simple and more complex SQLs make sure they work, and they do. Then I try the one in this thread and it Fails.

As mentioned elsewhere in this thread, for the UNPIVOT statement to work, the backend database has to support it, which explains why the statement fails for MS Access and the InlineQuerier (SQLite backend).

Reply


Community Stats

28,670
Posts
109,366
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.