Within a select are calculated columns allowed to use other calculated columns?

[Former Member]

Someone sent me a query that contained the following:

     /* Gets the reading_id of the last reading 30 days 
        ago - this is for quick lookups of other data 30 days ago */
     (SELECT  top 1 readings_id
     FROM     readings
     WHERE    meter_miu_id  = readings_miu_id
     AND site_id = 36801
       AND device_type = 'R450'
     AND      readings_date < now(*) - 30
     ORDER BY readings_date DESC
     ) AS Day30_id ,

/* Gets the profile  30 days ago*/ 
     (SELECT readings_status
     FROM    readings
     WHERE   readings_id = Day30_id
     )                                              AS Day30_Status ,
     IF Day30_Status > 1 THEN 'On' ELSE 'Off' ENDIF AS ProfileDay30 ,

Are you allowed to use the calculated columns in other calculated columns? Are you guaranteed that it fills in the values in the select column order? The select works, but I'm not sure I trust the data.

[Former Member · Accepted solution]

The short answer is yes. The server will evaluate the various expressions in the correct dependency order, if dependencies between them exist. If at build time a dependency cycle is encountered, the statement will get an error.

Hence this statement:

select EmployeeID as X, X + X as Y from Employees

works, but this statement

select EmployeeID as X, X as EmployeeID from Employees

gets SQLCODE -831.

[Breck_Carter]

In addition to what Glenn said... SQL Anywhere is like a spreadsheet, it doesn't matter what order the expressions are in the cells, er, select list.

Plus, you can refer to them in WHERE clauses, ORDER BY, etc...

SELECT y + x AS z, x + 1 AS y, 47 * 52 AS x
 WHERE z > y
 ORDER BY x, y, z;