decode issue in convert.
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
decode issue in convert.
Hi All,
I have the following expression for input values ...CAL AND LTR the result is Y instead of N , can anybody explain why ,
am I missing something.
If (Index(Convert(",",@FM,"CALA,LTRA,OOARCAL,OOARLTR,OOAY"), dedup_etn.CPNI_DATA_STRING ,1 ) <> 0 ) Then 'Y' Else If (Index(Convert(",",@FM,"CAL,CALF,GEN,LTR,LTRF,MCH,MCHA,MCHF,MCHT,OOADCAL,OOADLTR"), dedup_etn.CPNI_DATA_STRING , 1) <> 0) Then 'N' Else 'D'
Thanks you to you all in advance.
I have the following expression for input values ...CAL AND LTR the result is Y instead of N , can anybody explain why ,
am I missing something.
If (Index(Convert(",",@FM,"CALA,LTRA,OOARCAL,OOARLTR,OOAY"), dedup_etn.CPNI_DATA_STRING ,1 ) <> 0 ) Then 'Y' Else If (Index(Convert(",",@FM,"CAL,CALF,GEN,LTR,LTRF,MCH,MCHA,MCHF,MCHT,OOADCAL,OOADLTR"), dedup_etn.CPNI_DATA_STRING , 1) <> 0) Then 'N' Else 'D'
Thanks you to you all in advance.
Because the Index function finds 'CAL' in 'CALA' and 'LTR' in 'LTRA'.
Last edited by chulett on Fri Mar 07, 2008 3:58 pm, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 224
- Joined: Tue Sep 24, 2002 7:32 am
- Location: Denver, CO USA
I don't see it as an issue with Convert, it is Index that is giving you that answer. The convert is giving you:
"CALA@FMLTRA@FMOOARCAL@FMOOARLTR@FMOOAY"
Index is looking for the first occurance of CAL - well it is in there so you get Y.
You need to rethink the routines to use or let us know more about what you want to accomplish.
John
"CALA@FMLTRA@FMOOARCAL@FMOOARLTR@FMOOAY"
Index is looking for the first occurance of CAL - well it is in there so you get Y.
You need to rethink the routines to use or let us know more about what you want to accomplish.
John
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
Put something around each occurance in the list to check againts and then do the same for the value you need to check so they become unique, like single quotes. For example:
If it makes it more obvious what's going on, use an asterisk instead. There's probably better ways to do this but it should work. And ps, your Convert isn't really buying you anything as it doesn't need the Field Marks to function properly.
Code: Select all
If (Index(Convert(",",@FM,"'CALA','LTRA','OOARCAL','OOARLTR','OOAY'"), "'":dedup_etn.CPNI_DATA_STRING:"'",1 )) Then 'Y' Else If (Index(Convert(",",@FM,"'CAL','CALF','GEN','LTR','LTRF','MCH','MCHA','MCHF','MCHT','OOADCAL','OOADLTR'"), "'":dedup_etn.CPNI_DATA_STRING:"'", 1)) Then 'N' Else 'D'
-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:
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
chulett wrote:Put something around each occurance in the list to check againts and then do the same for the value you need to check so they become unique, like single quotes. For example:
If it makes it more obvious what's going on, use an asterisk instead. There's probably better ways to do this but it should work. And ps, your Convert isn't really buying you anything as it doesn't need the Field Marks to function properly.Code: Select all
If (Index(Convert(",",@FM,"'CALA','LTRA','OOARCAL','OOARLTR','OOAY'"), "'":dedup_etn.CPNI_DATA_STRING:"'",1 )) Then 'Y' Else If (Index(Convert(",",@FM,"'CAL','CALF','GEN','LTR','LTRF','MCH','MCHA','MCHF','MCHT','OOADCAL','OOADLTR'"), "'":dedup_etn.CPNI_DATA_STRING:"'", 1)) Then 'N' Else 'D'
I modified my expression the following way
If (Index(" 'CALA','LTRA','OOARCAL','OOARLTR','OOAY' ", "'":dedup_etn.CPNI_DATA_STRING:"'",1 )) Then 'Y' Else If (Index("'CAL','CALF','GEN','LTR','LTRF','MCH','MCHA','MCHF','MCHT','OOADCAL','OOADLTR'", "'":dedup_etn.CPNI_DATA_STRING:"'", 1)) Then 'N' Else 'D'
as I understood from your message that "convert" is not needed in there. all I want is to match the string exactly. And also I have 2 million rows coming in which would be an unneccasary over head ..
Both the above expressions worked fine.. for LTR and CAL ..
Thank you all very much for the responses.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
No, a necessary overhead. This is processing that you want to do, whether you use Convert(), Index() or Locate or any other method to accomplish it.kollurianu wrote: I want is to match the string exactly. And also I have 2 million rows coming in which would be an unneccasary over head ..
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.