Variables in SQL, for Those not using SQL Server | Learn SQL from Scratch 13 | Kovolff
One thing to avoid in calculations is using constants such as:
CAST("weight" AS INT) * 30 AS "daily_calories",
Constants are often NOT that constant.
If you use this 30 in multiple locations in a very long SQL query, it will be quite a hassle changing all instances of this 30 to another number.
Why not use variables, which you set and initialize at the top of the query.
Some Database engines such as SQL Server and MySQL allow you to set variables and use these variables in your calculations.
What do you do when you’re not using one of these engines?
You can create your own variables by creating a view holding your variables
i.e.
CREATE OR REPLACE VIEW global_factors AS
SELECT
'30' AS "cal_per_day_per_kg"
, '1.5' AS "prot_per_day_per_kg"
FROM user_data;
Then in your calculations, instead of
i.e.
CAST("weight" AS INT) * 30 AS "daily_calories"
You use
CAST("weight" AS INT) * CAST((SELECT DISTINCT "cal_per_day_per_kg" FROM global_factors) AS INT) AS "daily_calories"
Views are great because they hardly cost you any memory, and this view is created and modified everytime you run the query.
Alternatively you can create a table
i.e.
CREATE TABLE public.global_factors
(
"id" text,
"cal_per_day_per_kg" text,
"prot_per_day_per_kg" text
);
INSERT INTO public.global_factors("id", "cal_per_day_per_kg", "prot_per_day_per_kg") VALUES('0', '30', '2');
Then in your calculations you use
CAST("weight" AS INT) * CAST((SELECT DISTINCT "cal_per_day_per_kg" FROM global_factors) AS INT) AS "daily_calories",
Just wit tables you have to be careful to drop the table before running the query again, as the table already exists, i.e.
DROP TABLE public.global_factors;
CREATE TABLE public.global_factors
(
"id" text,
"cal_per_day_per_kg" text,
"prot_per_day_per_kg" text
);
INSERT INTO public.global_factors("id", "cal_per_day_per_kg", "prot_per_day_per_kg") VALUES('0', '30', '2');
#sql #variables #databases