Question 215354 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQl calculated field

I’m using mysql 8, I need to create a calculated field named status in the supplier table. When creating this field, it shows an error. where am i going wrong? Could it be a syntax error?

create table tbqualificacao(
  idqualificacao int primary key auto_increment,
  data_inicio date not null,
  data_validade date not null,
  percentual decimal(10,2) not null,
  status varchar(20) not null as (CASE WHEN data_validade <= CURDATE() THEN "Vencido" ELSE "Vigente" END),
  servico_campo varchar(3) not null,
  processo_renovacao_qualificacao varchar(100) not null,
  contrato_vigente varchar(100) not null,
  qualificante varchar(100) not null,
  unique foreign key(idfornecedor) references tbfornecedor(idfornecedor)

);

The error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘as (CASE WHEN data_validade <= CURDATE() THEN "Vencido" ELSE "Vigente" END),
s’ at line 6

Tags:

2

Answers


  1. 0

    You are facing some issues and therefore you can’t create the table.

    1. Tables have columns, not fields.
    2. The information that your column should be not nullable has to be placed AFTER the CASE construct.
    3. Using non deterministic functions in generated columns is forbidden. CURDATE() is non determenistic, i.e. it does not produce the same result on the same data when being executed twice.

    To solve this, you should think about what you want to do. I think creating such column is the wrong idea.

    If you want to get such a column that compares a date with the current date while fetching data from this table, just put the case condition in the query rather than store this in a column. Or even let your application do this.

    If you want to have a separate column with the date when a row is inserted in your table, that column is usually be named "insert_date", "created_date" or similar. Then again, build your query depening on that date.

    If you really think you need a column which stores exactly this information as you tried for whatever reason, you can just create the column without the CASE construct and extend your INSERT commands to fill the column.

    Or – but this is again bad practice in my opinion – you could create a trigger which fills the column whenever inserts are executed on this table.

    Generally, you should keep in mind a database should store data, not logic based on this data. This part should be done in your application.

    Login or Signup to reply.
  2. 0

    Here is code that will work,

    CREATE TABLE tbqualificacao (
  idqualificacao int primary key auto_increment,
  data_inicio date not null,
  data_validade date not null,
  percentual decimal(10,2) not null,
  status varchar(20) as (CASE WHEN data_validade <= CURDATE() THEN "Vencido" ELSE "Vigente" END) not null,
  servico_campo varchar(3) not null,
  processo_renovacao_qualificacao varchar(100) not null,
  contrato_vigente varchar(100) not null,
  qualificante varchar(100) not null,
  idfornecedor INT,
  unique foreign key(idfornecedor) references tbfornecedor(idfornecedor)
);

    Changes:

    1. In correct syntax status column should be created as a generated column based on data_validade column,
    2. Removed not null expression from status.
    3. Moved status column before the service_campo column.

    Could you please confirm if it worked for you!!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search