Unable to run a SQL statement

[kiran kumar]

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

[chandra.shekhar@tcs.com]

Whats the error ?

[BI-RMA]

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.

[kiran kumar]

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

[ArndW]

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 "?

[ray.wurlod]

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.