I have requriment like this,source table has >100000 records
I want output like this
|ABC865YTE||invalid serial no||NNNXXXXXNN||NNNXNXXXNN||NNNXXXNNNN|
|UYD8J8UUU||invalid serial no||XXXNNNXXXX||XXXNXNXXXX||XXXNNNNNN|
|AA856GB63||valid serial no||XXNNNXXNN||XXNNNXXXNN||XXNNNXXNN|
|WQU8M6OH||invalid serial no||NNXXXNNXX||NNXXXNNNXX||NNXXXNNXNXN|
|SJUW9999UYSB||invalid serial no||XXXNNNXXXX||XXXNNNXXNX||XXXNNNXXXX|
|SKUJ96IJDJF8||invalid serial no||XXNNNXXNN||XXNNNXXNNX||XXNNXNXNXNN|
|QQQ890IIIMM||invalid serial no||XXXNNNXNXX||XXXNNXXNXX||XXXNNNXXNNN|
IF THE SERIAL_NO EXACTLY MATCH WITH ANY ONE OF THE OTHER SR_FORMATS(SR_FOR_1,
SR_FOR_2,SR_FOR_3) THEN VALID SERIAL NO ELSE INVALID SERIAL NO
EX: AA856GB63 THIS IS SAME AS THE FORMAT OF SR_FOR_1
please give me solution , if u want then divide this table into any parts,all columns are varchars
please unserstand my problem,
N- Numeric value and X-alpha numeric
means first character in serial_no should be checking with first character in sr_for_1 or sr_for_2 or sr_for_3 and then second and then third and so on untill size of the 3 format columns,
are u understand my problem...? if no then i will explain
acutallly that logic is developed in pl_sql but our requriment is changed that logic to informatica transforamtions
Try the following,
Use translate function or replacechr function to replace the alphabets with 'N' and Number with 'X'.Then compare the results with all the serialformat.
In expression,Create the following ports in the order
Alphabets=ABCD........Z --Enter all the alphabets
Numbers=0123....9 --Enter all the numbers
Alphanumeric = N
Numeric = X
ChangingAlphabets = Translate ( Serial_no, Alphabets,Alphanumeric) ---This will replace the alphabets with N
Format_Final = Transalate( ChangingAlphabets,Numbers,Numeric) --This will replace the number with X
Valid_Check = IIF( (Format_Final = sr_for_1 OR Format_Final = sr_for_2 OR Format_Final = sr_for_3), 'Valid Serial No', 'Invalid Serial No')
Please try this and let me know the results
FYI : You can use the replacestr function also ,but you need to provide all the alphabets and numberic in the REPLACESTR function.Refer syntax in the help docs
i understand your logic. but see the below serial_n
The above serial_no is matched with sr_for_1 becauese N-1234567890 and X-'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'
as per ur logic
for Above serial_no:986ADN68RNP
The result is not matched with sr_for_1 so it gives the 'invalid serial no'
The main concern i am troubling with 'X',alphanumric means numeric and alphabets
How to identify which are all the alphanumeric i.e 68 as per your above example.What is the logic and when should the numbers will be replaced with X instead of N.
I have misplaced the replacing character in my previous post .Please use the below
for Above serial_no:986ADN68RNP
Required_Format = NNNXXXXXXXX
I have given the required format above .You want to replace the numbers with X.Please corret me if i am wrong.
thats not a problem,
first i want to change serial_no , to 'X' format--->
so the above condition converts all characters to 'X'
else first i want to change to serial_no,to 'N' format-->
so the above condition convers all numeric characters to 'N', this is fine
but in compare with sr_for_1,2 and 3 the values of numerics are populated to 'X' and 'N' both
that is my problem
give me your hand for this solution
@Naresh : not sure about your requriement ,
Convention : Alphabets : a-z A-Z ; Numerics : 0-9 ; Alphanumeric : a-z A-Z 0-9
If your requirment is
Case1)Alphabets[a-z A-Z] being represented by X and numerics[0-9] by N then the below expression should do,
*note that first alphabets are converted to X and not numerics as N indicating a number can be replaced by N character and vice-versa.
*as a work around you can use some other character #,* etc., to replace each of X and N indicators. in this case you have to translate the sr_for_[1-3] using same conventions X ,N to other special characters [#,* etc.,] before comparision.
Case2)Some numerics by N and alphanumeric as X
In this case the X's are immaterial just have to validate that only numerics are present at the place where N is present ,
we can use reduction to get both the serial_no and sr_for_[1-3] to a common notation for comparision,
reduce the code present in sr_for_[1-3] in terms of X and N with X with 0 and and N as 1
and for the serial number mark the alphabets as 0
as weight of alphabets is lower than any number if alphabets is present in place of a numeric it would imply a overall lower values, and hence..
IIF((serial_no_val >= sr_for_1_val AND length(sr_for_1)=length(serial_no)) OR
(serial_no_val >= sr_for_2_val AND length(sr_for_2)=length(serial_no)) OR
(serial_no_val >= sr_for_3_val AND length(sr_for_3)=length(serial_no)),
'valid serial no','invalid serial no')
As you mentioned number of records are high (>100k) try and splti the logic ,have filter before transformation.
Hi Naresh ,
Create an Expression Transformation and design following Ports :
REG_REPLACE ( REG_REPLACE ( SR_FOR_1 , ' [0-9]? ' , ' N ' ) , ' \w? ', ' X ' )
REG_REPLACE ( REG_REPLACE ( SR_FOR_2 , ' [0-9]? ' , ' N ' ) , ' \w? ', ' X ' )
REG_REPLACE ( REG_REPLACE ( SR_FOR_3 , ' [0-9]? ' , ' N ' ) , ' \w? ', ' X ' )
IIF ( V_SR_FOR_1 = V_SR_FOR_2 AND V_SR_FOR_2 = V_SR_FOR_3 , ' VALID_SERIAL_NO ' , ' INVALID_SERIAL_NO ' )