Implementing lengthy transformation logic

[sourabhverma]

Hi All,

I am having one Parallel job with one input source,one Transformer & one target table. Inside the transformation,for one of the column I am trying to perform transformation logic which is nothing but having only hard coded test conditions. If we try to replicate this logic into datastage transformation it will be lenghty & bulky task. I am attaching logic for your reference, Is there any smart and efficient way of handling this logic. Any help is appreciated.


CASE
WHEN ( @CLASS LIKE '%FTM4%' OR @CLASS LIKE '%FTM-4%' OR @CLASS LIKE '%FTM 4%' OR @CLASS LIKE '%622 M%' OR @CLASS LIKE '%622M%' OR @CLASS LIKE '%622m%' OR @CLASS LIKE '%622 m%' OR @CLASS LIKE '%LANLink 622%' ) THEN 622
WHEN (@CLASS LIKE '%11 M%' OR @CLASS LIKE '%11 m%') THEN 11
WHEN ( @CLASS LIKE '%12 M%' OR @CLASS LIKE '%12M%' OR @CLASS LIKE '%12 m%' OR @CLASS LIKE '%12m%' ) THEN 12
WHEN (@CLASS LIKE '%13 M%' OR @CLASS LIKE '%13 m%') THEN 13
WHEN (@CLASS LIKE '%17 M%' OR @CLASS LIKE '%17 m%') THEN 17
WHEN (@CLASS LIKE '%24 M%' OR @CLASS LIKE '%24 m%') THEN 24
WHEN (@CLASS LIKE '%96 M%' OR @CLASS LIKE '%96 m%') THEN 96
WHEN (@CLASS LIKE '%150 M%' OR @CLASS LIKE '%150 m%') THEN 150
WHEN (@CLASS LIKE '%250 M%' OR @CLASS LIKE '%250 m%') THEN 250
WHEN (@CLASS LIKE '%450 M%' OR @CLASS LIKE '%450 m%') THEN 450
WHEN (@CLASS LIKE '%620 M%' OR @CLASS LIKE '%620 m%') THEN 620
WHEN (@CLASS LIKE '%365 M%' OR @CLASS LIKE '%365 m%') THEN 365
WHEN ( @CLASS LIKE '%8 M%' OR @CLASS LIKE '%8M%' OR @CLASS LIKE '%8 m%' OR @CLASS LIKE '%8m%' OR @CLASS LIKE '%4x2M%' OR @CLASS LIKE '%4x2 M%' OR @CLASS LIKE '%4*2 M%' OR @CLASS LIKE '%4*2M%') THEN 8
WHEN ( @CLASS LIKE '%32 M%' OR @CLASS LIKE '%32M%' OR @CLASS LIKE '%32 m%' OR @CLASS LIKE '%32m%' OR @CLASS LIKE '%16*2M%' OR @CLASS LIKE '%16x2 M%') THEN 32
WHEN ( @CLASS LIKE '%Fractional E3 Access Fort%' OR @CLASS LIKE '%16x2M%' OR @CLASS LIKE '%16*2M%' ) THEN 34
WHEN ( @CLASS LIKE '%2 M%' OR @CLASS LIKE '%2M%' OR @CLASS LIKE '%2 m%' OR @CLASS LIKE '%2m%' OR @CLASS LIKE '%2.0%' OR @CLASS LIKE '%2.048 M%') THEN 2
WHEN ( @CLASS LIKE '%18 M%' OR @CLASS LIKE '%18M%' OR @CLASS LIKE '%18 m%' OR @CLASS LIKE '%18m%' ) THEN 18
WHEN ( @CLASS LIKE '%8 M%' OR @CLASS LIKE '%8M%' OR @CLASS LIKE '%8 m%' OR @CLASS LIKE '%8m%' OR @CLASS LIKE '%4x2M%' OR @CLASS LIKE '%4x2 M%' OR @CLASS LIKE '%4*2 M%' OR @CLASS LIKE '%4*2M%') THEN 8
WHEN ( @CLASS LIKE 'Link 155%' OR @CLASS LIKE 'Link 155%' OR @CLASS LIKE 'link 155%' OR @CLASS LIKE '%MSH 11C%' OR @CLASS LIKE '%63x2Mbit%' OR @CLASS LIKE '%63*2Mbit%' OR @CLASS LIKE '%63 x 2Mbit%' OR @CLASS LIKE '%63*2 M%' OR @CLASS LIKE '%63x2 M%') THEN 155
WHEN ( @CLASS LIKE '%32 M%' OR @CLASS LIKE '%32M%' OR @CLASS LIKE '%32 m%' OR @CLASS LIKE '%32m%' OR @CLASS LIKE '%16*2M%' OR @CLASS LIKE '%16x2 M%') THEN 32
WHEN ( @CLASS LIKE '%Access Fort%' OR @CLASS LIKE '%16x2M%' OR @CLASS LIKE '%16*2M%' ) THEN 34
WHEN ( @CLASS LIKE '%Fibre%' ) THEN 0
WHEN ( @CLASS LIKE 'DSL 1024kbFs%' ) THEN 1
WHEN ( @CLASS LIKE 'DSL 1536kbFs%' ) THEN 1.536
WHEN ( @CLASS LIKE 'DSL 2048kbFs%' ) THEN 2
WHEN ( @CLASS LIKE 'DSL+ 512kbFs%' ) THEN 0.512
WHEN ( @CLASS LIKE 'DSL 256kbFs%' ) THEN 0.256
WHEN ( @CLASS LIKE 'Access Fort, ULL DSL, 2Mbit/s, E1 G703 NTE%' ) THEN 2
WHEN ( @CLASS LIKE '%100 M%' OR @CLASS LIKE '%100 Mbit%' OR @CLASS LIKE '%100M%' OR @CLASS LIKE '%100 m%' OR @CLASS LIKE '%100m%' OR @CLASS LIKE 'FastLink' OR @CLASS LIKE ' Fast Ethernet Link' OR @CLASS LIKE ' FastLink' OR @CLASS LIKE ' FastLink') THEN 100
WHEN ( @CLASS LIKE '%140 Mb%' OR @CLASS LIKE '%140Mb%' OR @CLASS LIKE '%140 m%b' OR @CLASS LIKE '%140mb%' ) THEN 140
WHEN ( @CLASS LIKE '%155 M%' OR @CLASS LIKE '%155M%' OR @CLASS LIKE '%155 m%' OR @CLASS LIKE '%155m%' OR @CLASS LIKE '%FTM1%' OR @CLASS LIKE '%FTM 1%' OR @CLASS LIKE '%FTM-1%' OR @CLASS LIKE '%FMA 1%' OR @CLASS LIKE '%FMA 4%'
OR @CLASS LIKE '%FMA 16%' OR @CLASS LIKE '%Link155%' OR @CLASS LIKE '%Link 155%' OR @CLASS LIKE '%Link 155%') THEN 155
WHEN ( @CLASS LIKE '%200 M%' OR @CLASS LIKE '%200M%' OR @CLASS LIKE '%200 m%' OR @CLASS LIKE '%200m%' OR @CLASS LIKE 'Link 200%') THEN 200
WHEN ( @CLASS LIKE '%270 M%' OR @CLASS LIKE '%270M%' OR @CLASS LIKE '%270 m%' OR @CLASS LIKE '%270m%' OR @CLASS LIKE 'Audio 270%' OR @CLASS LIKE 'Vision-270%' ) THEN 270
WHEN ( @CLASS LIKE '%300 M%' OR @CLASS LIKE '%300M%' OR @CLASS LIKE '%300 m%' OR @CLASS LIKE '%300m%' ) THEN 300
WHEN ( @CLASS LIKE '%800 M%' OR @CLASS LIKE '%800M%' OR @CLASS LIKE '%800 m%' OR @CLASS LIKE '%800m%' ) THEN 800
WHEN ( @CLASS LIKE '%10 M%' OR @CLASS LIKE '%10M%' OR @CLASS LIKE '%10 m%' OR @CLASS LIKE '%10m%' OR @CLASS LIKE 'Link 10%' OR @CLASS LIKE 'link 10%' OR @CLASS LIKE 'link M%' OR @CLASS LIKE 'Link'
OR @CLASS LIKE ' Internet Link' OR @CLASS LIKE 'Internet Link') THEN 10 WHEN ( @CLASS LIKE '%14 M%' OR @CLASS LIKE '%14M%' OR @CLASS LIKE '%14 m%' OR @CLASS LIKE '%14m%' ) THEN 14
WHEN ( @CLASS LIKE '%15 M%' OR @CLASS LIKE '%15M%' OR @CLASS LIKE '%15 m%' OR @CLASS LIKE '%15m%' ) THEN 15
WHEN ( @CLASS LIKE '%16 M%' OR @CLASS LIKE '%16M%' OR @CLASS LIKE '%16 m%' OR @CLASS LIKE '%16m%' ) THEN 16
WHEN ( @CLASS LIKE '%20 M%' OR @CLASS LIKE '%20M%' OR @CLASS LIKE '%20 m%' OR @CLASS LIKE '%20m%' ) THEN 20
WHEN ( @CLASS LIKE '%25 M%' OR @CLASS LIKE '%25M%' OR @CLASS LIKE '%25 m%' OR @CLASS LIKE '%25m%' ) THEN 25
WHEN ( @CLASS LIKE '%26 M%' OR @CLASS LIKE '%26M%' OR @CLASS LIKE '%26 m%' OR @CLASS LIKE '%26m%' ) THEN 26
WHEN ( @CLASS LIKE '%30 M%' OR @CLASS LIKE '%30M%' OR @CLASS LIKE '%30 m%' OR @CLASS LIKE '%30m%' ) THEN 30
WHEN ( @CLASS LIKE '%34 M%' OR @CLASS LIKE '%34M%' OR @CLASS LIKE '%34 m%' OR @CLASS LIKE '%34m%' ) THEN 34
WHEN ( @CLASS LIKE '%38 M%' OR @CLASS LIKE '%38M%' OR @CLASS LIKE '%38 m%' OR @CLASS LIKE '%38m%' ) THEN 38
WHEN ( @CLASS LIKE '%40 M%' OR @CLASS LIKE '%40M%' OR @CLASS LIKE '%40 m%' OR @CLASS LIKE '%40m%' ) THEN 40
WHEN ( @CLASS LIKE '%45 M%' OR @CLASS LIKE '%45M%' OR @CLASS LIKE '%45 m%' OR @CLASS LIKE '%45m%' OR @CLASS LIKE '%21x2M%') THEN 45 WHEN ( @CLASS LIKE '%50 M%' OR @CLASS LIKE '%50M%' OR @CLASS LIKE '%50 m%' OR @CLASS LIKE '%50m%' ) THEN 50 WHEN ( @CLASS LIKE '%55 M%' OR @CLASS LIKE '%55M%' OR @CLASS LIKE '%55 m%' OR @CLASS LIKE '%55m%' ) THEN 55
WHEN ( @CLASS LIKE '%60 M%' OR @CLASS LIKE '%60M%' OR @CLASS LIKE '%60 m%' OR @CLASS LIKE '%60m%' ) THEN 60
WHEN ( @CLASS LIKE '%64 M%' OR @CLASS LIKE '%64M%' OR @CLASS LIKE '%64 m%' OR @CLASS LIKE '%64m%' OR @CLASS LIKE '%32x2M%') THEN 64 WHEN ( @CLASS LIKE '%65 M%' OR @CLASS LIKE '%65M%' OR @CLASS LIKE '%65 m%' OR @CLASS LIKE '%65m%' ) THEN 65
WHEN ( @CLASS LIKE '%75 M%' OR @CLASS LIKE '%75M%' OR @CLASS LIKE '%75 m%' OR @CLASS LIKE '%75m%' ) THEN 75
WHEN ( @CLASS LIKE '%80 M%' OR @CLASS LIKE '%80M%' OR @CLASS LIKE '%80 m%' OR @CLASS LIKE '%80m%' ) THEN 80
WHEN ( @CLASS LIKE '%85 M%' OR @CLASS LIKE '%85M%' OR @CLASS LIKE '%85 m%' OR @CLASS LIKE '%85m%' ) THEN 85
WHEN ( @CLASS LIKE '%4 M%' OR @CLASS LIKE '%4M%' OR @CLASS LIKE '%4 m%' OR @CLASS LIKE '%4m%' OR @CLASS LIKE '%2x2M%' ) THEN 4 WHEN ( @CLASS LIKE '%0.5 M%' OR @CLASS LIKE '%0.5M%' OR @CLASS LIKE '%0.5 m%' OR @CLASS LIKE '%0.5m%' OR @CLASS LIKE '%0,5 M%') THEN 0.512 WHEN ( @CLASS LIKE '%1 M%' OR @CLASS LIKE '%1M%' OR @CLASS LIKE '%1 m%' OR @CLASS LIKE '%1m%' OR @CLASS LIKE '%1 M%' ) THEN 1 WHEN ( @CLASS LIKE '%1.5 M%' OR @CLASS LIKE '%1.5M%' OR @CLASS LIKE '%1.5 m%' OR @CLASS LIKE '%1.5m%' OR @CLASS LIKE '%1.5

M%' OR @CLASS LIKE '%1,5M%' ) THEN 1.536
WHEN ( @CLASS LIKE '%2,3 M%' OR @CLASS LIKE '%2,3M%' OR @CLASS LIKE '%2,3 m%' OR @CLASS LIKE '%2,3m%' ) THEN 2.3
WHEN ( @CLASS LIKE '%2.3 M%' OR @CLASS LIKE '%2.3M%' OR @CLASS LIKE '%2.3 m%' OR @CLASS LIKE '%2.3m%' ) THEN 2.3
WHEN ( @CLASS LIKE '%1.9 M%' OR @CLASS LIKE '%1.9M%' OR @CLASS LIKE '%1.9 m%' OR @CLASS LIKE '%1.9m%' ) THEN 1.9
WHEN ( @CLASS LIKE 'DirectDSL T2%' OR @CLASS LIKE '%1 SO%' OR @CLASS LIKE '%2 SO%' OR @CLASS LIKE '%3 SO%' OR @CLASS LIKE '%4

SO%' OR @CLASS LIKE '%5 SO%' OR @CLASS LIKE '%6 SO%' OR @CLASS LIKE '%7 SO%' OR @CLASS LIKE '%8 SO%' OR @CLASS LIKE '%2So%'

OR @CLASS LIKE 'InterVoice DSL, SO%' OR @CLASS LIKE '%8 So%' OR @CLASS LIKE '%S0 (BRD)%' OR @CLASS LIKE '%S0 (FMF)%' OR

@CLASS LIKE '%So (FMF)%' OR @CLASS LIKE '%So (FF)%' ) THEN 2
WHEN ( @CLASS LIKE '%3 M%' OR @CLASS LIKE '%3M%' OR @CLASS LIKE '%3 m%' OR @CLASS LIKE '%3m%' ) THEN 3
WHEN ( @CLASS LIKE '%5 M%' OR @CLASS LIKE '%5M%' OR @CLASS LIKE '%5 m%' OR @CLASS LIKE '%5m%' ) THEN 5
WHEN ( @CLASS LIKE '%6 M%' OR @CLASS LIKE '%6M%' OR @CLASS LIKE '%6 m%' OR @CLASS LIKE '%6m%' ) THEN 6
WHEN ( @CLASS LIKE '%7 M%' OR @CLASS LIKE '%7M%' OR @CLASS LIKE '%7 m%' OR @CLASS LIKE '%7m%' ) THEN 7
WHEN ( @CLASS LIKE '%9 M%' OR @CLASS LIKE '%9M%' OR @CLASS LIKE '%9 m%' OR @CLASS LIKE '%9m%' ) THEN 9
WHEN ( @CLASS LIKE '%PTM16%' OR @CLASS LIKE '%PTM-16%' OR @CLASS LIKE '%2.5 GB%' OR @CLASS LIKE '%2.5 Gb%' OR @CLASS LIKE

'%2.5GB%' OR @CLASS LIKE '%2,5 GB%' OR @CLASS LIKE '%2,5GB%' OR @CLASS LIKE '%2,5Gb%' OR @CLASS LIKE '%2.5Gb%' OR @CLASS LIKE

'%2,5 Gb%') THEN 2500
WHEN ( @CLASS LIKE '%10 GB%' OR @CLASS LIKE '%10GB%' OR @CLASS LIKE '%10Gb%' OR @CLASS LIKE '%10 Gb%' ) THEN 10000
WHEN ( @CLASS LIKE '%PTM32%' OR @CLASS LIKE '%PTM 32%' OR @CLASS LIKE '%PTM-32%' ) THEN 10000
WHEN ( @CLASS LIKE '%20 GB%' OR @CLASS LIKE '%20GB%' OR @CLASS LIKE '%20Gb%' ) THEN 20000
WHEN ( @CLASS LIKE '%50 GB%' OR @CLASS LIKE '%50GB%' ) THEN 50000
WHEN ( @CLASS LIKE '%1.25 GB%' OR @CLASS LIKE '%1.25GB%' OR @CLASS LIKE '%1,25 GB%' OR @CLASS LIKE '%1,25GB%' OR @CLASS LIKE

'%1,25Gb%' OR @CLASS LIKE '%1,25 Gb%') THEN 1250
WHEN ( @CLASS LIKE '%1.5 GB%' OR @CLASS LIKE '%1.5GB%' OR @CLASS LIKE '%1,5 GB%' OR @CLASS LIKE '%1,5Gb%' ) THEN 1500
WHEN ( @CLASS LIKE '%1000 M%' OR @CLASS LIKE '%1000M%' OR @CLASS LIKE '%1000 m%' OR @CLASS LIKE '%1000m%' OR @CLASS LIKE '%1

GB%' OR @CLASS LIKE '%1GB%' OR @CLASS LIKE 'Link 1000%' OR @CLASS LIKE '%Link 1000%' ) THEN 1000
WHEN ( @CLASS LIKE '%1060 M%' OR @CLASS LIKE '%1060M%' OR @CLASS LIKE '%1060 m%' OR @CLASS LIKE '%1060m%' OR @CLASS LIKE

'SANLink 1060%' OR @CLASS LIKE '%LANLink 1060%' ) THEN 1060
WHEN ( @CLASS LIKE '%2 gb%' OR @CLASS LIKE '%2gb%' OR @CLASS LIKE '%2 GB%' OR @CLASS LIKE '%2GB%' OR @CLASS LIKE '%2Gb%' )

THEN 2000
WHEN ( @CLASS LIKE '%1 Gb%' OR @CLASS LIKE '%1Gb%' ) THEN 1000
WHEN ( @CLASS LIKE '%1024 k%' OR @CLASS LIKE '%1024k%' OR @CLASS LIKE '%1024 K%' OR @CLASS LIKE '%1024K%' ) THEN 1
WHEN ( @CLASS LIKE '%1088 k%' OR @CLASS LIKE '%1088k%' OR @CLASS LIKE '%1088 K%' OR @CLASS LIKE '%1088K%' ) THEN 1.088
WHEN ( @CLASS LIKE '%1280 k%' OR @CLASS LIKE '%1280k%' OR @CLASS LIKE '%1280 K%' OR @CLASS LIKE '%1280K%' ) THEN 1.28
WHEN ( @CLASS LIKE '%DSL1280%' ) THEN 1.28
WHEN ( @CLASS LIKE '%1500 k%' OR @CLASS LIKE '%1500k%' OR @CLASS LIKE '%1500 K%' OR @CLASS LIKE '%1500K%' ) THEN 1.536
WHEN ( @CLASS LIKE '%1536 k%' OR @CLASS LIKE '%1536k%' OR @CLASS LIKE '%1536 K%' OR @CLASS LIKE '%1536K%' ) THEN 1.536
WHEN ( @CLASS LIKE '%1544 k%' OR @CLASS LIKE '%1544k%' OR @CLASS LIKE '%1544 K%' OR @CLASS LIKE '%1544K%' ) THEN 1.536
WHEN ( @CLASS LIKE '%1600 k%' OR @CLASS LIKE '%1600k%' OR @CLASS LIKE '%1600 K%' OR @CLASS LIKE '%1600K%' ) THEN 1.6
WHEN ( @CLASS LIKE '%1.6 M%' OR @CLASS LIKE '%1.6 m%') THEN 1.6
WHEN ( @CLASS LIKE '%1,6 M%' OR @CLASS LIKE '%1,6 m%') THEN 1.6
WHEN (@CLASS LIKE '%1 .6 M%' OR @CLASS LIKE '%1 .6 m%') THEN 1.6
WHEN (@CLASS LIKE '%1 ,6 M%' OR @CLASS LIKE '%1 ,6 m%') THEN 1.6
WHEN ( @CLASS LIKE '%1792 k%' OR @CLASS LIKE '%1792k%' OR @CLASS LIKE '%1792 K%' OR @CLASS LIKE '%1792K%' ) THEN 1.792
WHEN ( @CLASS LIKE '%1920 k%' OR @CLASS LIKE '%1920k%' OR @CLASS LIKE '%1920 K%' OR @CLASS LIKE '%1920K%' ) THEN 2
WHEN ( @CLASS LIKE '%1984 k%' OR @CLASS LIKE '%1984k%' OR @CLASS LIKE '%1984 K%' OR @CLASS LIKE '%1984K%' ) THEN 2
WHEN ( @CLASS LIKE '%2000 k%' OR @CLASS LIKE '%2000k%' OR @CLASS LIKE '%2000 K%' OR @CLASS LIKE '%2000K%' ) THEN 2
WHEN ( @CLASS LIKE '%2048 k%' OR @CLASS LIKE '%2048k%' OR @CLASS LIKE '%2048 K%' OR @CLASS LIKE '%2048K%' ) THEN 2
WHEN ( @CLASS LIKE '%2300 k%' OR @CLASS LIKE '%2300k%' OR @CLASS LIKE '%2300 K%' OR @CLASS LIKE '%2300K%' ) THEN 2.3
WHEN ( @CLASS LIKE '%4096 k%' OR @CLASS LIKE '%4096k%' OR @CLASS LIKE '%4096 K%' OR @CLASS LIKE '%4096K%' ) THEN 4
WHEN ( @CLASS LIKE '%9200 k%' OR @CLASS LIKE '%9200k%' OR @CLASS LIKE '%9200 K%' OR @CLASS LIKE '%9200K%' ) THEN 9.216
WHEN ( @CLASS LIKE '%960 k%' OR @CLASS LIKE '%960k%' OR @CLASS LIKE '%960 K%' OR @CLASS LIKE '%960K%' ) THEN 0.96
WHEN ( @CLASS LIKE '%832 k%' OR @CLASS LIKE '%832kb%' OR @CLASS LIKE '%832 K%' OR @CLASS LIKE '%832K%' ) THEN 0.832
WHEN ( @CLASS LIKE '%768 k%' OR @CLASS LIKE '%768k%' OR @CLASS LIKE '%768 K%' OR @CLASS LIKE '%768K%' ) THEN 0.768
WHEN ( @CLASS LIKE '%640 k%' OR @CLASS LIKE '%640k%' OR @CLASS LIKE '%640 K%' OR @CLASS LIKE '%640K%' ) THEN 0.622
WHEN ( @CLASS LIKE '%512 k%' OR @CLASS LIKE '%512k%' OR @CLASS LIKE '%512 K%' OR @CLASS LIKE '%512K%' ) THEN 0.512
WHEN ( @CLASS LIKE '%384 k%' OR @CLASS LIKE '%384k%' OR @CLASS LIKE '%384 K%' OR @CLASS LIKE '%384K%' ) THEN 0.384
WHEN ( @CLASS LIKE '%300 k%' OR @CLASS LIKE '%300k%' OR @CLASS LIKE '%300 K%' OR @CLASS LIKE '%300K%' ) THEN 0.32
WHEN ( @CLASS LIKE '%256 k%' OR @CLASS LIKE '%256k%' OR @CLASS LIKE '%256 K%' OR @CLASS LIKE '%256K%' ) THEN 0.256
WHEN ( @CLASS LIKE '%250 k%' OR @CLASS LIKE '%250k%' OR @CLASS LIKE '%250 K%' OR @CLASS LIKE '%250K%' ) THEN 0.256
WHEN ( @CLASS LIKE '%192 k%' OR @CLASS LIKE '%192k%' OR @CLASS LIKE '%192 K%' OR @CLASS LIKE '%192K%' ) THEN 0.192
WHEN ( @CLASS LIKE '%128+16 kbit/s%' OR @CLASS LIKE '%EURO ISDN-2%' OR @CLASS LIKE '%128+16 kb%' ) THEN 0.192
WHEN ( @CLASS LIKE '%180 k%' OR @CLASS LIKE '%180k%' OR @CLASS LIKE '%180 K%' OR @CLASS LIKE '%180K%' ) THEN 0.18
WHEN ( @CLASS LIKE '%150 k%' OR @CLASS LIKE '%150k%' OR @CLASS LIKE '%150 K%' OR @CLASS LIKE '%150K%' ) THEN 0.15
WHEN ( @CLASS LIKE '%144 k%' OR @CLASS LIKE '%144k%' OR @CLASS LIKE '%144 K%' OR @CLASS LIKE '%144K%' ) THEN 0.144
WHEN ( @CLASS LIKE '%128 k%' OR @CLASS LIKE '%128k%' OR @CLASS LIKE '%128 K%' OR @CLASS LIKE '%128K%' ) THEN 0.128
WHEN ( @CLASS LIKE '%19.2 K%' OR @CLASS LIKE '%19.2K%' OR @CLASS LIKE '%19.2 k%' OR @CLASS LIKE '%19.2k%' OR @CLASS LIKE
'%19.200 bit/s%') THEN 0.064
WHEN ( @CLASS LIKE '%9.6 K%' OR @CLASS LIKE '%9.6K%' OR @CLASS LIKE '%9.6 k%' OR @CLASS LIKE '%9.6k%' OR @CLASS LIKE '%9,6 k%' ) THEN 0.064
WHEN ( @CLASS LIKE '%64 k%' OR @CLASS LIKE '%64k%' OR @CLASS LIKE '%64 K%' OR @CLASS LIKE '%64K%' OR @CLASS LIKE '%MF4' OR @CLASS LIKE 'Mixed Cas%' OR @CLASS LIKE 'Mixed-CAS%' OR @CLASS LIKE 'Mixed-Voice CAS%' OR @CLASS LIKE '2-Fraht%' OR @CLASS LIKE '%Gen%' OR @CLASS LIKE '%Gen-Gen%' OR @CLASS LIKE '%Gen-F%' OR @CLASS LIKE '%Gen/Gen%' OR @CLASS LIKE ' Link,
Analogue%' OR @CLASS LIKE 'Vision, Analogue%' OR @CLASS LIKE 'Voice, analog%' OR @CLASS LIKE 'VoiceLine, analog%' OR @CLASS
LIKE 'VoiceDSL Analogue%' OR @CLASS LIKE 'VoiceDSL with Number Fortability, Analogue%' OR @CLASS LIKE 'Circuit: Analogue%' OR @CLASS LIKE 'Circuit: CAS-CAS%' OR @CLASS LIKE 'Circuit: Mixed%' ) THEN 0.064
WHEN ( @CLASS LIKE '%1 K%' OR @CLASS LIKE '%1Kb' OR @CLASS LIKE '%1 k%' OR @CLASS LIKE '%1k%' ) THEN 0.064
WHEN ( @CLASS LIKE '%19200 b%' OR @CLASS LIKE '%19200b%' OR @CLASS LIKE '%19200 B%' OR @CLASS LIKE '%19200B%' ) THEN 0.064
WHEN ( @CLASS LIKE '%1200 b%' OR @CLASS LIKE '%1200b%' OR @CLASS LIKE '%1200 B%' OR @CLASS LIKE '%1200B%' ) THEN 0.064
WHEN ( @CLASS LIKE '%1600 b%' OR @CLASS LIKE '%1600b%' OR @CLASS LIKE '%1600 B%' OR @CLASS LIKE '%1600B%' ) THEN 0.064
WHEN ( @CLASS LIKE '%4800 b%' OR @CLASS LIKE '%4800b%' OR @CLASS LIKE '%4800 B%' OR @CLASS LIKE '%4800B%' ) THEN 0.064
WHEN ( @CLASS LIKE '%9600 b%' OR @CLASS LIKE '%9600b%' OR @CLASS LIKE '%9600 B%' OR @CLASS LIKE '%9600B%' ) THEN 0.064
WHEN ( @CLASS LIKE '%256 b%' OR @CLASS LIKE '%256b%' OR @CLASS LIKE '%256 B%' OR @CLASS LIKE '%256B%' ) THEN 0.064
WHEN ( @CLASS LIKE '%E0%' OR @CLASS LIKE '%E1%' ) THEN 2
WHEN ( @CLASS LIKE '%E3%' ) THEN 34
WHEN ( @CLASS LIKE '%E4%' ) THEN 140
WHEN ( @CLASS LIKE '%E5%' OR @CLASS LIKE '%PTM-1%' OR @CLASS LIKE '%PTM 1%' OR @CLASS LIKE '%PTM1%' ) THEN 155
WHEN ( @CLASS LIKE '%E6%' OR @CLASS LIKE '%PTM-4%' OR @CLASS LIKE '%PTM 4%' OR @CLASS LIKE '%PTM4%' ) THEN 622
WHEN ( @CLASS LIKE '%E7%' OR @CLASS LIKE '%PTM-16%' OR @CLASS LIKE '%PTM 16%' OR @CLASS LIKE '%PTM16%' ) THEN 2500
WHEN ( @CLASS LIKE '%E8%' OR @CLASS LIKE '%PTM-64%' OR @CLASS LIKE '%PTM 64%' OR @CLASS LIKE '%PTM64%' ) THEN 10000
ELSE 0
END

[ray.wurlod]

There's no CASE - you will need nested IF..THEN..ELSE

As to whether there's a simpler way, probably not. There does not appear to be any pattern to the test expression values or generated values.

[chulett]

Seems like many of the checks could be simplified if you stripped spaces and up-cased the value before checking, then you could do one check rather than the four you are currently doing.

[throbinson]

I admire your notion of bulky and lengthy. Clearly the smart and efficient methodology would be to update your resume.

[Kryt0n]

Hi All,
WHEN ( @CLASS LIKE '%2 M%' OR @CLASS LIKE '%2M%' OR @CLASS LIKE '%2 m%' OR @CLASS LIKE '%2m%' OR @CLASS LIKE '%2.0%' OR @CLASS LIKE '%2.048 M%') THEN 2
...
WHEN ( @CLASS LIKE '%32 M%' OR @CLASS LIKE '%32M%' OR @CLASS LIKE '%32 m%' OR @CLASS LIKE '%32m%' OR @CLASS LIKE '%16*2M%' OR @CLASS LIKE '%16x2 M%') THEN 32
WHEN ( @CLASS LIKE '%Fractional E3 Access Port%' OR @CLASS LIKE '%16x2M%' OR @CLASS LIKE '%16*2M%' ) THEN 34

If you get started now... you may just finish by the end of the year...

Can see some logic problem there though... you will never get to the "THEN 32" or "THEN 34" logic as the %2M% will always consume it.

Certainly trimming and uppercasing will reduce it a touch

Is there anyway you can include this logic in your DB extract query (as an extra column). Or run a side query that can be used in a lookup, keying on whatever makes the input unique?

[Kryt0n]

Duplicate post - cleared for sanity purposes

[chulett]

Certainly trimming and uppercasing will reduce it a touch

If a 'touch' is around 75% then I agree.

[Kryt0n]

Hmmm, so one reply to one of my duplicate posts stops me deleting either... (and I didn't click Submit twice for anyone thinking so)

[chulett]

Yup, the moment someone replies you cannot delete. And you don't need to click submit twice to double or triple post, it happens automatically when there's a delay posting. If the 'Submit' doesn't immediately complete, I always go back and check the thread for duplicates and delete any it decided to create. And it usually does.

You can always edit one and remove the content if you like.

[Kryt0n]

I have noticed it is when the posting gets delayed. If there is a duplicate after I post I do tend to go delete it immediately but had to go to a meeting this time so never saw the aftermath...

[jhmckeever]

Hi sourabhverma,

I would consider placing all your 'like' patterns into a file or DB table, expressing them as regular expressions. You could then join these to your source and compare them to your value (@CLASS) using the regular expression parallel routine provided <a href="viewtopic.php?t=107882&highlight=regexp">here</a>.

You'd then need to post-process the results to see if you had any matches for your primary key, but this might be a good place to start.

Rgds,
John.

[Sainath.Srinivasan]

Get a row generator / seq file with pattern and associated value.

Do an inverted pattern match using index - i.e. locate pattern in word rather than word of a pattern.

If it matches, then set it to the associated value.

[dh_Madhu]

Try what John has said. I have done it that way before for a similar requirement.....