No question at this time
DBA Top 10
1 A. Kavsek 12400
2 M. Cadot 7300
3 B. Vroman 6400
4 P. Wisse 5600
5 J. PĂ©ran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 A. Chavan 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48458
Total active users1487
Act. users last 24h2
Act. users last hour1
Registered user hits last week190
Registered user hits last month829
Go up

SQL Formula advise
Next thread: Clusterware state back to ONLINE state
Prev thread: issue with table import

Message Score Author Date
Hi, I would like to write a function to calculate...... harinder kaur Jun 18, 2018, 13:55
First you have to tell where are these numbers. ...... Michel Cadot Jun 18, 2018, 18:17
Hello Hsac, assuming the digits are stored as a...... Bruno Vroman Jun 19, 2018, 08:14

Follow up by mail Click here


Subject: SQL Formula advise
Author: harinder kaur, United Kingdom
Date: Jun 18, 2018, 13:55, 538 days ago
Os info: linux
Oracle info: 12c
Message: Hi,
I would like to write a function to calculate the check digit.

My requirement is:

i have given a 12 digit number ,each of the first 12 digits is multiplied by a weight which depends on its position in the number, and the resulting products added. The check digit is then obtained by subtracting the final digit of the resulting sum from 10.

Number 0 7 1 0 6 4 1 2 3 4 5 6

Weight 1 3 7 9 1 3 7 9 1 3 7 9

Product 0 21 7 0 6 12 7 18 3 12 35 54

The sum of the products is 175, the final digit being 5, so the check digit is 10 - 5, or 5.

i can write a loop in the function
for i IN 1..Length(Number) Loop
if i=1 then multiplier :=1
elsif i=2 then multiplier :=3
..

product := product+to_number(substr(Number,i,1)*multiplier;

End Loop;
checkdigit := SUBSTR(product,-1,LENGTH(product))


Appreciate your advise on doing this a better way.

Thanks
Hsac

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SQL Formula advise
Author: Michel Cadot, France
Date: Jun 18, 2018, 18:17, 537 days ago
Message:
First you have to tell where are these numbers.
In a table? In a string? In a variable? What type?
Put a complete test case, with the actual input.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SQL Formula advise
Author: Bruno Vroman, Belgium
Date: Jun 19, 2018, 08:14, 537 days ago
Message: Hello Hsac,

assuming the digits are stored as a string:
WITH sample_data AS

( SELECT 1 id, '071064123456' x FROM dual
UNION ALL SELECT 2, '123456789012' FROM dual
UNION ALL SELECT 3, '100000000000' FROM dual
UNION ALL SELECT 4, '010000000000' FROM dual
UNION ALL SELECT 5, '001000000000' FROM dual
UNION ALL SELECT 6, '000100000000' FROM dual
UNION ALL SELECT 7, '000000000000' FROM dual
)
, weights AS
( SELECT '137913791379' x FROM dual
)
, n AS
( SELECT LEVEL n FROM dual CONNECT BY LEVEL <= 12 )
SELECT s.id
, s.x
, 10 - MOD( SUM( TO_NUMBER( SUBSTR( s.x, n.n, 1 ) )
* TO_NUMBER( SUBSTR( w.x, n.n, 1 ) )
)
, 10
) check_digit
FROM sample_data s
, weights w
, n
GROUP BY s.id, s.x
ORDER BY id
;
Explanations:
- sample_data: just to present some cases (the first one is the one you have provided)
- weights: constant weights for each digit: always 1 3 7 9 1 3 7 9 1 3 7 9. I store them also in a string of 12 characters.
- n : simply an easy way to generate twelve rows with n = 1, 2, 3, ..., 12. To be used to "SUBSTR" strings of sample_data and weights
- main select: sum( digit(n) * weight(n) ), take "modulo 10", result is 10 - this modulo

Remarks:
- last example (all zeroes) is probably not allowed (check "digit" becomes "10")
- alternatively the string might contain 13 digits and we might check if the last digit is equal to its expected value

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here