1

Topic: Request simplification (grouping on conditions)

I welcome!
Whether probably to reduce the given request and to get rid from UNION'?

SELECT
TRUNC (s. ACTIVATION_DATE, ' MM ') AS ACTIVATION_DATE;
' Offices of sales ' AS DEALER_NAME;
COUNT (ph. MSISDN) AS KOLVO
FROM
SUBS_HISTORY sh;
TARIFF_PLAN tp;
CONTRACT ct;
DEALER dl;
PHONE ph;
SUBSCRIBER s
WHERE
s. SUBS_ID = sh. SUBS_ID
AND ph. PHONE_ID = sh. PHONE_ID
AND sh. TRPL_ID = tp. TRPL_ID
AND ct. CLNT_ID = sh. CLNT_ID
AND ct. DLR_ID = dl. DLR_ID
AND dl. DLR_ID IN (4,5,57,60,94,158,161,214,219,232,233,244,293,309,560,562,590,591)
AND s. ACTIVATION_DATE IS NOT NULL
AND s. ACTIVATION_DATE BETWEEN sh. STIME AND sh. ETIME - 1/86400
AND s. ACTIVATION_DATE BETWEEN ct. STIME AND ct. ETIME - 1/86400
AND s. ACTIVATION_DATE> = TRUNC (:DATE1)
AND s. ACTIVATION_DATE <= TRUNC (:DATE2) + 1 - 1 / 86400
GROUP BY TRUNC (s. ACTIVATION_DATE, ' MM ')
UNION ALL
SELECT
TRUNC (s. ACTIVATION_DATE, ' MM ') AS ACTIVATION_DATE;
' Own dealers ' AS DEALER_NAME;
COUNT (ph. MSISDN) AS KOLVO
FROM
SUBS_HISTORY sh;
TARIFF_PLAN tp;
CONTRACT ct;
DEALER dl;
PHONE ph;
SUBSCRIBER s
WHERE
s. SUBS_ID = sh. SUBS_ID
AND ph. PHONE_ID = sh. PHONE_ID
AND sh. TRPL_ID = tp. TRPL_ID
AND ct. CLNT_ID = sh. CLNT_ID
AND ct. DLR_ID = dl. DLR_ID
AND dl. DLR_ID IN (301,302)
AND s. ACTIVATION_DATE IS NOT NULL
AND s. ACTIVATION_DATE BETWEEN sh. STIME AND sh. ETIME - 1/86400
AND s. ACTIVATION_DATE BETWEEN ct. STIME AND ct. ETIME - 1/86400
AND s. ACTIVATION_DATE> = TRUNC (:DATE1)
AND s. ACTIVATION_DATE <= TRUNC (:DATE2) + 1 - 1 / 86400
GROUP BY TRUNC (s. ACTIVATION_DATE, ' MM ')
UNION ALL
SELECT
TRUNC (s. ACTIVATION_DATE, ' MM ') AS ACTIVATION_DATE;
' Implementation on Psou ' AS DEALER_NAME;
COUNT (ph. MSISDN) AS KOLVO
FROM
SUBS_HISTORY sh;
TARIFF_PLAN tp;
CONTRACT ct;
DEALER dl;
PHONE ph;
SUBSCRIBER s
WHERE
s. SUBS_ID = sh. SUBS_ID
AND ph. PHONE_ID = sh. PHONE_ID
AND sh. TRPL_ID = tp. TRPL_ID
AND ct. CLNT_ID = sh. CLNT_ID
AND ct. DLR_ID = dl. DLR_ID
AND dl. DLR_ID IN (561)
AND s. ACTIVATION_DATE IS NOT NULL
AND s. ACTIVATION_DATE BETWEEN sh. STIME AND sh. ETIME - 1/86400
AND s. ACTIVATION_DATE BETWEEN ct. STIME AND ct. ETIME - 1/86400
AND s. ACTIVATION_DATE> = TRUNC (:DATE1)
AND s. ACTIVATION_DATE <= TRUNC (:DATE2) + 1 - 1 / 86400
GROUP BY TRUNC (s. ACTIVATION_DATE, ' MM ')
UNION ALL
SELECT
TRUNC (s. ACTIVATION_DATE, ' MM ') AS ACTIVATION_DATE;
' development Department ' AS DEALER_NAME;
COUNT (ph. MSISDN) AS KOLVO
FROM
SUBS_HISTORY sh;
TARIFF_PLAN tp;
CONTRACT ct;
DEALER dl;
PHONE ph;
SUBSCRIBER s
WHERE
s. SUBS_ID = sh. SUBS_ID
AND ph. PHONE_ID = sh. PHONE_ID
AND sh. TRPL_ID = tp. TRPL_ID
AND ct. CLNT_ID = sh. CLNT_ID
AND ct. DLR_ID = dl. DLR_ID
AND (dl. DLR_ID IN (285,677)
OR dl. DEP_DLR_ID = 285)
AND s. ACTIVATION_DATE IS NOT NULL
AND s. ACTIVATION_DATE BETWEEN sh. STIME AND sh. ETIME - 1/86400
AND s. ACTIVATION_DATE BETWEEN ct. STIME AND ct. ETIME - 1/86400
AND s. ACTIVATION_DATE> = TRUNC (:DATE1)
AND s. ACTIVATION_DATE <= TRUNC (:DATE2) + 1 - 1 / 86400
GROUP BY TRUNC (s. ACTIVATION_DATE, ' MM ')
UNION ALL
SELECT
TRUNC (s. ACTIVATION_DATE, ' MM ') AS ACTIVATION_DATE;
' Dealers ' AS DEALER_NAME;
COUNT (ph. MSISDN) AS KOLVO
FROM
SUBS_HISTORY sh;
TARIFF_PLAN tp;
CONTRACT ct;
DEALER dl;
PHONE ph;
SUBSCRIBER s
WHERE
s. SUBS_ID = sh. SUBS_ID
AND ph. PHONE_ID = sh. PHONE_ID
AND sh. TRPL_ID = tp. TRPL_ID
AND ct. CLNT_ID = sh. CLNT_ID
AND ct. DLR_ID = dl. DLR_ID
AND dl. DLR_ID NOT IN (4,5,57,60,94,158,161,214,219,232,233,244,293,309,560,562,590,591;
301,302,561,285,677)
AND (dl. DEP_DLR_ID NOT IN (285) or dl. DEP_DLR_ID IS NULL)
AND s. ACTIVATION_DATE IS NOT NULL
AND s. ACTIVATION_DATE BETWEEN sh. STIME AND sh. ETIME - 1/86400
AND s. ACTIVATION_DATE BETWEEN ct. STIME AND ct. ETIME - 1/86400
AND s. ACTIVATION_DATE> = TRUNC (:DATE1)
AND s. ACTIVATION_DATE <= TRUNC (:DATE2) + 1 - 1 / 86400
GROUP BY TRUNC (s. ACTIVATION_DATE, ' MM ')

