Hi,
I am calling a SQL query which has a FUNCTION (DERIVE) in the Oralce OCI stage (Custom SQL statement). The query is running fine from command line.
-- SELECT TABLE_ID, CC_ID, CC_TEST.DERIVE(TN||T_CODE) AS CC_ID_NEW FROM VEN_DETAILS WHERE REGION IS NOT NULL AND (NVL(CC_ID,0) <> NVL(DERIVE(TN||T_CODE),0))
Can you please guide me in this.
Regards,
Kiran Kumar
Unable to run a SQL statement
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 25
- Joined: Wed May 02, 2007 1:07 am
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
Hi Kumar,
first of all: is the beginning of your line ("--") present in your job?
"--" identifies the content of the rest of the line as comment. So what you posted here is not an SQL-Statement but a commented out SQL-Statement.
It is impossible to guide you concerning the usage of the function DERIVE, since You can see by the name that it is a user defined function contained in package CC_TEST, which is unknown to me.
Still, at second guess, I would expect a function to accept one or more parameters separated by comma, but I would expect it does not allow use of concatenation marks. And "NVL(DERIVE(TN||T_CODE),0))" could be an inconsistency of datatypes (string replaced by number on NULL-input).
But since you do not say anything about the type of error-message you get, this is just educated guessing.
first of all: is the beginning of your line ("--") present in your job?
"--" identifies the content of the rest of the line as comment. So what you posted here is not an SQL-Statement but a commented out SQL-Statement.
It is impossible to guide you concerning the usage of the function DERIVE, since You can see by the name that it is a user defined function contained in package CC_TEST, which is unknown to me.
Still, at second guess, I would expect a function to accept one or more parameters separated by comma, but I would expect it does not allow use of concatenation marks. And "NVL(DERIVE(TN||T_CODE),0))" could be an inconsistency of datatypes (string replaced by number on NULL-input).
But since you do not say anything about the type of error-message you get, this is just educated guessing.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 25
- Joined: Wed May 02, 2007 1:07 am
Hi,
Thanks for the response.
The query is not commented out. I just gave two hyphens before the query.
SELECT TABLE_ID, CC_ID, CC_TEST.DERIVE(TN||T_CODE) AS CC_ID_NEW FROM VEN_DETAILS WHERE REGION IS NOT NULL AND (NVL(CC_ID,0) <> NVL(DERIVE(TN||T_CODE),0))
The error was "ORA-00904 string: invalid identifier"
Regards,
Kiran Kumar
Thanks for the response.
The query is not commented out. I just gave two hyphens before the query.
SELECT TABLE_ID, CC_ID, CC_TEST.DERIVE(TN||T_CODE) AS CC_ID_NEW FROM VEN_DETAILS WHERE REGION IS NOT NULL AND (NVL(CC_ID,0) <> NVL(DERIVE(TN||T_CODE),0))
The error was "ORA-00904 string: invalid identifier"
Regards,
Kiran Kumar
To confirm that you are in the right database in the right environment, what happens if you write a test job with the SQL "SELECT TABLE_ID FROM VEN_DETAILS "?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It's not a DataStage error - it's a database error (invalid identifier).
Try qualifying the table name with a schema/owner name. Check, too, that every one of the columns you've named belongs in that table and that the alias name you've used doesn't.
Is it legal to use expressions in the WHERE clause? Perhaps you should use the alias from the SELECT clause in the WHERE clause.
Try qualifying the table name with a schema/owner name. Check, too, that every one of the columns you've named belongs in that table and that the alias name you've used doesn't.
Is it legal to use expressions in the WHERE clause? Perhaps you should use the alias from the SELECT clause in the WHERE clause.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.