Question

SQLCreator/Executor question

  • 13 February 2015
  • 13 replies
  • 11 views
G
Badge +3
Hi again,

 

 

I use SQL cretors and executors a lot.

 

 

I have a slightly complexish sql query wich refuses to output.

 

The query is valid and works in SQL Developer as intended.

 

 

But it uses with statements wich has subselects in it as wel as grouping and aggregationfunctions.

 

 

Is this a limit to sql creator and executor i wonder.

 

 

For example the following script refuses output.

 

This will not yield output and no exposing of attributes:

 

 

  
 WITH vorming AS ( Select  vb.verblijfseenheid_id vbeenheid,                           vbs.omschrijving as sto,                           bj.bouwjaar as bouwjr                                               From    verblijfseenheid vb,                           verblijfsobjectstatus vbs,                           (SELECT   g.Gebouw_ID,                                     g.VLOEROPPERVLAK,                                     g.geometrie,                                     g.bouwjaar,                                     vg.verblijfseenheid_id,                                     vg.verblijfseenheidvolgnummer                               FROM  Gebouw g,                                     verblijfseenheid_gebouw vg                               WHERE g.bouwjaar = 2014                                                                       AND g.modification IS NULL                                   AND g.gebouw_id = vg.gebouw_id(+)                                   AND g.gebouwvolgnummer = vg.gebouwvolgnummer(+) ) bj                   Where   bj.verblijfseenheid_id = vb.verblijfseenheid_id                           AND vbs.status = vb.status_id                   ORDER BY vb.verblijfseenheid_id)                     SELECT vorming.bouwjr,vorming.vbeenheid,vorming.sto  FROM vorming;
 The inner part does output:

 

  
 Select  vb.verblijfseenheid_id vbeenheid,                           vbs.omschrijving as sto,                           bj.bouwjaar as bouwjr                                               From    verblijfseenheid vb,                           verblijfsobjectstatus vbs,                           (SELECT   g.Gebouw_ID,                                     g.VLOEROPPERVLAK,                                     g.geometrie,                                     g.bouwjaar,                                     vg.verblijfseenheid_id,                                     vg.verblijfseenheidvolgnummer                               FROM  Gebouw g,                                     verblijfseenheid_gebouw vg                               WHERE g.bouwjaar = 2014                                                                       AND g.modification IS NULL                                   AND g.gebouw_id = vg.gebouw_id(+)                                   AND g.gebouwvolgnummer = vg.gebouwvolgnummer(+) ) bj                   Where   bj.verblijfseenheid_id = vb.verblijfseenheid_id                           AND vbs.status = vb.status_id                   ORDER BY vb.verblijfseenheid_id
 

 

 

Are these transformers limited to like a single nestinglevel?

 

 

I have a much larger sql-queries wich i'd like to use. Is it possible, or am i missing something?

 

 

Tx in advance again.

 

 

13 replies

david_r
Rank
Userlevel 4
Hi,

 

 

have you tried to wrap the entire top query inside a SELECT statement? E.g.

 

 

SELECT * FROM (

 

  WITH...etc

 

)

 

 

David

 

 
G
Badge +3
Hi David,

 

 

I just tried your suggestion. It is still valid when run in SQL Developer and still yields correct result.

 

 

The SQL-Creator/executor runs, but does not output. Neither can i autoexpose the attributes. Manual adding them still yields no output.

 

I checked lot of my previous scripts, non of them go deeper then 1 nestinglevel. Might this by a limitation?

 

 

If so, then i must try restructure my script.

 

 

Tx for hlp

 

 

Gio

 

 

 
takashi
Rank
Userlevel 2
Badge +17
Hi Gio,

 

 

I succeeded to read data from MS SQL Server database through the SQLCreator/SQLExecutor with this query.

 

-----

 

with tmp as (select Field1, Field2 from MyTable)

 

select * from tmp

 

-----

 

FME 2014 SP5 and FME 2015.0

 

 

Although it's a simplified example, I don't think the fundamental structure is different from your SQL statement.

 

Perhaps there is a difference between Oracle and MS SQL Server?

 

 

