span8
span4
span8
span4
Hi,
I'm new to the FME world,and have a question that probably has a super obvious answer to anyone with experience with FME.SQL is rather new to me too.
I've got an SQLCreator with the following SQL Script running in it,against an Oracle Spatial Object database.
I know I need to enter something in the 'attributes to expose box',but I can't for the life of me work out what it needs to be.
FME_SQL_DELIMITER /DECLARE GLASSHOUSEAR number;BEGIN select count (*) INTO GLASSHOUSEAR from GLASSHOUSE_AR;END;/DECLARE RAILWAYSTATIONPT number;BEGIN select count (*) INTO RAILWAYSTATIONPT from RAILWAYSTATION_PT;END;/DECLARE MARINEWATERAR number;BEGIN select count (*) INTO MARINEWATERAR from MARINEWATER_AR;END;/
When I use the following in the SQL script,all I need in the 'attribute to expose' is 'count(*)' and it throws the count out as expected.
select count (*) from RAILWAYSTATION_PT;
Could someone point me in the right direction so that I can get the SQL creator to output the correct things.
I'd like it to give me the counts for all three of:
GLASSHOUSE_AR
RAILWAYSTATION_PT
MARINEWATER_AR
When I run the workbench in it's current state,it connects to the database fine,runs the SQL,and returns nothing.
Attributes to Expose that I've tried so far with no luck:
count (*) from RAILWAYSTATION_PTRAILWAYSTATIONPTcount (*) INTO RAILWAYSTATIONPT from RAILWAYSTATION_PTCOUNT(*) RAILWAYSTATIONPTCOUNT(*)RAILWAYSTATIONPTRAILWAYSTATION_PTGLASSHOUSEAR
I've also tried the 'Populate from SQL Query' but that returns nothing at all.
Thanks for any help!
Or....one SQL Statement using theFME delimiter,followed by an aggregator transformer,will result in the same.
As for your exposed attributes use the names of the attributes you declare in de SQL (e.i.GLASSHOUSE_AR,etc).
You can always try the populate from SQL button too.
Hope this helps,
Itay
Thanks all for your replies :)
In the end I went with
FME_SQL_DELIMITER ;select count (*) as GLASSHOUSEAR from GLASSHOUSE_AR;select count (*) as RAILWAYSTATIONPT from RAILWAYSTATION_PT;select count (*) as MARINEWATERAR from MARINEWATER_AR;
and then exposed GLASSHOUSEAR,RAILWAYSTATIONPT,MARINEWATERAR and it's working as expected now.
In Oracle 12.c and above we can use a dynamic function to do the same,this give a more generic solution,this way we can use pl/sql to get a result
WITH
FUNCTION get_count( i_tab IN varchar2) RETURN NUMBER IS
i_count integer ;
BEGIN
EXECUTE IMMEDIATE 'select count(*) from ' || i_tab INTO i_count ;
RETURN i_count;
END;
SELECT get_count('GLASSHOUSEAR') AS GLASSHOUSE_AR
,get_count('RAILWAYSTATIONPT') AS RAILWAYSTATION_PT
,get_count('MARINEWATERAR') AS MARINEWATER_AR
FROMdual
This SQL will give you one feature with the attributes you want
select ( select count(*) from GLASSHOUSEAR ) AS GLASSHOUSE_AR
,( select count(*) from RAILWAYSTATIONPT ) AS RAILWAYSTATION_PT
,( select count(*) from MARINEWATERAR ) AS MARINEWATER_AR
from dual
Just put all the three queries together into one using union and expose the attributes NumberOfRecords and TableName
select count (*) as NumberOfRecords,'GLASSHOUSEAR' as TableName from GLASSHOUSE_AR
union
select count (*) as NumberOfRecords,'RAILWAYSTATIONPT' as TableName from RAILWAYSTATION_PT
union
select count (*) as NumberOfRecords,'MARINEWATERAR' as TableName from MARINEWATER_AR;
Unfortunately you can not get data from a PL/SQL block this way.
The only way to do this is using 3 SQLCreator transformers with the SQL statements:
select count (*) as GLASSHOUSEAR from GLASSHOUSE_AR;
select count (*) as RAILWAYSTATIONPT from RAILWAYSTATION_PT;
select count (*) as MARINEWATERAR from MARINEWATER_AR;
Then use a FeatureMerger (merge on 1 = 1) to get all 3 in one record (1 entering as Requestor,the other tow as Supplier).
That should give you 1 feature with the 3 counts.
© 2019 亚搏在线Safe Software Inc |Legal