HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Remove duplication in SELECT statement

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
removeduplicationselectstatement

Problem

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?

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.