problem with select statement 1

[Guest_imported]

Hi everyone,

I've a little problem with a select statement ... perhaps anyone can help me.

I've a statement that is similar to the statement below:

SELECT
table1.column1,
sum(<very:long_expression1>) bla1,
sum(<very:long_expression2>) bla2
FROM
... several tables ...
WHERE
... several contraints ...
GROUP BY
table1.column1

Now I would like to have the sum of the both returned sums:

SELECT
table1.column1,
sum(<very:long_expression1>) bla1,
sum(<very:long_expression2>) bla2,
(bla1 + bla2) bla3 -- here an error occures
FROM
... several tables ...
WHERE
... several contraints ...
GROUP BY
table1.column1

Does anyone know how to fix that?

 

[r937]

create a view which assigns a column name to the very long expressions, then you can write

SELECT
table1.column1,
sum(bla1),
sum(bla2),
sum(bla1 + bla2)

otherwise, i think you will have to repeat the very long expressions where you currently are trying to use the aliases

rudy

http://rudy.ca/

 

[barbarul2001]

You could try this:
SELECT
table1.column1,
sum(<very:long_expression1>) bla1,
sum(<very:long_expression2>) bla2,
sum(<very:long_expression2> + <very:long_expression2>) bla3 -- here an error occures
FROM
... several tables ...
WHERE
... several contraints ...
GROUP BY
table1.column1

good luck
web/sql developer

 

[angiole]

Alternatley, you may also try

Select column1
       bla1,
       bla2,
       bla1+bla2 as bla3
From   (
       SELECT table1.column1,
              sum(<very:long_expression1>) bla1,
              sum(<very:long_expression2>) bla2
       FROM
       ... several tables ...
       WHERE
       ... several contraints ...
       GROUP BY
             table1.column1)

This way, you only need to specify the long expressions once.




AA 8~)

 

[angiole]

Too bad I can't edit my own post, but to show the flexibility of the previous, consider this stretch of the imagination...

Select column1
       bla1,
       bla2,
       bla1+bla2 as bla3,
       bla1/bla2 as bla4,
       bla1*bla2 as bla5,
       bla1-bla2 as bla6,
       (bla1*3.14159)/(bla2*bla2) as bla7
       etc etc
From   (
       SELECT table1.column1,
              sum(<very:long_expression1>) bla1,
              sum(<very:long_expression2>) bla2
       FROM
       ... several tables ...
       WHERE
       ... several contraints ...
       GROUP BY
             table1.column1)

Again, showing you only need to build the complicated part of the expression once.

AA 8~)

 

[Guest_imported]

Thank you all for your answers...

... but I think I've to go with the &quot;<very:long_expressions>&quot;.

The example I posted is only a small part of a very huge SQL statement. I tried to solved the problem by utilise your hints but there occured some not wanted side effects.

Thank you anyway for your efforts

 

[angiole]

Like? AA 8~)

 

[r937]

yeah, &quot;not wanted side effects&quot; like what, exactly?

rudy