decode issue in convert.

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
kollurianu
Premium Member
Premium Member
Posts: 614
Joined: Fri Feb 06, 2004 3:59 pm

decode issue in convert.

Post by kollurianu »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
ds_developer
Premium Member
Premium Member
Posts: 224
Joined: Tue Sep 24, 2002 7:32 am
Location: Denver, CO USA

Post by ds_developer »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Yes, exactly. Edited my response to make that more obvious. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
kollurianu
Premium Member
Premium Member
Posts: 614
Joined: Fri Feb 06, 2004 3:59 pm

Post by kollurianu »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Given that this is a server job, the ideal solution would be routine containing a Locate statement.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Ah yes, Locate. Exact match without all the fuss. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
kollurianu
Premium Member
Premium Member
Posts: 614
Joined: Fri Feb 06, 2004 3:59 pm

Post by kollurianu »

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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply