divisor-by-zero.sql

gistfile1.txt

select 'Qualified pipeline worth ' ||
        '<span class=''xl green''>' ||
        CASE WHEN currency = 'GBP' THEN '&pound;' ELSE '&euro;' END ||
        LTRIM(TO_CHAR(CAST(SUM(CASE WHEN OPPORTUNITY_STAGE != 'Identify' THEN VALUE ELSE 0 end) AS INTEGER), '999G999G999G999')) ||
        '</span> (' ||
        CAST(100*SUM(CASE WHEN OPPORTUNITY_STAGE != 'Identify' THEN VALUE ELSE 0 end)/SUM(VALUE) AS INTEGER) ||
        '% of total), made up of ' ||
        '<span class=''xl blue''>' ||
        LTRIM(TO_CHAR(CAST(SUM(CASE WHEN OPPORTUNITY_STAGE != 'Identify' THEN NUMBER_OF_OPPORTUNITIES end) AS INTEGER), '999G999G999G999')) ||
        '</span>' ||
        ' opportunities. ' ||
        case when SUM(NUMBER_OF_OPPORTUNITIES_STALE) > 0 THEN 'Stale (qualified) opportunities worth ' ||
            '<span class=''xl red''>' ||
            case when currency = 'GBP' THEN '&pound;' ELSE '&euro;' end ||
            ltrim(to_char(CAST(SUM(case when OPPORTUNITY_STAGE != 'Identify' THEN VALUE_STALE ELSE 0 end) AS INTEGER), '999G999G999G999')) ||
            '</span>'
        ELSE ''
        end as summary
FROM
  (SELECT OPPORTUNITY_STAGE,
          CAST(SUM(NUMBER_OF_DEALS) AS INTEGER) AS NUMBER_OF_OPPORTUNITIES,
          CAST(SUM(CASE WHEN STALE_FLAG = 'Fresh' THEN NUMBER_OF_DEALS ELSE 0 END) AS INTEGER) AS NUMBER_OF_OPPORTUNITIES_FRESH,
          CAST(SUM(CASE WHEN STALE_FLAG = 'Stale' THEN NUMBER_OF_DEALS ELSE 0 END) AS INTEGER) AS NUMBER_OF_OPPORTUNITIES_STALE,
          CAST(SUM(CASE WHEN STALE_FLAG = 'Fresh' THEN conversion_rate*VALUE ELSE 0 END) AS INTEGER) AS VALUE_FRESH,
          CAST(SUM(CASE WHEN STALE_FLAG = 'Stale' THEN conversion_rate*VALUE ELSE 0 END) AS INTEGER) AS VALUE_STALE,
          CAST(SUM(conversion_rate*VALUE) AS INTEGER) AS VALUE,
          iso_code currency
   FROM
     (SELECT OPPORTUNITY_STAGE,
             CASE WHEN CLOSE_DATE >= TRUNC(SYSDATE, 'DD') THEN 'Fresh' ELSE 'Stale' END AS STALE_FLAG,
                                                                                           sum(NVL(aov,0)*multiplier) AS VALUE,
                                                                                           COUNT(DISTINCT OPPORTUNITY_ID) AS NUMBER_OF_DEALS,
                                                                                           SORT_ORDER
      FROM DATA_ONE_SF_LOOKUP
      INNER JOIN DATA_CORE_ONE_SF ON "DATA_ONE_SF_LOOKUP"."LINE_ITEM_ID" = DATA_CORE_ONE_SF."LINE_ITEM_ID"
      AND "DATA_ONE_SF_LOOKUP"."EBU" = DATA_CORE_ONE_SF."EBU"
      WHERE (("DATA_CORE_ONE_SF"."MONTH" >= DATE '2017-04-01') AND ("DATA_CORE_ONE_SF"."MONTH" <= DATE '2017-11-28') AND ("DATA_CORE_ONE_SF"."MARKET" IN ('OpCo', '_', 'Partner', 'Off-Footprint')) AND ("DATA_CORE_ONE_SF"."COUNTRY_REGION" IN ('americas', 'asia_pacific', 'central_europe', 'middle_east_and_africa', 'northern_europe', 'southern_europe', 'other')) AND ("DATA_CORE_ONE_SF"."ACCOUNT_REGION" IN ('americas', 'asia_pacific', 'central_europe', 'middle_east_and_africa', 'northern_europe', 'southern_europe', 'other')) AND ("DATA_CORE_ONE_SF"."BILLING_TYPE" IN ('Centrally Billed', '_', 'Locally Billed', 'Non_Vodafone Billed')) AND ("DATA_CORE_ONE_SF"."BUSINESS_TYPE" IN ('New', '_', 'Renewal')) AND ("DATA_CORE_ONE_SF"."PRODUCT_CATEGORY" IN ('Mobile', '_', 'Fixed', 'Other')))
        AND PIPELINE = 1
        AND ASIS = 0
        AND EMPLOYEE_ID = '005b0000006AptlAAC'
      GROUP BY OPPORTUNITY_STAGE,
               SORT_ORDER,
               CASE WHEN CLOSE_DATE >= TRUNC(SYSDATE, 'DD') THEN 'Fresh' ELSE 'Stale' END)
   CROSS JOIN
     (SELECT CONVERSION_RATE,
             ISO AS ISO_CODE
      FROM EXCHANGE_RATES
      WHERE sysdate BETWEEN start_date AND next_start_date
        AND iso = 'EUR' )
   GROUP BY OPPORTUNITY_STAGE,
            SORT_ORDER,
            iso_code
   ORDER BY SORT_ORDER)
GROUP BY currency