Finding a substring with wildcard

[I_Server_Whale]

Hi All,

Is there any function where I can pattern match part of the string from an incoming field?

I want an equivalent function for the letters in bold:


UPDATE TABLE1
SET Col1 = 'DSX'
WHERE Col1 LIKE 'DSX%'

If the incoming data is :

Code: Select all

ULE
RSXoih
DSXchange
DSXhghghg
DSXigjoi099
DSX903u09ru0
DSXncc8
ULE

Then the output should be:

Code: Select all

ULE
RSXoih
DSX
DSX
DSX
DSX
DSX
ULE

I tried 'Matches' But it doesn't seem to work. I can use 'COUNT' as well. But is there a better way?

Your help is very much appreciated.

Thanks,
Naveen.

[kalpna]

Naveen!
try using like operator.

Cheers
Kalpana.

[ArndW]

Are you looking to do this in a transform stage? If so, you could use

Code: Select all

IF In.Column[1,3]='DSX' THEN 'DSX' ELSE In.Column

. Or are you looking to do this elsewhere?

[ray.wurlod]

Matches is fine; but it uses an idiosyncratic pattern specification. A pattern is made up of a number of alphabetic characters, a number of numeric characters and/or a number of "don't care what type" characters. These are represented by nA, nN or nX respectively, where n is the number, or 0 for "any number of". So "0X" is the true wildcard; it can also be represented as "...". Literal text can be intermingled. Two solutions for your particular requirement are

Code: Select all

InLink.Column Matches "'DSX'0X"
InLink.Column Matches "DSX..."

If the match pattern is multi-valued, Matches returns true if the string matches any of the specified patterns. For example to match either DSX or XML at the beginning of the string in one Matches operator

Code: Select all

InLink.Column Matches "DSX..." : @VM : "XML..."

[kcbland]

DS BASIC function INDEX searches a text string for a string value and returns the starting character position for a given occurence.

[I_Server_Whale]

Hi All,

Forgive me for the delay. Been caught up with work. Actually, I need to update a particular field in a table through DataStage.

Which is similar to this statement:

Code: Select all

UPDATE TABLE1 
SET Col3 = 'DSX' 
WHERE Col3 LIKE 'DSX%' 

Sample Input & Output data and formats:

If the incoming data is :

Code: Select all

ULE 
RSXoih 
DSXchange 
DSXhghghg 
DSXigjoi099 
DSX903u09ru0 
DSXncc8 
ULE 

Then the output should be:

Code: Select all

ULE 
RSXoih 
DSX 
DSX 
DSX 
DSX 
DSX 
ULE 

My first idea was to pull all the records from the database to a sequential file, where I update the required field and then in an other job I load this sequential file back to the table using ORABLK Stage. The table has around 3-4 million records.

Some what like this:

Design 1 (Two Jobs)

Code: Select all

OracleTable-----------> Transformer -------------> Sequential File
                (Update required field)

Code: Select all

Sequential File-----------> Transformer ----------->OracleTable(BLK Load)

Some how, the idea seemed inefficient to me as I was using two jobs instead of just one. So, I thought I would go for Design 2 as below-

Design 2

Code: Select all

Transformer(Dummy) --------------> OCI Stage (run Custom update SQL)

In this design, I will have a dummy transformer by defining a dummy stage variable to make the compiler happy (learned from Mr.Craig) .
For the link, I have all the meta-data of the table whose derivations contain the dummy stage variable. The link has a constraint "@FALSE".

Now, In the OCI stage, I selected "User defined Query" and my custom SQL statement was:

Code: Select all

UPDATE TABLE1 
SET Col3 = 'DSX' 
WHERE Col3 LIKE 'DSX%' 

Note: The OCI stage has all the columns of the table in the metadata, not the just 'Col3' which is supposed to be updated.

BUT, When I run this job:

I get this error:

Code: Select all

JobName..LinkName: ORA-01036: illegal variable name/number

Am I missing anything or Am I going wrong somewhere?

Many Thanks for your earlier responses,

Naveen.

[kcbland]

For the error message, can't tell you why. But for your query, use LIKE not =

Code: Select all

UPDATE TABLE1 
SET Col3 = 'DSX' 
WHERE Col3 LIKE 'DSX%' 

[kcbland]

I re-read your original post. Remove all columns from the output link, just leave one column. Then try debugging why the database believes that a value is being passed to DML that doesn't match the datatype.

[chulett]

Now, In the OCI stage, I selected "User defined Query" and my custom SQL statement was:

Code: Select all

UPDATE TABLE1 
SET Col3 = 'DSX' 
WHERE Col3 = 'DSX%' 

Note: The OCI stage has all the columns of the table in the metadata, not the just 'Col3' which is supposed to be updated.

Can't do that! All columns defined in the stage must be bound into the query with the appropriate parameter markers. For two columns there must be a :1 and a :2 in the query. Change the sql to look like this:

Code: Select all

UPDATE TABLE1 
SET Col3 = :2
WHERE Col3 like :1

Note the like to match the wildcard usage, unless that is a literal you are trying to replace. Two columns in your job, doesn't matter what you call them. The first is marked as a Key and is populated with 'DSX%', the second non-key field is set to 'DSX' in the derivation. Then you should be fine.

[I_Server_Whale]

Ken,

Sorry about the typo, I did use LIKE and not '='.

As per you suggestion, I did remove all the columns from the output link except the one that is being updated (Col3). But still I'm get the same error.

I tried to modify the query as per one post (LINK).

I tried to replace Col3 with :3, it still spit the same error. And then I tried with:

Code: Select all

UPDATE TABLE1 
SET :1 = 'DSX' 
WHERE :1 LIKE 'DSX%'

The job runs successfully but it does not update any rows in the table.

Now, I'm stuck here. . I will correct the SQL in my earlier posts.

Thanks again,
Naveen.

[I_Server_Whale]

Can't do that! All columns defined in the stage must be bound into the query with the appropriate parameter markers. For two columns there must be a :1 and a :2 in the query. Change the sql to look like this:

Code:
UPDATE TABLE1
SET Col3 = :2
WHERE Col3 like :1


Note the like to match the wildcard usage, unless that is a literal you are trying to replace. Two columns in your job, doesn't matter what you call them. The first is marked as a Key and is populated with 'DSX%', the second non-key field is set to 'DSX' in the derivation. Then you should be fine.

_________________
Craig Hulett

Ahhhh! I got the whole concept wrong.

So, I still will have the '@FALSE' constraint and the derivation for the two columns will be Col1 = 'DSX%' and Col2 = 'DSX'. Am I right?

I will try this and let you know.

Thanks Craig!

Naveen.

[chulett]

No, @FALSE is not what you need in this case as that will prevent any rows going down the link. Sounds like you want @OUTROWNUM=1 in this case.

[I_Server_Whale]

Craig,

Thank you sooooo much. It works like a charm. :D

Have a nice weekend, Thank you all for your responses.

Naveen.