replicate the value get in a SQL Select

Question

How I can replicate the value of a exact field on the same Select statement?

What I have:

SELECT
  FIELD1,
  FIELD2,
  CASE WHEN FIELD3 <> FIELD4
   THEN CASE WHEN SUBSTRING(FIELD10,1,3) = FIELD5
     THEN (FIELD1 * FIELD3) + FIELD4
     ELSE (FIELD1 / FIELD3) + FIELD4
     END 
   END AS CUSTOM_FIELD1,
-- does the same CASE only to set it to another field.
-- i don't want to make the same case again, I want it to be smarter and use the same value as CUSTOM_FIELD1 without needing to calculate again.
  CASE WHEN FIELD3 <> FIELD4
   THEN CASE WHEN SUBSTRING(FIELD10,1,3) = FIELD5
     THEN (FIELD1 * FIELD3) + FIELD4
     ELSE (FIELD1 / FIELD3) + FIELD4
     END 
   END AS CUSTOM_FIELD2

I bet there is a way to make this code smarter and cleaner, I just don't know how.

score 3 · Accepted Answer · answered Sep 13 '11 at 18:06

3

 SELECT
    FIELD1,
    FIELD2,
    CUSTOM_FIELD1,
    CUSTOM_FIELD1 AS CUSTOM_FIELD2
    FROM
    (
    SELECT
      FIELD1,
      FIELD2,
      CASE WHEN FIELD3 <> FIELD4
       THEN CASE WHEN SUBSTRING(FIELD10,1,3) = FIELD5
         THEN (FIELD1 * FIELD3) + FIELD4
         ELSE (FIELD1 / FIELD3) + FIELD4
         END 
       END AS CUSTOM_FIELD1
     FROM TABLE
) AS SUB

answered Sep 13 '11 at 18:06

Wil

4,130
1
16
15

for some reason, when I try to implement this in my real SQL, each result comes twice. I tried your solution in a very small SQL and it worked, I don't know why it doesn't work on mine. – bitlamas Sep 13 '11 at 18:57
Add DISTINCT to the inner SELECT. Otherwise, can you post the entire query that you are running? – Wil Sep 13 '11 at 19:00

replicate the value get in a SQL Select

1 Answers1