CODEWITHSUNDEEP
mysqlsql
IKnowHowBitcoinWorks
IKnowHowBitcoinWorks

Reputation: 349

How to apply conditional values on select with mysql

Is it possible to use the case when function when creating views from joined tables?

t1.daycol     |    t2.daycol    |    t1.v    |    t2.v   |   r
2020-06-30    |  2020-03-30     |    3       |    1      |   2      <<<< r = t1.v - t2.v for t1.daycol and t2.daycol are in the same year
2020-03-30    |  2019-12-30     |    1       |    6      |   1      <<<< r = t1.v for t1.daycol and t2.daycol are not in the same year

I tried to add another column then subtract it later with

create view offperiod as
select t1.*, t2.*, 
case when year(t1.daycol) > year(t2.daycol) then 0 else t2.daycol,
from table1 t1 left join table2 t2
on timestampdiff(day,t2.daycol,t1.daycol) > 0 and timestampdiff(day,t2.daycol,t1.daycol) < 85

But I got some syntax error in this statement.

Is it possible to do that in the line when creating views?

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

This doesn't make sense:

case when t1.daycol.year > t2.daycol.year then 0 else t2.daycol,

in many ways.

Perhaps you intend:

(case when year(t1.daycol) > year(t2.daycol) then 0 else t2.daycol end) as something

Note that the comma is removed, year() is used as a function, and the case expression has an end.

You probably can't select * from both tables -- if columns have the same name, that will cause a problem for the view.

Upvotes: 2

Related Questions