So i am not able to create a calculated column for a table in mySQL, it says calculated columns cannot be calculated from calculated columns

Question

I am trying to create a calculated column but it shows error for the total_value column as it cannot be created from an calculated column vat_value... Is there a way around it, I desperately need those two columns in the table. Thanks,

create table purchase(
invoice_num varchar(8),
product_id varchar(10),
product_name varchar(50),
supplier_name varchar(30),
gstn int(16),
vat_num varchar(12),
ph_num int(10),
quantity decimal(12,3),
unit varchar(3),
in_out char(1),
tax_value decimal(12,2),
gst_percent int(2),
igst decimal(12,2),
cgst decimal(12,2),
sgst decimal(12,2),
unloading int(8),
fare int(8),
vat_percent int(2),
vat_value decimal(8,3) as ((tax_value*vat_percent)/100) persistent,
total_value decimal(12,2) as (tax_value+igst+cgst+sgst+unloading+fare+vat_value)    
);

What is your MySQL version ? Run `Select VERSION();` and report the result of this query into the question. Generated Columns support was added only after MySQL version 5.7 and onwards. — Madhur Bhaiya, Jul 19 '20 at 04:19

score 0 · Accepted Answer · answered Jul 19 '20 at 03:57

0

You can not refer one calculated column in definition of others.

One workaround can be, you refactor formula for total_value as below -

total_value decimal(12,2) as (tax_value+igst+cgst+sgst+unloading+fare+((tax_value*vat_percent)/100))

answered Jul 19 '20 at 03:57

Shantanu Kher

1,014
1
8
14

this works actually, Now I can use both columns without referring to the calculated column, – SagarRawat Jul 19 '20 at 05:40
Glad to help!! Thank you for confirming. – Shantanu Kher Jul 19 '20 at 10:43

So i am not able to create a calculated column for a table in mySQL, it says calculated columns cannot be calculated from calculated columns

1 Answers1