Takashi
takashi
Rank
Userlevel 2
Badge +17
Oops! MS SQL Server threw an error if I added ORDER clause to the statement.

 

-----

 

with tmp as (select Field1, Field2 from MyTable order by Field1)

 

select * from tmp

 

-----

 

 

This statement is OK.

 

-----

 

with tmp as (select top(100) percent Field1, Field2 from MyTable order by Field1)

 

select * from tmp

 

----- 
takashi
Rank
Userlevel 2
Badge +17
or

 

-----

 

with tmp as (select Field1, Field2 from MyTable)

 

select * from tmp order by Field1

 

-----
G
Badge +3
Hi all,

 

I see the "add a code sample" made my sql not fit in the box.

 

 

I don't think your example is similar Takashi.

 

You don't use a subselect wthin the with clause and you dont use aliases.

 

 

The is a subselect in the select with prefix bj.

 

Also my script uses aliases, so "vorming.bouwjr" is actually "vorming.bj.g.bouwjr"

 

 

If i only use inner part as i posted then it would be "bj.g.bouwjr". Inner part works.

 

Thats why i was wondering wether fme has limitations concerning this nesting.

 

 

 
takashi
Rank
Userlevel 2
Badge +17
This statement also worked with the SQLCreator/Executor.

 

-----

 

with tmp as (select

 

    s.Field1 as newField1,

 

    t.FieldA2 as newField2,

 

    u.FieldC2 as newField3

 

    from

 

        MyTable s,

 

        MyTableA t,

 

        (select b.FieldB1, c.FieldC2 

 

            from MyTableB b, MyTableC c

 

            where c.FieldC1 = b.FieldB1) u

 

    where

 

        t.FieldA1 = s.Field1

 

        and u.FieldB1 = s.Field1)

 

select tmp.newField1, tmp.newField2, tmp.newField3

 

from tmp

 

-----

 

 

The differences from yours are:

 

(1) I'm using MS SQL Server (probably you are using Oracle).

 

(2) I didn't put ORDER clause in the WITH clause.

 

 

Can you see any other fundamental differences?

 

G
Badge +3
Yes, im a doing this in oracle. Oracle Spatial.

 

 

And apart from ordering i use analytical and listaggragegation functions.

 

 

I have flattened the query, but still SQL_creator/executor does not want to output.

 

 

Maybe it has something to do with the grouoing and partitioning functions.

 

I have differtent scripts with same structure as the one i posted, except they do not use grouping and partitoning functions.

 

 

All my sql run as intended in SQL Developer.

 

 

It starts to look as a fme limitation to me.
david_r
Rank
Userlevel 4
Hi,

 

 

I just tested an SQLCreator on Oracle using a WITH-clause and it seems to work without a hitch. Granted, my query was a lot simpler than yours.

 

 

Also, I had to skip the ; at the end of the query to avoid a nasty error message from FME. Could that be the root of the problem here?

 

 

David
G
Badge +3
Hi, no the ";" at the end is because of my copy pasting from developer, i mostly remember to remove it..;)

 

 

 

It seems to be the analytical/stringaggregation functions that prevent teh output. This requires grouping etc.

 

Though i am not very sure about that. I have none of those working trhough the sql-creator/executor.

 

 

I have flattened the query, producing same result and it still does'nt budge in fme.

 

 

