Member Avatar for fcaserio

I'm dealing with queries that manages a lot of data consolidation and I'm having to repeat large blocks of code to get the desired result.
I'm wondering if that is any way to make it simple.

My queries looks like this:

SELECT
  (A + B) AS C,
  (D + E) AS F,
  (G + H) AS I,
  (A + B) * (D + E) AS total1,
  (D + E) * (G + H) AS total2,
  (A + B) * (G + H) AS total3,
  (A + B) * (D + E) * (G + H) AS total
FROM table

Can I somehow declare each part of the expression and make it something like this?

SELECT
  (A + B) AS C,
  (D + E) AS F,
  (G + H) AS I,
  C * F AS total1,
  F * I AS total2,
  C * I AS total3,
  C * F * I AS total
FROM table

Maybe it seems silly, but each sub-expression, on the real life, includes a lot of CASEs and aggragate functions and my codes are getting complex and confuse.

Since I have lots of code pieces that I repeat more than once, I would like to make it simpler.

If anyvody knows how to help me I appreciate!!!

  • 3 Contributors
  • 2 Replies
  • 63 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by hollystyles
Member Avatar for space1000

have a look into a function. I use this a lot for calculated work.

Member Avatar for hollystyles

You can do it with a sub query:

SELECT 
	C,
	F,
	I,
  	C * F AS total1,
  	F * I AS total2,
  	C * I AS total3,
 	C * F * I AS total 
FROM (
	SELECT
 		(A + B) AS C,
		(D + E) AS F,
		(G + H) AS I
	FROM 
		table 
	) t
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.