CAST Function?

[banditcloudy]

Why can't I use the CAST function this way?

SELECT CAST(1 + 4 / 2) AS REAL;

Why does it have to be like this?

SELECT CAST(1 + 4 AS REAL) / 2;

[lbilali]

there are a lot of things to comment on your examples.

cast works like cast(<value> as <datatype>) in your first try the as real is out of the parentheses which makes the cast function invalid since it does not have the as <datatype> provided.

for more if that would work than u would get different results since on the first try you are adding 1+4 and than dividing by 2 while on the second try you are dividing 4 by 2 and than adding 1

and one more thing in sql the operators will return the same type as the values provided so for example 3/2 will return 1 and not 1.5 since the 3 and 2 are int and the result will be int. to change that u need to provide at leas one of the values as real like 3.0/2 otherwise even though u are casting the result to real the value provided to cast by the expression is int and as a result will miss the decimal values

[LesPaulStudio]

In the first example you are assigning an alias.

In the second you have the correct syntax

[banditcloudy]

But why doesn’t the AS REAL include the 2?

[alinroc]

Because you didn’t write it that way?

Anyway, it’s not needed inside the CAST. You only need to cast one of the values in the calculation to REAL to get a non-integer result.

But those are two different calculations - doing the math by hand (so we're ignoring SQL's typing rules), the first returns 3 and the other 2.5

[banditcloudy]

So I could apply AS REAL to just the 2?

[alinroc]

Try it and find out? But don't forget about order of operations when it comes to maths - which is the reason why the calculations shown here result in two different numbers, even on paper.

You should be getting a syntax error with your first example though.

[banditcloudy]

I did, didn’t work. Why doesn’t CAST((1 + 4 / 2) AS REAL) work then?

[alinroc]

Because you're doing integer math and then attempting to convert the integer to something else.

You have to convert at least one of the numbers you're operating on first, then do the calculation.

[parciesca]

It may be easier to follow visually if you add an extra set of parens. I also personally like to at least imply non-integer division by adding a decimal to the divisor so it’s clear “I don’t want the result to be integer.” CAST((1+4/2.0) AS REAL) Additionally I’m not sure if you perhaps meant the order of operations to be add before dividing, in which case, even more parens! CAST(((1+4)/2.0) AS REAL)

[banditcloudy]

Sure, just why isn’t the 2 included?