MySQL Forums
Forum List  »  Microsoft Access

Trouble with Generated Column
Posted by: Ron Mittelman
Date: August 16, 2020 04:22PM

Not sure if this is correct forum for this issue.
I'm trying to migrate a Microsoft Access back-end database to MySQL, and having problems with generated columns. Couldn't get migration assistant to work properly, so used a 3rd-party tool to migrate my tables. This created the tables but I have to fix some things before populating data.

In Access I have some calculated columns. For example, the 'Heading' column is a combination of title (Mr & Mrs, etc.) and names and spouse names.

Here is calculation in Access to build a heading:

IIf([UseTitle],[Title] & " ","") & IIf(IsNull([DonorFirstName]),[DonorLastName],[DonorFirstName] & IIf(IsNull([SpouseName])," " & [DonorLastName],IIf(IsNull([SpouseLastName])," and " & [SpouseName] & " " & [DonorLastName]," " & [DonorLastName] & " and " & [SpouseName] & " " & [SpouseLastName])))

(In Access, IIF is same as MySQL IF function)

So if I'm supposed to use a title, put that in.
If first name is null, add last name (that's how this database denotes a company name). Otherwise, add first name. If no spouse name, add space and last name. If spouse, add " and " plus spouse first name. Then add last name. So now you have "Mr. & Mrs. John and Jane Doe". In case the spouse has a different last name, it will instead build a string like "Mr. & Mrs. John Doe and Jane Smith".

I tried doing this in MySQL generated column, but it keeps erroring out. It seems to work, then when I go into the table designer in Workbench, it says Error parsing DDL. When I view the DDL, the calculated column shows the error on the Heading column:

`Heading` varchar(509) CHARACTER SET utf8 COLLATE utf8_general_ci GENERATED ALWAYS AS (concat(if(((`UseTitle` = 0) or (`Title` is null)),_utf8mb3'',concat(`Title`,_utf8mb3' ')),`DonorFirstName`,_utf8mb3' ',`DonorLastName`)) VIRTUAL,

The squiggly red line is at _utf8mb3'', right under the ''. It says "Syntax error: extraneous input found - expected 'comma'".

This is using a very simple generated field for a test, trying to combine only 2 or 3 columns.
It's very strange that the DDL will create a new table, then give me a syntax error when I try to open the table designer in Workbench.

Is there a better way to accomplish this rather than a bunch of contats with IF statements like I'm doing?

Thanks...

Options: ReplyQuote


Subject
Views
Written By
Posted
Trouble with Generated Column
219
August 16, 2020 04:22PM


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.