CODEWITHSUNDEEP
mysqlsqlpdo
gator
gator

Reputation: 3523

Define column name as another name in SQL?

I have a query as below:

SELECT value
    FROM table
    WHERE ((col1+col2+col3)/col4) > 4
    AND ((col1+col2+col3)/col4) < 5
UNION ALL
SELECT value
    FROM table
    WHERE ((col1+col2+col3)/col4) > 3
    AND ((col1+col2+col3)/col4) < 4

I'd like to define ((col1+col2+col3)/col4) as some name, so if I want to change the conditions of the SQL query (eg. maybe I'd like it to be ((col1+col2-col3)*col4) instead), I don't need to go through the entire text of the SQL query and change every line.

DEFINE ((col1+col2+col3)/col4) AS var
SELECT value
    FROM table
    WHERE var > 4
    AND var < 5
UNION ALL
SELECT value
    FROM table
    WHERE var > 3
    AND var < 4

Only one line needs to be altered instead of four in this example. It's easier to read and maintain.

Upvotes: 0

Views: 201

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You can do the union all before the definition:

SELECT ((col1+col2+col3)/col4) as value
FROM ((SELECT col1, col2, col3, col4
       FROM table1
      ) UNION ALL
      (SELECT col1, col2, col3, col4
       FROM table2
      )
     ) t
HAVING value > 4 and value < 5

This use of the having clause is specific to MySQL. You would need another subquery or CTE for another database.

Upvotes: 2

Joy Acharya
Joy Acharya

Reputation: 680

   SELECT tt.value FROM 
   (SELECT ((t.col1+t.col2+t.col3)/t.col4) as value
      FROM ((SELECT col1, col2, col3, col4
               FROM #Temp
            ) UNION ALL
            (SELECT col1, col2, col3, col4
               FROM #Temp
            )

           ) t)tt
GROUP BY tt.value
HAVING tt.value > 4 and tt.value < 5

Upvotes: 2

Ana C.
Ana C.

Reputation: 43

In MySQL:

"User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT. This is true even if the variable is quoted[...]"

Link:https://dev.mysql.com/doc/refman/5.0/en/user-variables.html

That said, you may construct and execute the query as follows:

SET @var=((col1+col2+col3)/col4);
SET @s=CONCAT("SELECT value FROM table WHERE ", @var," > 4 AND ", @var,
" < 5 UNION ALL SELECT value FROM table WHERE ", @var," > 3 AND ", @var,
" < 4");
PREPARE stmt FROM @s;
EXECUTE stmt;

However, this probably defeats the purpose of making it easier to read or maintain.

Upvotes: 0

Related Questions