Question

SQLCreator - Attributes to Expose help

S

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_PT
RAILWAYSTATIONPT
count (*) INTO RAILWAYSTATIONPT from RAILWAYSTATION_PT
COUNT(*) RAILWAYSTATIONPT

COUNT(*)
RAILWAYSTATIONPT
RAILWAYSTATION_PT
GLASSHOUSEAR

 

I've also tried the 'Populate from SQL Query' but that returns nothing at all.

 

Thanks for any help!

6 replies

erik_jan
Rank
Userlevel 2
Badge +12

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.

itay
Rank
Badge +16

Or....one SQL Statement using the FME 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

arnovananrooij
Rank
Badge +4

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;

P
Badge

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

P
Badge

 

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

S

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. 

Reply


Community Stats

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