SQL select statement syntax issue

Question

I am getting an error with the below code, apologies this is probably a very simplistic question but i am really nor understanding why it is not working.

I am getting a "Line 9, Column 1 Incorrect Syntax near 'from'" error.

select distinct
b.Identifier,
(b.Low+ b.High) / 2 as bAverage,
m.Average as mAverage,
coalesce(
case when mAverage not null then ((((b.Low + b.High) / 2) + m.Average) / 2) else null end,
case when mAverage is null then ((b.Low + b.High) / 2)) end as TotalAvg,
(b.Volume + m.Qty) as TotalVolume
from table_b_data b
full outer join table_m_data m on b.Identifier=m.Identifier

Gordon Linoff · Accepted Answer · 2021-08-06 11:43:44Z

2

Your coalesce() doesn't have a closing paren. I might write this as:

coalesce( m.average / 4 + (b.Low + b.High) / 4,
          (b.Low + b.High) / 2
        ) as TotalAvg,

The case is not necessary, because the arithmetic expression will return NULL. I just rephrased the arithmetic (hopefully correctly!) to reduce the number of parentheses.

answered Aug 6, 2021 at 11:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

st87_top Over a year ago

thanks - this works when simplifying and removing the case when. for some reason it worked for me once with the case when, then i inserted the 8th row and it started playing up. But now works OK - cheers.

Gordon Linoff Over a year ago

@st87_top . . . When you modified the code, you seem to have deleted a closing paren.

Collectives™ on Stack Overflow

SQL select statement syntax issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related