Job Parm in SQL statement of ODBC Connector

bobyon · Post by **bobyon** » Thu Jun 18, 2009 8:22 pm

Is there any way to use a job parm in the SQL statement of an ODBC Connector Stage.

e.g Select col_name from #$dbname#.TableName where .....

I know that sounds like it should be really simple, but I cant' figure it out.

Sometimes you feel like a nut; sometimes......

kduke · Post by **kduke** » Thu Jun 18, 2009 11:35 pm

That will work. It should not have the space or the $. If you parameter name is dbname.

Code: Select all

Select col_name from #dbname#.TableName where .....

bobyon · Post by **bobyon** » Fri Jun 19, 2009 6:53 am

Well as often happens; in trying to simplify things for purposes of an easy post I masked the real issue in my question. Let me try again with the actual details:

This query:

Code: Select all

SELECT DISTINCT SUSPECT.CONT_ID AS CONT_ID FROM #MDM_DB2DATABASE#.SUSPECT AS SUSPECT WHERE SUSPECT.CUR_SUSPECT_TP_CD=1 AND SUSPECT.CONT_ID NOT IN  (SELECT DISTINCT SUSPECT.SUSPECT_CONT_ID FROM M80QA1.SUSPECT)

works fine. However this one (notice the 2nd use of the parm in the subselect):

Code: Select all

SELECT DISTINCT SUSPECT.CONT_ID AS CONT_ID FROM #MDM_DB2DATABASE#.SUSPECT AS SUSPECT WHERE SUSPECT.CUR_SUSPECT_TP_CD=1 AND SUSPECT.CONT_ID NOT IN  (SELECT DISTINCT SUSPECT.SUSPECT_CONT_ID FROM #MDM_DB2DATABASE#.SUSPECT)

results in the following error:

Code: Select all

An exception occurred while trying to receive the response from the handler:  An exception was received from the handler:  Failed to intialize the data producer.  The select statement may be invalid.

Is the error obvious and I have just been looking at this so long I can no longer see it, or is this some bug?

chulett · Post by **chulett** » Fri Jun 19, 2009 7:02 am

I don't see anything wrong and simply using a job parameter more than once in the sql should not be an issue. However, connectors are new and this very well could be a bug. Suggest you contact your official support provider and then let us know what comes of it.

bobyon · Post by **bobyon** » Fri Jun 19, 2009 8:24 am

The more I look at this the more it looks like an intermittent issue.

I can enter a query that works just fine via view data. Then do nothing but hit view data again and it fails.

PMR is open.

bobyon · Post by **bobyon** » Fri Jun 19, 2009 7:36 pm

After considerably more "research" I clearly understand what is happening; although I have no explanation as to why; and it is repeatable.

Apparently the stage is reading the select stmt out of some sort of cache or something instead of what is displayed on the screen.
follow this sequence:
1 - enter a valid SQL select stmt, hit view data, data returns good.
2 - change dbname to parm, hit view data, data returns good.
3 - hit view data again, returns error.
4 - replace parm with original hard coded dbname, returns error.
5 - exit stage
6 - re-enter stage, view data, data returns good.
7 - enter complete garbage in to stmt, view data, data returns good
8 - view data, returns error.

Imagine how bad my head hurts after beating it against this wall for the better part of 3 days.

I'll update later with the response from Tech Support.