No SQL statement Error??
Moderators: chulett, rschirm, roy
No SQL statement Error??
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?
Code: Select all
ODBC Stage Output has no SQL statement
(Schema_Size.Schema_Sizes.DATAMGR)
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
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.
Code: Select all
select sum(bytes) from dba_segments
where owner = 'DATAMGR'
group by owner;
How are you able to have 11 input links to a Transformer stage? Can you please clearly state your job design?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
I'm still getting the compilation error.
kcbland -
This is querying a view, not sure if that helps
Again, I appreciate all your help.
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;
kcbland -
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.I don't see this in your posted SQL example
This is querying a view, not sure if that helps
Again, I appreciate all your help.
Now change the sql to look like this:
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.
Code: Select all
select owner, sum(bytes) from dba_segments
where owner = ?
group by owner;
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle