5
\$\begingroup\$

Say I have the following SQL:

SELECT 
    amount,
    amount*.1,
    (amount*1)+3,
    ((amount*1)+3)/2,
    (((amount*1)+3)/2)+37
FROM table

Instead of repeating that identical code every time, I really want to be able to do something like this:

SELECT
    amount,
    amount*.1 AS A,
    A+3 AS B,
    B/2 AS C,
    C+37 AS D,
FROM table

But this code doesn't work.

So, is there another way to avoid duplication in the working query that I have?

\$\endgroup\$
  • \$\begingroup\$ Not with T-SQL - From: technet.microsoft.com/en-us/library/ms176104.aspx ` < select_list > The columns to be selected for the result set.` and A, B, and C are not columns. \$\endgroup\$ – rolfl Dec 2 '13 at 4:42
  • 1
    \$\begingroup\$ FYI: stackoverflow.com/questions/12988637/column-aliases-reference \$\endgroup\$ – rolfl Dec 2 '13 at 4:55
  • \$\begingroup\$ Is something like this possible? and the 'this' code does not work. If you are specifically concerned that it was closed 'because it was asking for code to be written', well, it is also required that the code-to-be-reviewed also works: See question 5 here: codereview.stackexchange.com/help/on-topic \$\endgroup\$ – rolfl Dec 2 '13 at 5:28
  • \$\begingroup\$ How are the results from this query used? The calculations look like business logic which belong in a domain object rather than inserted directly into a query. I would only put them in the query if I had no other choice (e.g., building reports). \$\endgroup\$ – Dan Lyons Dec 2 '13 at 18:35
  • \$\begingroup\$ In this case, I'm just generating tablular data to be imported into a separate system. There is no domain object. Otherwise, I agree with you. I wouldn't normally want to do this kind of stuf in SQL. \$\endgroup\$ – brainbolt Dec 2 '13 at 21:40
6
\$\begingroup\$

That's not how SQL works. As mentioned in the SO answer linked by @rolfl:

You can only refer to a column alias in an outer select, so unless you recalculate all the previous values for each column you'd need to nest each level, which is a bit ugly

Which means:

SELECT amount, A, B, C, C+37 D
FROM (SELECT amount, A, B, B/2 C
      FROM (SELECT amount, A, A+3 B
            FROM (SELECT amount, amount*0.1 A FROM table) firstPass) secondPass) thirdPass

That said, the last comma in your 2nd snippet is a pseudo syntax error (given it's a pseudo query)

\$\endgroup\$
7
\$\begingroup\$

You could define intermediate views using common table expressions. That would eliminate the redundancy of the calculations, but redundantly introduce a different kind of redundancy.

WITH ATable AS (
    SELECT amount, amount*.1 AS A FROM table
), BTable AS (
    SELECT amount, A, A+3 AS B FROM ATable
), CTable AS (
    SELECT amount, A, B, B/2 AS C FROM BTable
)
SELECT amount, A, B, C, C+37 AS D FROM CTable;
\$\endgroup\$
  • 2
    \$\begingroup\$ “redundantly introduce a different kind of redundancy.” Isn't that “redundantly” redundant? \$\endgroup\$ – svick Dec 2 '13 at 17:47
  • \$\begingroup\$ you said something about the redundancy in your answer and then redundantly commented "“redundantly introduce a different kind of redundancy.” Isn't that “redundantly” redundant?" on the Redundancy of the statement. \$\endgroup\$ – Malachi Dec 2 '13 at 17:51
3
\$\begingroup\$

If this is SQL Server 2005 or later version, you could use CROSS APPLY:

SELECT
    t.amount,
    a.A,
    b.B,
    c.C,
    d.D
FROM atable AS t
CROSS APPLY (SELECT t.amount*.1 AS A) AS a
CROSS APPLY (SELECT a.A+3       AS B) AS b
CROSS APPLY (SELECT b.B/2       AS C) AS c
CROSS APPLY (SELECT c.C+37      AS D) AS d
;

Essentially, every CROSS APPLY here creates a calculated column which can be referenced in subsequent CROSS APPLYs just as well as in the SELECT clause, i.e. this way you are creating re-usable calculated columns.

If this is an older version or Sybase, there's probably no other option apart from the one suggested by @retailcoder.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.