No SQL statement Error??

[jshurak]

I'm getting an erro that states one particular odbc output lacks a SQL statement, when it does actually have a sql statement. Any ideas?

[chulett]

Post the exact error.

[jshurak]

Code: Select all

ODBC Stage Output has no SQL statement
(Schema_Size.Schema_Sizes.DATAMGR)

Its a compilation error.

[chulett]

Ok, so something is making it believe that you have no sql statement in the stage for that link. Why would that be? What update action are you using and where exactly is your statement in the stage?

[jshurak]

the ODBC stage is the initial stage, it performs the extract and outputs to a transformer stage. there is no update action. There are actually 11 links from the ODBC stage to the transformer. They are all user generated queries and all generate correct results when I select 'view data'

[kcbland]

No, one must be the primary input and the other 10 links are reference links. Viewing data on the reference links does not use your SQL. It uses the column definitions and the table name to bogus a SQL statement.

The View Data on a reference link is misleading as it CAN'T use your user-defined SQL - it has no WHERE values to simulate to the query to find data. Therefore, it takes the supplied table name and column names and uses "SELECT columnnames from tablename".

It would seem that one of your links is missing its user-defined SQL statement as the compilation message indicates.

[jshurak]

Thank you all for your responses. Alas, I can assure its not missing the SQL statement. That was the first thing I checked twenty something times. The query is selecting from the dba_segments view in sys schema. The query in question is

Code: Select all

select sum(bytes) from dba_segments
where owner = 'DATAMGR'
group by owner;

Update - I removed the link in question and got the same compilation error on a different link. It looks like data stage doesn't like that query.

[kcbland]

How are you able to have 11 input links to a Transformer stage? Can you please clearly state your job design?

[jshurak]

Please note there are only 10 links as I deleted one fo them.





[/img]

[kcbland]

It's exactly what I said - 1 input link and all the rest are references. The View Data SQL on reference links is a generated query and cannot be trusted.

Your reference links must have WHERE clauses with columns marked as KEY to use in fulfilling the requirements for finding a reference row. I don't see this in your posted SQL example.

[jshurak]

my apologies and thanks. I'll give that a try

[chulett]

You'll also have to include the key fields in your select statement as well, such that you select the same fields in the same order as they are defined in the stage.

[jshurak]

thanks for all your help and I'm sure you're sick of looking at this thread by now.

I've gone into the odbc stage, included the column owner, marked it as key. Included that column in the SQL statement and in the where clause

Code: Select all

select owner, sum(bytes) from dba_segments
where owner = 'ACCT_HIST'
group by owner;

I'm still getting the compilation error.


kcbland -

I don't see this in your posted SQL example

Do you mean using the key column in the where clause or somehow marking it as key within the where clause? I'm not sure how to go about doing that last bit.

This is querying a view, not sure if that helps


Again, I appreciate all your help.

[chulett]

Now change the sql to look like this:

Code: Select all

select owner, sum(bytes) from dba_segments 
where owner = ?
group by owner;

And put 'ACCT_HIST' as the value of the Key Expression in the lookup link. This assumes you only have these two columns defined in the ODBC stage.

[kcbland]

Create another reference link, load some metadata, set SQL to Auto-Generated, set some columns as "KEY". Now go look at the generated SQL. You will see how DS is expecting to reference retrieve a row, using tokens ( the ? marks) in the ordinal order the column metadata is defined. You need to make sure your references SQL looks like this.