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.

asked 18 Jan '10, 17:00

Jon%20Greisz's gravatar image

Jon Greisz
571979
accept rate: 0%


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.

permanent link

answered 18 Jan '10, 21:23

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

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;
permanent link

answered 19 Jan '10, 10:10

Breck%20Carter's gravatar image

Breck Carter
25.8k428592853
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90

question asked: 18 Jan '10, 17:00

question was seen: 986 times

last updated: 19 Jan '10, 10:10