Bind Variable issues

jpr196 · Post by **jpr196** » Fri Mar 09, 2007 12:18 pm

Hi,

Can someone help me understand how to use the '?' in user-defined sql?
I have a job that has a lookup joining 3 tables. One of the tables in the lookup is also the source table, so I am using the '?' to qualify source against lookup. My problem is when I try to run this job, my lookup returns no rows. However, if I search for a specific value in the source (ex where emplid = '0002') my lookup returns a row. Anyone know why it would behave this way?

chulett · Post by **chulett** » Fri Mar 09, 2007 1:16 pm

The question mark stands for each column defined in the job. I much prefer the numbered Oracle parameter markers as they are more flexible and can be reused.

If you have X columns than your can only have X '?' bind variables - and the order that there are fulfilled is based on the column order in the stage. Meaning the first ? is bound to the first column, the second ? to the second column, etc.

We'd need to see your sql to give any more specific advise.

chulett · Post by **chulett** » Fri Mar 09, 2007 1:18 pm

Oh, and it can be effected by your 'Key' columns. Bind variables in the 'where' clause are sub'd in from the Key columns, 'data' columns are bound in from the non-Key columns. When applicable.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 09, 2007 2:39 pm

In a lookup situation, only Key columns are bound to the parameter markers. Therefore, if you have three ? characters in your WHERE clause, you need three columns marked as Key.

The first-named Key column (top-down in the Columns grid) substitutes for the first-occurring parameter marker, the second-named Key column substitutes for the second-occurring parameter marker, and the third-named Key column substitutes for the third-occurring parameter marker. And so on, if you have more.

The situation Craig describes relates to Insert/Update/Delete SQL, not to lookups.

chulett · Post by **chulett** » Fri Mar 09, 2007 2:52 pm

I'd kinda lumped all that into the 'where applicable' statement.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 09, 2007 4:32 pm

Guess which one of us is a trainer by profession?

chulett · Post by **chulett** » Fri Mar 09, 2007 4:45 pm

Umm... twelve?

(no guessing required)

ray.wurlod · Post by **ray.wurlod** » Fri Mar 09, 2007 5:16 pm

<Deep Thought>
FORTY TWO
</Deep Thought>

jpr196 · Post by **jpr196** » Mon Mar 12, 2007 8:01 am

Thanks for the help! The problem was with the sql statement and not the bind variables. By the way, is it possible to use the numbered bind variables in db2 user defined sql or is that an oracle uniqe capability?

ray.wurlod · Post by **ray.wurlod** » Mon Mar 12, 2007 8:05 am

It's unique to Oracle.

jpr196 · Post by **jpr196** » Fri Mar 16, 2007 11:02 am

Not sure if I should have started a new topic for this, but it is an issue related to bind variables. Does anyone know how I can compare two bind variables to each other in user-defined sql? I'm getting an error that I cannot compare ? = ? and I read that I need to use a cast statement on one of the parameters.

I tried Cast(%DateTimeIn(?) As TimeStamp) = ? but now I get the error: 'A statement contains a use of a parameter marker that is not valid'

Anyone think they can help with this? Thanks!

Update: Nevermind....Resolved this issue myself. Code needs to be %DateTimeIn(Cast(? As Date)).

chulett · Post by **chulett** » Fri Mar 16, 2007 1:27 pm

Oh good, you got a solution.

ps. If you're not sure if you should start a new topic or not - start a new topic.