Please help with correcting some quick SQL syntax, inner join on new column.

Question

Ok so say I Col1,Col2, and COl3 exist in MyTable. However, [Interval] does not.

Select Col1,Col2,Col3, [Interval] = CASE
WHEN (cast(segstart as float) - floor(cast(segstart as float))) >= (cast(@TweleveAM as float) - floor(cast(@TweleveAM as float))) THEN CAST('0' as smallint) End
FROM MyTable

But now I want to use the new column I made in a join like so

Inner Join NewTable New on Interval = New.starttime

How do I do this? I can't seem to find the correct syntax

Answer 1

Most RMDBSs don't allow you to use column aliases within the main body of the immediate SQL statement. Some of them allow it in GROUP BY and HAVING clauses.

You can get around it by using a subquery, but that may cause performance issues. Your best bet is to just repeat the equation.

In case you're interested, the subquery method would look like this:

SELECT
    col1,
    col2,
    col3,
    Interval
FROM
(
    SELECT
        col1,
        col2,
        col3,
        CASE
            WHEN CAST(segstart AS FLOAT) - FLOOR(CAST(segstart AS FLOAT)) >=
                 CAST(@TweleveAM AS FLOAT) - FLOOR(CAST(@TweleveAM AS FLOAT))
                 THEN CAST(0 AS SMALLINT)
        END AS interval
    FROM
        My_Table
) AS SQ
INNER JOIN New_Table NEW ON
    NEW.start_time = SQ.Interval

Another option would be to use a User Defined Function (if you are using MS SQL Server) or any equivalent in your RDBMS. Keep in mind that there could be performance issues there as well, so be sure to test it for performance. It would let you keep the equation all in one place though.

Answer 2

Assuming that your SQL engine can handle Common Table Expressions, then

With myCTE (col1, col2, col3, Interval) AS 
(
Select Col1,Col2,Col3, 
     CASE  WHEN (cast(segstart as float) - floor(cast(segstart as float))) >= 
                (cast(@TweleveAM as float) - floor(cast(@TweleveAM as float))) 
       THEN CAST('0' as smallint) 
       ELSE NULL /* Placeholder, A case when you have only 1 result? */
     End AS [Interval]  
FROM MyTable
)
Select *
FROM myCTE 
   INNER JOIN  NewTable New 
     ON Interval = New.starttime