Tried CASE', swears on grouping.

2

Re: Request simplification (grouping on conditions)

Show as tried

3

Re: Request simplification (grouping on conditions)

982183 wrote:

Show as tried

Here so:

SELECT
TRUNC (s. ACTIVATION_DATE, ' MM ') AS ACTIVATION_DATE;
(CASE
WHEN dl. DLR_ID IN (4,5,57,60,94,158,161,214,219,232,233,244,293,309,560,562,590,591) THEN COUNT (ph. MSISDN) END) AS "Offices of sales";
(CASE WHEN dl. DLR_ID IN (301,302) THEN COUNT (ph. MSISDN) END) AS "Own dealers"
FROM
SUBS_HISTORY sh;
TARIFF_PLAN tp;
CONTRACT ct;
DEALER dl;
PHONE ph;
SUBSCRIBER s
WHERE
s. SUBS_ID = sh. SUBS_ID
AND ph. PHONE_ID = sh. PHONE_ID
AND sh. TRPL_ID = tp. TRPL_ID
AND ct. CLNT_ID = sh. CLNT_ID
AND ct. DLR_ID = dl. DLR_ID
AND s. ACTIVATION_DATE IS NOT NULL
AND s. ACTIVATION_DATE BETWEEN sh. STIME AND sh. ETIME - 1/86400
AND s. ACTIVATION_DATE BETWEEN ct. STIME AND ct. ETIME - 1/86400
AND s. ACTIVATION_DATE> = TRUNC (:DATE1)
AND s. ACTIVATION_DATE <= TRUNC (:DATE2) + 1 - 1 / 86400
GROUP BY TRUNC (s. ACTIVATION_DATE, ' MM ')

Swears on ct. DLR_ID

4

Re: Request simplification (grouping on conditions)

[quote =-2] it is passed...
group by CASE' on dl. DLR_ID IN (...) or make the reference manual and add .

I will try so.