0

I have trouble with my computed column for calculate automatically age. I use latest version of Laragon, with HeidiSQL (11.2.06213).

See the request :

    CREATE TABLE client(
        id_client             Int  Auto_increment  NOT NULL ,
        
        client_date_naissance Date NOT NULL ,
       
        age    Int AS YEAR(NOW) - YEAR(client_date_naissance) 
    - (CASE WHEN MONTH(client_date_naissance) > MONTH(NOW) 
OR (MONTH(@client_date_naissance) = MONTH(NOW) 
AND DAY(client_date_naissance) > DAY(NOW)) THEN 1 ELSE END);
    
    ,CONSTRAINT client_PK PRIMARY KEY (id_client)
    )ENGINE=InnoDB;

I tried with "currendate" instead of "NOW", same with supressed the "int" for age.

In SQL's doc, i found this "TIMESTAMPDIFF(YEAR,birth,CURDATE()) " but doesn't work.

I precise , the meaning of "client_date_naissance" (french) is equal to birthday.

The error, for all case is :

Erreur SQL (1064) : 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 'YEAR(NOW) - YEAR(client_date_naissance) - (CASE WHEN MONTH(client_date_naissance'

Any ideas ? Thank for support.

jeremy
  • 23
  • 6
  • Debugging tip: try a simpler condition in your CASE/WHEN and build up to what you have. You may need additional parentheses. Also, shouldn't the ELSE return an actual value since you're subtracting it from something? – Barry Carter Jul 28 '22 at 16:52
  • http://www.noelshack.com/2022-30-4-1659027453-jer.png Same with a simple request. – jeremy Jul 28 '22 at 16:57
  • DATEDIFF only takes two arguments. As https://www.heidisql.com/forum.php?t=536 notes, you have to divide to get some other unit. – Barry Carter Jul 28 '22 at 17:02
  • Arf, same in php my admin... Is it possible what i want to do unless ? I found the concept of computed column attractive... – jeremy Jul 28 '22 at 17:56
  • Does HeidiSQL support VIEWs? That's where you'd get computed columns – Barry Carter Jul 28 '22 at 18:08
  • I don't think so... I didn't see VIEWS on their forum... – jeremy Jul 29 '22 at 06:43
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246885/discussion-between-barry-carter-and-jeremy). – Barry Carter Jul 29 '22 at 12:51

0 Answers0