A.L.E.C wrote:
SELECT c.*, CONCAT(98 - ( (CAST(CONCAT(CONCAT(d.account, LPAD(c.id, 24 - LENGTH(d.account), '0')), '252100') AS decimal(32,0))) - FLOOR( (CAST(CONCAT(CONCAT(d.account, LPAD(c.id, 24 - LENGTH(d.account), '0')), '252100') AS decimal(32,0)))/97)*97 ), CONCAT(d.account, LPAD(c.id, 24 - LENGTH(d.account), '0'))) AS account FROM customers c JOIN divisions d ON (c.divisionid = d.id)
Poprawka
SELECT c.*, CONCAT(LPAD(98 - ( (CAST(CONCAT(CONCAT(d.account, LPAD(c.id, 24 - LENGTH(d.account), '0')), '252100') AS decimal(32,0))) - FLOOR( (CAST(CONCAT(CONCAT(d.account, LPAD(c.id, 24 - LENGTH(d.account), '0')), '252100') AS decimal(32,0)))/97)*97 ), 2, '0'), CONCAT(d.account, LPAD(c.id, 24 - LENGTH(d.account), '0'))) AS account FROM customers c JOIN divisions d ON (c.divisionid = d.id)
i funkcja dla postgresa
CREATE OR REPLACE FUNCTION bank_account(int, text) RETURNS text AS $$ SELECT CASE WHEN LENGTH($2) > 20 THEN $2 WHEN LENGTH($2) < 8 THEN '' ELSE TO_CHAR(98 - ( CAST($2 || LPAD($1::text, 24 - LENGTH($2), '0'::text) || '252100' AS decimal(32,0)) - FLOOR( CAST($2 || LPAD($1::text, 24 - LENGTH($2), '0'::text) || '252100' AS decimal(32,0))/97::decimal(34,2))*97 ), 'FM00') || $2 || LPAD($1::text, 24 - LENGTH($2), '0'::text) END; $$ LANGUAGE SQL IMMUTABLE;
SELECT c.*, bank_account(c.id, d.account) AS account FROM customers c JOIN divisions d ON (c.divisionid = d.id)