Hi,
This should indicate that your query is not correct.
the best way to be sure it is, is to run this thru the sql editor you have.
when it will work so will your sql in the DS Job.
BTW:your sql (here at least) is missing an "and" after the first where condition!!!
***
select a.colwithvarchar, b.colwithinteger from
table a, table b
where
a.col1 = b.col1
**** !!! missing and !!!!****
a.colwithvarchar = (Select c.key
from table c,table d
where c.col1 = d.col1)
and SYSDATE between a.xx and a.yy
and SYSDATE between b.xx and b.yy
***
ORA-01306
Moderators: chulett, rschirm, roy
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
-
Paul Preston
- Participant
- Posts: 24
- Joined: Wed Apr 02, 2003 7:09 am
- Location: United Kingdom
Hi
you mention that this query is in a lookup. With no bind variables (:1,:2 etc) the same query will be executed for every row in your source data.
Lookups with user written queries usually include bind variables.
If you have a user defined query with no bind variables and you expect that the record match will be done by the link between the key columns in the transformer stage then you get Oracle error 1036. I am afraid the lookup does not work in the way that I think you are expecting it to. Try including the bind variables :1 and :2 etc in your where clause. That should work provided it is sufficient restriction for the query to return just one record.
Can the query return more than one record? Datastage only allows an OCI lookup to return one matching record. To use multiple returned records per lookup row you need to use an ODBC stage or a Universe table.
Paul.
you mention that this query is in a lookup. With no bind variables (:1,:2 etc) the same query will be executed for every row in your source data.
Lookups with user written queries usually include bind variables.
If you have a user defined query with no bind variables and you expect that the record match will be done by the link between the key columns in the transformer stage then you get Oracle error 1036. I am afraid the lookup does not work in the way that I think you are expecting it to. Try including the bind variables :1 and :2 etc in your where clause. That should work provided it is sufficient restriction for the query to return just one record.
Can the query return more than one record? Datastage only allows an OCI lookup to return one matching record. To use multiple returned records per lookup row you need to use an ODBC stage or a Universe table.
Paul.
Raul, it would probably help if you posted your complete query as used in the lookup - including the bind variables. Off the top of my head, I don't see where they would fit in your query as posted.
If you are able to 'View' the data and it looks like what you are expecting, it's probably not a problem with the sql per se, more than likely it's not properly setup from a DataStage standpoint. Make sure that the columns defined in the stage match what are being returned from the query (order and type) and that all defined columns have an associated bind variable in the query.
If you are able to 'View' the data and it looks like what you are expecting, it's probably not a problem with the sql per se, more than likely it's not properly setup from a DataStage standpoint. Make sure that the columns defined in the stage match what are being returned from the query (order and type) and that all defined columns have an associated bind variable in the query.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Raul
You have an Oracle error. You can go to http://asktom.oracle.com. This is the best Oracle site which I did and got nothing. I think you need a tochar() or todate() to make your where clause work. If you have a where a.col1 = b.col1 then a.col1 cannot be a number unless b.col1 is a number. So something in your where clause does not match.
Kim.
You have an Oracle error. You can go to http://asktom.oracle.com. This is the best Oracle site which I did and got nothing. I think you need a tochar() or todate() to make your where clause work. If you have a where a.col1 = b.col1 then a.col1 cannot be a number unless b.col1 is a number. So something in your where clause does not match.
Kim.
Mamu Kim
Re: ORA-01306
dsadm> oerr ora 1306
01306, 00000, "dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents"
// *Cause: A select was issued from v$logmnr_contents without first invoking
// the dbms_logmnr.start_logmnr() procedure.
// *Action: Invoke the dbms_logmnr.start_logmnr() procedure before issuing
// a select from the v$logmnr_contents view.
dsadm-appqdv1@haddock:/usr/users/dsadm/Ascential/DataStage>
* * *
Huh?
* * *
dsadm> oerr ora 1036
01036, 00000, "illegal variable name/number"
// *Cause: Unable to find bind context on user side
// *Action: Make sure that the variable being bound is in the sql statement.
* * *
Basically your SQL statement is incorrect. You can only really get this error with the output SQL statement, and I do not see any listed here.
Remember, in programming, a lot of bugs are due to the most obvious thing that your mind would overlook. You need to show us everything you can reasonably can -- so show us the SQL.
-T.J.
01306, 00000, "dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents"
// *Cause: A select was issued from v$logmnr_contents without first invoking
// the dbms_logmnr.start_logmnr() procedure.
// *Action: Invoke the dbms_logmnr.start_logmnr() procedure before issuing
// a select from the v$logmnr_contents view.
dsadm-appqdv1@haddock:/usr/users/dsadm/Ascential/DataStage>
* * *
Huh?
Oh. Typo on the topic.etlnbi wrote:ORA-01036: illegal variable name/number
* * *
dsadm> oerr ora 1036
01036, 00000, "illegal variable name/number"
// *Cause: Unable to find bind context on user side
// *Action: Make sure that the variable being bound is in the sql statement.
* * *
Basically your SQL statement is incorrect. You can only really get this error with the output SQL statement, and I do not see any listed here.
Remember, in programming, a lot of bugs are due to the most obvious thing that your mind would overlook. You need to show us everything you can reasonably can -- so show us the SQL.
-T.J.
Developer of DataStage Parallel Engine (Orchestrate).