DSXchange

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.

Because the Index function finds 'CAL' in 'CALA' and 'LTR' in 'LTRA'.

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

Yes, exactly. Edited my response to make that more obvious.

Thanks for the responses..

Well what i need to be done is

only when the exact matches of the strings are found ..want the decoding to be done.

so any suggestions for accomplishing that.

Thanks all.

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.

Given that this is a server job, the ideal solution would be routine containing a Locate statement.

Ah yes, Locate. Exact match without all the fuss.

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:

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' 

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.

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.

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 ..

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.