Here is a sample:

 

 

  
 WITH BJ2014 as(SELECT  DISTINCT          g.bouwjaar,         g.Gebouw_ID,         g.VLOEROPPERVLAK,         vb.verblijfseenheid_id         ,LISTAGG(vbs.omschrijving,',') WITHIN GROUP (ORDER BY vb.verblijfseenheid_id ) OVER(PARTITION BY vb.verblijfseenheid_id) as vormcyclus   FROM  gebouw g,         verblijfseenheid_gebouw vg,         verblijfseenheid vb,         verblijfsobjectstatus vbs   WHERE g.bouwjaar = 2014               AND g.modification IS NULL       AND g.gebouw_id = vg.gebouw_id(+)       AND g.gebouwvolgnummer = vg.gebouwvolgnummer(+)       AND vg.verblijfseenheid_id = vb.verblijfseenheid_id       AND vbs.status = vb.status_id       GROUP BY vb.verblijfseenheid_id,vbs.omschrijving,g.bouwjaar,g.Gebouw_ID,g.VLOEROPPERVLAK) SELECT geb.geometrie,        BJ2014.VLOEROPPERVLAK,        BJ2014.vormcyclus,        BJ2014.verblijfseenheid_id,        BJ2014.bouwjaar,        BJ2014.Gebouw_ID FROM   BJ2014, Gebouw geb WHERE geb.Gebouw_ID = BJ2014.Gebouw_ID  AND geb.modification IS NULL
 

 

the geometry can't be used in the statistical/analytical queries so it has to be backreferened in the query. This runs correctly using sql-developer.

 

 

It is about "stay-unit" wich have been "shaped" and or "taken in use"  in buildings buildt in 2014. 

 

 

 

I like to use SQL-creators because it is much faster than using fme oracle readers. And i can parametrise entire sql-scripts and use attributes created in fme at runtime, wich is very cool. (though this can be tricky because everyone seems to use different flavors of regexp and sql syntaxes..)

 

 

 

I'm still fiddling with it. I may end up having to export result to file and read this file into fme (bwaah...)
david_r
Rank
Userlevel 4
How annoying...

 

 

I don't suppose you have the rights to create a (temporary) view in the database?

 

 

Or you could even use an Oracle global temporary table: http://oracle-base.com/articles/misc/temporary-tables.php

 

 

David
G
Badge +3
Hi all.

 

 

I finaly got it to work trough the SQL-Creator.

 

I had to add a alias to the selected attributes within the with statement to acces them. It was not possible to acces them otherwise. In Oracle, using SQL developer this is possible, in fme appearently not.

 

 

so following does work in fme SQL-Creator:

 

  
 WITH BJ2014 AS (SELECT  DISTINCT                          g.bouwjaar bj,                         g.Gebouw_ID gid,                         g.VLOEROPPERVLAK gop,                         vb.verblijfseenheid_id vbi                         ,LISTAGG(vbs.omschrijving,',') WITHIN GROUP (ORDER BY vb.verblijfseenheid_id ) OVER(PARTITION BY vb.verblijfseenheid_id)  vormcyclus                   FROM  gebouw g,                         verblijfseenheid_gebouw vg,                         verblijfseenheid vb,                         verblijfsobjectstatus vbs                   WHERE g.bouwjaar = 2014                                               AND g.modification IS NULL                       AND g.gebouw_id = vg.gebouw_id(+)                       AND g.gebouwvolgnummer = vg.gebouwvolgnummer(+)                       AND vg.verblijfseenheid_id = vb.verblijfseenheid_id                       AND vbs.status = vb.status_id                       GROUP BY vb.verblijfseenheid_id,vbs.omschrijving,g.bouwjaar,g.Gebouw_ID,g.VLOEROPPERVLAK) SELECT   BJ2014.vormcyclus         ,BJ2014.bj bouwjaar         ,BJ2014.gid Gebouw_id         ,BJ2014.gop Vloeroppervlak         ,BJ2014.vbi verblijfseenheid_id         ,geb.geometrie          FROM   BJ2014,gebouw geb WHERE  BJ2014.gid = Gebouw_ID  AND geb.modification IS NULL
 

 

 

 

As you can see i reinstate the orignal names in the final selection.
steveatsafe
Rank
Badge +11
Hi @gio,

 

I'll take a look at this... we would expect if it can work in SQL Developer that it should work within FME.

 

A tip on autoexpose attributes...

 

in the "Populate from SQL Query" we don't handle very complicated queries (the query here can be manipulated/changed and doesn't affect the main query)... An easy Select statement from a single table and a simple where clause is handled well...

 

So sometimes simplifying the query can help get some of the attributes back and then manually add the rest.
SteveatSafe

Reply


Community Stats

28,661
Posts
109,318
Replies
36,528
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.