Parameters for ODBC Delete - Not working

bikramg · Post by **bikramg** » Thu Apr 12, 2012 5:13 pm

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

qt_ky · Post by **qt_ky** » Thu Apr 12, 2012 5:19 pm

Are you using generated SQL or custom user-defined SQL? What is the actual SQL statement being used? Does it fail every time, sometimes, only when row count is greater than zero, etc.?

bikramg · Post by **bikramg** » Thu Apr 12, 2012 8:10 pm

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

SURA · Post by **SURA** » Thu Apr 12, 2012 8:37 pm

I am not sure whether you can pass the column from the previous stage to where clause. I haven't tried before and trust it will expect param should come from outside.

This is my view.

Thanks
DS User

qt_ky · Post by **qt_ky** » Thu Apr 12, 2012 8:53 pm

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.

sri_vin · Post by **sri_vin** » Thu Apr 12, 2012 9:01 pm

If you are using a job parameter then it should say something like that

WHERE dq_ExceptionKey = #exception_key#

or if it is going to be a value from one of the table column then you can say as Eric mentioned

WHERE dq_ExceptionKey = ORCHESTRATE.del_ExceptionKey

Sri

SURA · Post by **SURA** » Thu Apr 12, 2012 9:03 pm

I am not sure how i forget.

Thanks
DS User

qt_ky · Post by **qt_ky** » Thu Apr 12, 2012 9:09 pm

Maybe it depends upon your weekend. :D

SURA · Post by **SURA** » Thu Apr 12, 2012 9:12 pm

Friday sick i trust

Thanks
DS User

bikramg · Post by **bikramg** » Tue Apr 17, 2012 4:20 pm

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.