Please consider the following query logic:
-
I am pulling data out of a cube that only permits one parameter for the "SCOPE" dimension in each SELECT statement. As such, I perform a UNION on two separate SELECT statements with different SCOPE attributes as a derived table with the alias "combined".
-
I then perform an aggregation on the "combined" table with the alias "pvt".
-
I then calculate the difference between the aggregated values in "pvt" and perform a UNION on one of the original SELECT statements in step 1 above. However, I have to duplicate the SELECT statement that I wrote in step 1.
Please see the following SQL statement as described above:
SELECT
"ACCOUNT"
,'U' AS ConsolID
,"SIGN_DATA" AS "VALUE"
FROM
_SYS_BIC."CUBE_BPC"
(<CUBE PARAMETERS>)
WHERE
"SCOPE" = 'S_NONE' --DUPLICATE SELECT STATEMENT
--
UNION
--
SELECT
pvt.ACCOUNT
,'E' AS ConsolID
,pvt.c - pvt.u AS "VALUE" --Elimination. Diff betwen consol and unconsol
FROM(
SELECT
combined.ACCOUNT
,SUM(
CASE
WHEN combined.ConsolID = 'U'
THEN combined.VALUE
ELSE 0
END
) AS u
,SUM(
CASE
WHEN combined.ConsolID = 'C'
THEN combined.VALUE
ELSE 0
END
) AS c
FROM(
SELECT
"ACCOUNT"
,'U' AS ConsolID
,"SIGN_DATA" AS "VALUE"
FROM
_SYS_BIC."CUBE_BPC"
(<CUBE PARAMETERS>)
WHERE
"SCOPE" = 'S_NONE' --DUPLICATE SELECT STATEMENT
--
UNION
--
SELECT
"ACCOUNT"
,'C' AS ConsolID
,"SIGN_DATA" AS "VALUE"
FROM
_SYS_BIC."CUBE_BPC"
(<CUBE PARAMETERS>)
WHERE
"SCOPE" = 'S_TOTAL'
) AS combined
GROUP BY
combined.ACCOUNT
) AS pvt
I would like to understand how I can modify the above SQL statement so that I do not have to duplicate the SELECT statement noted above. I attempted to create an alias for each SELECT statement within the "combined" UNION statement and then reference this alias, but this syntax is incorrect. See below:
SELECT * FROM unconsol
--
UNION
--
SELECT
pvt.ACCOUNT
,'E' AS ConsolID
,pvt.c - pvt.u AS "VALUE" --Elimination. Diff betwen consol and unconsol
FROM(
SELECT
combined.ACCOUNT
,SUM(
CASE
WHEN combined.ConsolID = 'U'
THEN combined.VALUE
ELSE 0
END
) AS u
,SUM(
CASE
WHEN combined.ConsolID = 'C'
THEN combined.VALUE
ELSE 0
END
) AS c
FROM(
(
(
SELECT
"ACCOUNT"
,'U' AS ConsolID
,"SIGN_DATA" AS "VALUE"
FROM
_SYS_BIC."CUBE_BPC"
(<CUBE PARAMETERS>)
WHERE
"SCOPE" = 'S_NONE'
) AS unconsol
--
UNION
--
(
SELECT
"ACCOUNT"
,'C' AS ConsolID
,"SIGN_DATA" AS "VALUE"
FROM
_SYS_BIC."CUBE_BPC"
(<CUBE PARAMETERS>)
WHERE
"SCOPE" = 'S_TOTAL'
) AS consol
)
) AS combined
GROUP BY
combined.ACCOUNT
) AS pvt
The reason why I want to avoid duplicating the SELECT statement is because the cube parameters are fairly extensive and I want to make the query as concise as possible to make updates easier.