MySQL Forums
Forum List  »  General

Using a function in a generated column
Posted by: Steve Sirica
Date: February 13, 2023 01:04PM

I have a Summary Table and a detail table. the relationship is one to many. The summary table contains totals based on Summary ID and record type. I have a function that will perform this function:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getSummTotal`(
ID int,
RecType varchar(50)
) RETURNS decimal(12,2)
DETERMINISTIC
BEGIN
declare Total decimal(12,2);
select sum(INVOICE_ITEM_TOTAL)
into Total
from INVOICE_DATA
where INVOICE_SUMMARY_ID = ID
and RECORD_TYPE_ID in (select RECORD_TYPE_ID
from RECORD_TYPE
where RECORD_TYPE_DESC = RecType);
RETURN coalesce(Total, 0.00);
END$$
DELIMITER ;

Now I want my column to look something like this:
ALTER TABLE `tech2dev_valubill`.`invoice_summary`
CHANGE COLUMN `INVOICE_SUMMARY_TOTAL_NRC` `INVOICE_SUMMARY_TOTAL_NRC` DECIMAL(12,2) GENERATED ALWAYS AS (getSummTotal(invoice_summary_id, 'NRC')) VIRTUAL ;

when I run the code I get the error:
ERROR 3763: Expression of generated column 'INVOICE_SUMMARY_TOTAL_NRC' contains a disallowed function: `getSummTotal`.

is there a way to allow this function or am I barking up the wrong tree? This works perfectly fine in SQL Server.

Options: ReplyQuote


Subject
Written By
Posted
Using a function in a generated column
February 13, 2023 01:04PM


Sorry, you can't reply to this topic. It has been closed.

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.