Lookup Stage not matching conditions
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 49
- Joined: Fri Nov 11, 2005 10:19 am
Lookup Stage not matching conditions
I have a situation where if the source rows match the lookup rows then need to push 1 as the result ,else as 2.
In the lookup stage conditions if I use continue for both the columns "Condition Not Met" and " Lookup Failure " , it says, if the source records DO NOT MATCH the lookup records the default value will be used or sent to target.
My question is how do you set a default value for the specific row (It is a Non-nullable, Varchar row, tried to look in edit rows box)
Thanks for the ideas.
In the lookup stage conditions if I use continue for both the columns "Condition Not Met" and " Lookup Failure " , it says, if the source records DO NOT MATCH the lookup records the default value will be used or sent to target.
My question is how do you set a default value for the specific row (It is a Non-nullable, Varchar row, tried to look in edit rows box)
Thanks for the ideas.
ALISO
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Default values are not set in a Lookup Stage. There are lots of other stages where it can be done, for example a Modify stage using the handle_null() function.
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: 49
- Joined: Fri Nov 11, 2005 10:19 am
Hi Ray,
Hey finally got to see you on this website!
So I have a Oracel Stage then a lookup Stage and now do I need to add another modify stage (or a transformer) after the look up stage. In the modify stage I can set the default value if it is null , but the look up stage writes NULL so the field is as such not null !
I guess I need to use a transformer, let me know if I am on the right path !
Hey finally got to see you on this website!
So I have a Oracel Stage then a lookup Stage and now do I need to add another modify stage (or a transformer) after the look up stage. In the modify stage I can set the default value if it is null , but the look up stage writes NULL so the field is as such not null !
I guess I need to use a transformer, let me know if I am on the right path !
-
- Participant
- Posts: 49
- Joined: Fri Nov 11, 2005 10:19 am
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
On lookup failure, the default value for an non nullable varchar field is empty string "".
I don't remember if the modify stage has a function for converting empty strings, but a transformer will convert this to a null.
the derivation for this would be:
you will also need to set the nullability of this field to "Yes" and switch RCP off.
I don't remember if the modify stage has a function for converting empty strings, but a transformer will convert this to a null.
the derivation for this would be:
Code: Select all
if in.col="" then setNull() else in.col
you will also need to set the nullability of this field to "Yes" and switch RCP off.
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
-
- Participant
- Posts: 49
- Joined: Fri Nov 11, 2005 10:19 am
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
Hi Aliso,
Need a clarification:
What output do you need if the lookup fails. Is it "2" or the null value or the string "NULL" ?
Need a clarification:
What output do you need if the lookup fails. Is it "2" or the null value or the string "NULL" ?
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
-
- Participant
- Posts: 49
- Joined: Fri Nov 11, 2005 10:19 am
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
Ok,
In that case, undo this step i had mentioned:
in.col is the lookup reference varchar non nullable column.
In that case, undo this step i had mentioned:
In a transformer, set the derivation of the necessary output column(new or existing) to:you will also need to set the nullability of this field to "Yes" and switch RCP off.
Code: Select all
if in.col="" then 2 else 1
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
-
- Participant
- Posts: 49
- Joined: Fri Nov 11, 2005 10:19 am
Hi,
Thanks for the time, I have set the field to Yes for the "Nullable" column. Now I write the output to a copy stage and after which I have a transformer where i do the following
If In.Col='NULL' then '2' else '1'
It just drops the column !
APT_CombinedOperatorController,0: Field 'RR' from input dataset '0' is NULL. Record dropped.
Thanks for the time, I have set the field to Yes for the "Nullable" column. Now I write the output to a copy stage and after which I have a transformer where i do the following
If In.Col='NULL' then '2' else '1'
It just drops the column !
APT_CombinedOperatorController,0: Field 'RR' from input dataset '0' is NULL. Record dropped.
ALISO
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
you can set the field to non-nullable.
I'd recommended the nullable option becuse of:
I'd recommended the nullable option becuse of:
How can I get it to insert the word NULL if the lookup fails !
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>