MySQL Forums
Forum List  »  German

Dynamic Variable for all Columns of a Table
Posted by: Ana Moya
Date: December 18, 2019 03:50PM

I hope you are doing well.

I am Trying to make a calculation in MYSQL for all columns of a Table.

Table: bev

Jahr GKZ gesamt A B C

2017 1111000 88.519 855 888 814

2017 1112000 247.943 2.414 2.379 2.262

2017 1113000 253.106 2.290 2.343 2.289

2017 1113004 43.392 408 416 403

2017 1113008 12.383 137 134 124

2017 1113012 27.106 252 252 249

2017 1113016 41.673 391 410 398

2017 1113020 39.585 364 391 373

2017 1113024 10.075 63 73 74

2017 1113028 24.083 199 205 209

2017 1113032 8.745 63 77 65

2017 1113036 18.143 170 170 143

2017 1113040 27.921 243 215 251
Table: ja

GKZ Jahr ja_name

1001000 2017 K X

1002000 2017 K Y

5370000 2017 L Z

5370004 2017 Z1

5370012 2017 Z2

5370016 2017 Z3

5370020 2017 Z4
I alread got the calculation for one column (the first one: gesamt) in a function:

CREATE DEFINER=`DB`@`%` FUNCTION `Total_Amount_Funct`(
bev_ID int(11),
bev_Total int(11),
ja_name VARCHAR(255),
ja_jahr int(11)) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE Total_Amount int(11);
DECLARE kreis int(11);
DECLARE Total_Sum int(11);

SET kreis = (bev_ID / 1000) ;
SET Total_Sum = (SELECT SUM(b.gesamt)
FROM bev as b, ja as j
WHERE b.GKZ = j.GKZ
AND b.Jahr = j.Jahr
AND j.Jahr = ja_jahr
AND (MOD(b.GKZ, 1000) <> 0)
AND (MOD(b.GKZ, 1000) != 0)
AND NOT (MOD(b.GKZ, 1000) = 0)
AND (b.GKZ BETWEEN (kreis*1000 + 1) AND (((kreis+1)*1000)-1))
AND j.ja_name IS NOT NULL);


SET Total_Amount = bev_Total-Total_Sum;


RETURN (Total_Amount);
END
This function can be called with the following select:

SELECT DISTINCT
bev.GKZ,
bev.Jahr,
bev.gesamt,
CASE WHEN (bev.GKZ % 1000 = 0) THEN
coalesce(Total_Amount_Funct(bev.GKZ, bev.gesamt, ja.ja_name, bev.Jahr), bev.gesamt)
ELSE bev.gesamt
END AS bev,
ja.ja_name
FROM
ja, bev
WHERE
bev.GKZ = ja.GKZ
AND bev.Jahr = ja.Jahr;
I realy would like to apply the function for all columns of the table. Maybe as a stored procedure? Maybe as dynamic columns. I do not know. I have solved this problem in MS SQL with dynamic columns but I have the feeling that translating it will take more time than trying to complete the function as a Stored Procedure.

The name of the columns can be obtained by:

SELECT column_name
FROM information_schema.columns
WHERE table_name='bev'
and column_name not in ('Jahr','GKZ');
As Result it should be:

GKZ Jahr gesamt bev ja_name

1111000 2017 88.519 88.519 K X

1112000 2017 247.943 247.943 K Y

1113000 2017 253.106 101.350 L Z

1113004 2017 43.392 43.392 Z1

1113012 2017 27.106 27.106 Z2

1113016 2017 41.673 41.673 Z3

1113020 2017 39.585 39.585 Z4

I will appreciate a lot for every help.

Kind Regards Ana

Options: ReplyQuote


Subject
Views
Written By
Posted
Dynamic Variable for all Columns of a Table
179
December 18, 2019 03:50PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.