Re: How to refer to computed columns from other computed columns?

Am 16.08.2010 14:45, schrieb Matthew Wilson:
> I'm converting some procedural code to SQL as an experiment.  Here's the
> pseudocode:
>
>      c = a - b
>      if c<  0 then d = 'no'
>      else d = 'yes'
>
> In SQL, I've got this:
>
>      select a, b, a - b as c,
>      case when a - b<  0 then 'no'
>      else 'yes'
>      end as d
>
>      from foo;
>
> This is a trivial example, but you can see how I calculate a - b two
> separate times.
>
> In reality, I have much nastier calculations and they happen more than
> just twice.
>
> I'm looking for an elegant solution for this puzzle.  I don't want to
> repeat that a - b part over and over because I likely will need to
> change how c gets defined and I don't want to have to change more than
> one place in the code.
>
> All I can come up with so far is to use a view and then another view on
> top of that one:
>
>      create view v1 as
>      select a, b, a - b as c
>      from foo;
>
>      create view v2 as
>      select a, b, c,
>      case when c<  0 then 'no'
>      else 'yes'
>      end as d
>      from v1;
>
> This is better than the first solution because c is only defined in a
> single place.  Is this the best possible solution?
>
> Thanks for the help.
>
> Matt
>
>
>
You can also use the ' with Queries ' option to solve this Problem like
this:

with table_1 as (select a,b, a-b as c from foo)
Select a,b, c,
case when c<0 then 'no'
          else 'yes' end as d
          from table_1;
I hope , it will help you

--
Eric Ndengang
Junior Datenbankentwickler

Affinitas GmbH  |  Kohlfurter Straße 41/43  |  10999 Berlin  |  Germany
email: eric.ndengang_foyet@affinitas.de  | tel: +49.(0)30. 991 949 5 0  |  www.edarling.de

Geschäftsführer: Lukas Brosseder, David Khalil, Kai Rieke, Christian Vollmann
Eingetragen beim Amtsgericht Berlin, HRB 115958