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!
Using Regular Expression in Transformer Stage Constraint
Moderators: chulett, rschirm, roy
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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!
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!
dwcw
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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!
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!
dwcw
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.dannywcw wrote:... and encountered the following error while compiling:
Compiling: Source = 'DSU_BP/DSU.IsValid', Object = 'DSU_BP.O/DSU.IsValid'
Wagering you need a DEFFUN.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.