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@xxxxxxxxxxxx | 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
