Rebuild complex excel function in sql

Toad Data Point
1

Hello everyone,

I need help with the rebuild of an excel function in sql for oracle. You find this function below. It is an ADDITION to the Dutch Elfproef .
(see general examples on : Mobilefish.com - Check Dutch bank account number or citizen service number with Eleven test ).

I tried for 2 long times, but get stuck so PLEASE your help.

Ther is an excel version available at: toad - pCloud
In the excel on page examples are some numbers for testing including good/wrong ('=fout'). these numbers are below too.

I would be very glad to have an sql statement / funtion / package to check some thousends of existing numbers in an oracle database. The second goal is to make a constraint.

Again: the REGULAR Dutch Elfproef WON'T fit! I need this specific formula build in Excel.

Thanx al lot in advance! Harry.

note: Formula is in Dutch. Meaning > IF((when(value(part)----

=ALS((ALS(((WAARDE(DEEL(A2;1;1))*9)+(WAARDE(DEEL(A2;2;1))*8)+(WAARDE(DEEL(A2;3;1))*7)+(WAARDE(DEEL(A2;4;1))*6)+(WAARDE(DEEL(A2;5;1))*5)+(WAARDE(DEEL(A2;6;1))*4)+(WAARDE(DEEL(A2;7;1))*3)+(WAARDE(DEEL(A2;8;1))*2)-(AFRONDEN.NAAR.BENEDEN((((WAARDE(DEEL(A2;1;1))*9)+(WAARDE(DEEL(A2;2;1))*8)+(WAARDE(DEEL(A2;3;1))*7)+(WAARDE(DEEL(A2;4;1))*6)+(WAARDE(DEEL(A2;5;1))*5)+(WAARDE(DEEL(A2;6;1))*4)+(WAARDE(DEEL(A2;7;1))*3)+(WAARDE(DEEL(A2;8;1))*2))/11);0)*11))=10;0;((WAARDE(DEEL(A2;1;1))*9)+(WAARDE(DEEL(A2;2;1))*8)+(WAARDE(DEEL(A2;3;1))*7)+(WAARDE(DEEL(A2;4;1))*6)+(WAARDE(DEEL(A2;5;1))*5)+(WAARDE(DEEL(A2;6;1))*4)+(WAARDE(DEEL(A2;7;1))*3)+(WAARDE(DEEL(A2;8;1))*2)-(AFRONDEN.NAAR.BENEDEN((((WAARDE(DEEL(A2;1;1))*9)+(WAARDE(DEEL(A2;2;1))*8)+(WAARDE(DEEL(A2;3;1))*7)+(WAARDE(DEEL(A2;4;1))*6)+(WAARDE(DEEL(A2;5;1))*5)+(WAARDE(DEEL(A2;6;1))*4)+(WAARDE(DEEL(A2;7;1))*3)+(WAARDE(DEEL(A2;8;1))*2))/11);0)*11))))-(WAARDE(DEEL(A2;9;1)))=0;"GOED";"FOUT")

|examples|Resultaat|
|378498277|good|
|303067833|wrong|
|310007859|good|
|380071253|good|
|380041290|wrong|
|380071710|good|
|378561625|good|
|380107922|good|
|380061363|good|
|378602230|wrong|
|380111688|good|

2

Might want to post to a more appropriate forum, such as StackOverflow or similar, as ToadWorld focuses on Toad product usage and product capabilities.

However, trying to be helpful here.
Rather than attempt to create your elfproef formula in SQL syntax within the query, I would personally rather define a callable function or procedure in Oracle PL/SQL that will accomplish the above.

Here's a nice discussion thread, with sample code using both SQL and PL/SQL syntax that you can draw inspiration from:

Hope this helps.

3

Hello Gary,

Yes it helped. A lot! Thanx for that.

I did not make it to get a function, but with the below I did find the errors in our data.

Thanx again from Netherlands.

(and will post StackOverflow next time).

select sub2.controle, sub2.cri, sv2.subjectnr, sv2.subjectnaam, sv2.gbageboorte, trunc(ttopvoer) opvoerdatum
from
(
select sub1.*
, case when sub1.restant = 0 then 'Good'
when sub1.restant = 10 AND TO_NUMBER(SUBSTR(sub1.cri, 9, 1)) = 0 then 'Good'
else 'False'
end controle
from
(
select cri
, mod
(
(to_number(substr(Lpad(cri,9,0),1,1))*9) +
(to_number(substr(Lpad(cri,9,0),2,1))*8) +
(to_number(substr(Lpad(cri,9,0),3,1))*7) +
(to_number(substr(Lpad(cri,9,0),4,1))*6) +
(to_number(substr(Lpad(cri,9,0),5,1))*5) +
(to_number(substr(Lpad(cri,9,0),6,1))*4) +
(to_number(substr(Lpad(cri,9,0),7,1))*3) +
(to_number(substr(Lpad(cri,9,0),8,1))2) +
(to_number(substr(Lpad(cri,9,0),9,1))-1)
,11) restant
from sjm_fulsubject sv
where 1=1
and indvervallen = 'N'
group by cri
) sub1
where 1=1
) sub2
, sjm_fulsubject sv2
where 1=1
and sub2.cri = sv2.cri
and sub2.controle != 'Good'