Parameters for ODBC Delete - Not working
Moderators: chulett, rschirm, roy
Parameters for ODBC Delete - Not working
Hi,
I'm having trouble deleteing records using ODBC connector, paramterically. By that I'm meaning I'm running a statement like this - DELETE FROM TABLE1 WHERE COL1 = <PAR1> AND COL2 = <PAR2>
Here PAR1 AND PAR2 are coming from the previous transformer stage.
The error I get is PAR1 and PAR2 are invalid column names. So, I believe it is not recognising the fact that PAR1 and PAR2 is coming through from the previous stage (although in the ODBC they show up as columns).
Any ideas?
Thanks in advance.
Bikram
I'm having trouble deleteing records using ODBC connector, paramterically. By that I'm meaning I'm running a statement like this - DELETE FROM TABLE1 WHERE COL1 = <PAR1> AND COL2 = <PAR2>
Here PAR1 AND PAR2 are coming from the previous transformer stage.
The error I get is PAR1 and PAR2 are invalid column names. So, I believe it is not recognising the fact that PAR1 and PAR2 is coming through from the previous stage (although in the ODBC they show up as columns).
Any ideas?
Thanks in advance.
Bikram
Hi,
Details as follows -
Write mode - Delete
Generate SQL - No
Table Name - not specified as Generate SQL is no
Enable quoted identifiers - No
SQL ---> Delete statement
DELETE FROM dbo.DQPolicyException WHERE dq_ExceptionKey = del_ExceptionKey AND RIGHT(FurtherInformation,8) <> JobRunTime
This is giving me the error I have specified in my original post - i.e. del_ExceptionKey and JobRunTime are invalid columns.
If I replace them as ?del_ExceptionKey & ?JobRunTime then it tells me "Value has not been specified for parameter"
And finally when I try the same statement for user defined SQL it tells me "A null pointer was found where one shouldn't exist".
Hope this helps.
Thanks,
Bikram
Details as follows -
Write mode - Delete
Generate SQL - No
Table Name - not specified as Generate SQL is no
Enable quoted identifiers - No
SQL ---> Delete statement
DELETE FROM dbo.DQPolicyException WHERE dq_ExceptionKey = del_ExceptionKey AND RIGHT(FurtherInformation,8) <> JobRunTime
This is giving me the error I have specified in my original post - i.e. del_ExceptionKey and JobRunTime are invalid columns.
If I replace them as ?del_ExceptionKey & ?JobRunTime then it tells me "Value has not been specified for parameter"
And finally when I try the same statement for user defined SQL it tells me "A null pointer was found where one shouldn't exist".
Hope this helps.
Thanks,
Bikram
It should be possible with custom or generated SQL.
With custom, try prefixing your columns after the equal signs with "ORCHESTRATE." so that DataStage can know substitute the column values in, as in:
... WHERE dq_ExceptionKey = ORCHESTRATE.del_ExceptionKey AND ...
With generated, you will lose the ability to use that RIGHT() function and <> operator. But as a test, if you simplify your DELETE statement and get the generated option to work, then you should find the generated SQL in the job log and see the references to "orchestrate." Make sure both columns are marked as key columns.
With custom, try prefixing your columns after the equal signs with "ORCHESTRATE." so that DataStage can know substitute the column values in, as in:
... WHERE dq_ExceptionKey = ORCHESTRATE.del_ExceptionKey AND ...
With generated, you will lose the ability to use that RIGHT() function and <> operator. But as a test, if you simplify your DELETE statement and get the generated option to work, then you should find the generated SQL in the job log and see the references to "orchestrate." Make sure both columns are marked as key columns.
Last edited by qt_ky on Thu Apr 12, 2012 9:01 pm, edited 1 time in total.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Hey thanks guys for the advise.
I have kind of got it half working. The RIGHT() definitely doesn't work but <> seems to be OK (syntactically).
However now thwee problem I have got it is - as a parallel transaction I'm putting data in the table as well and the delete is getting rid of that well - so guessing the <> timestamp is not resolved to the extent it should have been.
I'll hunt around but in the mean time if you do have any better ideas to avhieve this then that would be really cool.
Thanks again.
I have kind of got it half working. The RIGHT() definitely doesn't work but <> seems to be OK (syntactically).
However now thwee problem I have got it is - as a parallel transaction I'm putting data in the table as well and the delete is getting rid of that well - so guessing the <> timestamp is not resolved to the extent it should have been.
I'll hunt around but in the mean time if you do have any better ideas to avhieve this then that would be really cool.
Thanks again.