Using Regular Expression in Transformer Stage Constraint

dannywcw · Post by **dannywcw** » Thu Oct 25, 2007 3:41 am

Hi,

Has anyone tried using Oracle regular expression in transformer stage constraints? I am trying to use it to do data filtering i.e. data which do not fulfill the condition will be rejected and stored in Reject Table.

The regular expression that I am using is as below:

REGEXP_LIKE(SUBSTR(COLUMN_NAME,1,4),'^[0-9]+$')

Keep getting error saying "Error in constraint: Variable 'REGEXP_LIKE' not defined."

Can anyone pls help?

Thanks in advance!

Maveric · Post by **Maveric** » Thu Oct 25, 2007 4:14 am

There is no function called "REGEXP_LIKE" in transformer stage. That is the reason you are getting the error. What exactly does "REGEXP_LIKE" do in Oracle? There might be a function in Transformer which does the same thing.

chulett · Post by **chulett** » Thu Oct 25, 2007 6:47 am

Curious what made you think you could use Oracle-specific syntax anywhere except in Oracle?

Rather than me explaining it, please explain in words for our home audience what exactly your regular expression is attempting to check for. Then people will be able to help with the DataStage syntax you need.

dannywcw · Post by **dannywcw** » Fri Oct 26, 2007 1:58 am

I am new in Datastage, thought that Datastage is so powerful that it can understand Oracle specific syntax as well...

Alright, let me explain what exactly that i wanted to do with the regular expression in details.

I have a Datastage job with a Transformer having a single input and 2 outputs. The first output is to generate another flat file after removing any invalid records found while the second output is to store all invalid records rejected by the transformer into the Reject Table.

Basically, this job is used to extract data from a flat file (extracted from external system using another job) to a target table by inserting data in output 1 using SQL loader.

Due to the nature of the external system, 1 of the column in the input file might contains either a character or a date value. Therefore, the column in the target table to hold the value is declared as varchar instead of date. Problem occur when there is bad data in that column. E.g.: 19P8-12-03 00:00:00. In this case, if it is a date value, I would like to use the regular expression to check if the date is a valid date. Record will be rejected if it fails the checking.

Other than regular expression, is there a function in datastage which is similar to isDate()?

Thank you!

ray.wurlod · Post by **ray.wurlod** » Fri Oct 26, 2007 2:22 am

What you need is the IsValid() function. This is a general purpose validation function for any data type (you provide the data type and, possibly, format picture to the function).

dannywcw · Post by **dannywcw** » Tue Oct 30, 2007 8:26 pm

Hi Ray,

By searching thru the forum for similar problem posted earlier, I understand that IsValid() function is only available in parallel job not server job. I have also downloaded the IsValid() function for server job which you have posted earlier and encountered the following error while compiling:

Compiling: Source = 'DSU_BP/DSU.IsValid', Object = 'DSU_BP.O/DSU.IsValid'

0002 BEGIN HEADER

^
Variable Name (UNDEFINED) unexpected, Was expecting: "CASE", "TRANSACTION"

1 Errors detected, No Object Code Produced.

Besides that, I have also read about using ICONV/OCONV to validate date. Could you pls explain it again as I don't really understand how to do it.

Thanks for your reply!

ArndW · Post by **ArndW** » Tue Oct 30, 2007 8:34 pm

Please post your first couple of lines, BEGIN HEADER is not DS/BASIC code. Also, read up on the OCONV() function with date conversion, look at the examples, try them and then ask any remaining questions. Few people are willing to write a lengthy answer to an open-ended question when you know the answer is documented.

chulett · Post by **chulett** » Tue Oct 30, 2007 9:41 pm

dannywcw wrote:... and encountered the following error while compiling:
Compiling: Source = 'DSU_BP/DSU.IsValid', Object = 'DSU_BP.O/DSU.IsValid'

Compiling what, exactly? From the error message, it seems to be a custom routine but always best to be as explicit as possible. As Arnd notes, posting the routine (wrapped in code tags) would help.

Wagering you need a DEFFUN.

ray.wurlod · Post by **ray.wurlod** » Tue Oct 30, 2007 10:17 pm

The BEGIN and END tags are characteristic of DSX export files. Can you please try downloading it again? Others have had success with it. You need to Import DataStage Components to get the routine into your project.