patternsqlMinor
Remove duplication in SELECT statement
Viewed 0 times
removeduplicationselectstatement
Problem
Say I have the following SQL:
Instead of repeating that identical code every time, I really want to be able to do something like this:
But this code doesn't work.
So, is there another way to avoid duplication in the working query that I have?
SELECT
amount,
amount*.1,
(amount*1)+3,
((amount*1)+3)/2,
(((amount*1)+3)/2)+37
FROM tableInstead 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 tableBut this code doesn't work.
So, is there another way to avoid duplication in the working query that I have?
Solution
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;Code Snippets
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;Context
StackExchange Code Review Q#36485, answer score: 7
Revisions (0)
No revisions yet.