On Wed, 06 Jan 2010 00:50:48 +0100 Paweł Rohde pawel@rohde.pl wrote:

Krzysztof Szwaba pisze:

...

Jakim zapytaniem SQL można wylistować numer konta bankowego dla danego klienta przy subkontach ?

Żadnym, trzeba użyć funkcji php z klasy LMS.

Potrzebuję tego do wyświetlenia numerów kont w Openoffice przy korespondencji seryjnej. Mogę prosić o więcej szczegółów jak ugryźć ten temat ?

Krzysztof

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)