1

I'm trying to call this calculated column 'RelativeEffectiveSpreadAbsoluteValue' in SQL servers' FROM part.

, case when cast(sa.Mid_Price as float)  = 0
           then  0
           else ((CAST(sa.Ask_Price as float)-cast(sa.Bid_Price as float))/CAST(sa.Mid_Price as float))/(0.01/100)
            end  As RelativeEffectiveSpreadAbsoluteValue

like this, but the SQL server won't recognize it

  left join [RISK].[dbo].[FILiquidityBuckets] FB6
ON FB6.Metric = 'Relative spread ' AND (
   ((CAST(RelativeEffectiveSpreadAbsoluteValue AS FLOAT)>= 0 AND CAST(RelativeEffectiveSpreadAbsoluteValue AS FLOAT)< 1000000) AND
    FB6.LiquidityScore = 5) OR
    ((CAST(RelativeEffectiveSpreadAbsoluteValue AS FLOAT)>= 1000000 AND CAST(RelativeEffectiveSpreadAbsoluteValue AS FLOAT)<10000000) AND
    FB6.LiquidityScore = 4) OR
    ((CAST(RelativeEffectiveSpreadAbsoluteValue AS FLOAT)>= 10000000 AND CAST(RelativeEffectiveSpreadAbsoluteValue AS FLOAT)< 100000000) AND
    FB6.LiquidityScore = 3) OR
    ((CAST(RelativeEffectiveSpreadAbsoluteValue AS FLOAT)>= 100000000 AND CAST(RelativeEffectiveSpreadAbsoluteValue AS FLOAT)<1000000000) AND
    FB6.LiquidityScore = 2) OR
    (CAST(RelativeEffectiveSpreadAbsoluteValue AS FLOAT) >= 1000000000 AND F65.LiquidityScore = 1)

)

So far I know by using 'Cross Apply' a calculated column can calculate another column in the same view, like this example

  Select
  ColumnA,
  ColumnB,
  c.calccolumn1 As calccolumn1,
  c.calccolumn1 / ColumnC As calccolumn2
  from t42
  cross apply (select (ColumnA + ColumnB) as calccolumn1) as c

but this is only for the select part, can we use it in the From part? Please help thank you!

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
Kenny smith
  • 27
  • 4
  • select `*` and that expression `from sa` in a CTE, then select/join the rest from said CTE. the CTE will make the aliased expression usable by name in further queries. – underscore_d Jul 15 '20 at 14:57
  • Does this answer your question? [Referring to a Column Alias in a WHERE Clause](https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) – Eric Brandt Jul 15 '20 at 15:08
  • Why the casts? Are you storing the values as strings instead of floats? In that case you should fix the table and use a numeric type – Panagiotis Kanavos Jul 15 '20 at 15:12
  • As for the actual error, `SELECT` occurs *after* the `FROM` and `JOIN` phases, so you can't use a calculated column in a JOIN. You'd have to use a CTE or subquery to calculate those values and JOIN the CTE or subquery with any other tables. That won't be very effective though, as the server would have to calculate the formulas for *all* rows before using them in the JOIN condition to decide which rows to include or exclude – Panagiotis Kanavos Jul 15 '20 at 15:14

1 Answers1

1

Put the apply operation which does the calculation prior to the join in your query:

create table t(a int);
create table u(b int);

select      t.a,
            t2.calculatedColumn,
            u.b
from        t 
cross apply (select t.a * 2) as t2 (calculatedColumn)
left join   u on u.b = t2.calculatedColumn

As Panagiotis observed, this may result in a slow join because the join predicate will not be able to use an index. But if the nature of your query demands it, the language supports it.

If you need this to be fast, create an indexed computed column on the table you have aliased as sa instead of calculating it in the query. Since your column will be of type float, you will need to mark the computed column as persisted before you can index it.

allmhuran
  • 4,154
  • 1
  • 8
  • 27