Evo nešto što radi pod PostgreSQL-om, a trebalo bi da radi i pod MS SQL-om.
Code:
CREATE TABLE artikli (
ean_kod VARCHAR(13) NOT NULL,
opis VARCHAR(5) NOT NULL
);
CREATE VIEW brojevi_od_1_do_13
(broj)
AS
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
UNION ALL
SELECT 11
UNION ALL
SELECT 12
UNION ALL
SELECT 13;
INSERT INTO artikli (ean_kod, opis)
VALUES ('8600102938083', 'dobar');
INSERT INTO artikli (ean_kod, opis)
VALUES ('1234567890123', 'los');
INSERT INTO artikli (ean_kod, opis)
VALUES ('86003148', 'dobar');
SELECT a.ean_kod,
a.opis,
(SELECT SUM(cds.cd) % 10 = 0
FROM (SELECT CAST(substring(a.ean_kod from b.broj for 1) AS SMALLINT) AS cd
FROM brojevi_od_1_do_13 AS b
WHERE (length(a.ean_kod) - b.broj) % 2 = 0
AND length(a.ean_kod) >= b.broj
UNION ALL
SELECT 3 * CAST(substring(a.ean_kod from b.broj for 1) AS SMALLINT) AS cd
FROM brojevi_od_1_do_13 AS b
WHERE (length(a.ean_kod) - b.broj) % 2 = 1
AND length(a.ean_kod) >= b.broj
) AS cds
) AS ispravan
FROM artikli AS a;
Ako postoji funkcija koja generiše skup rednih brojeva (znam da u PostgreSQL-u postoji), onda VIEW nije potreban, a upit se može skratiti da izgleda pristojno.
"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo