Bind Variable issues
Moderators: chulett, rschirm, roy
Bind Variable issues
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?
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?
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)).
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)).