This query giving error, While executing this syntax error comes for AS LastYearVolumeBilled-CodePudding

Error comes on AS LastYearVolumeBilled this line how to add two case when in one select statement

select 'R-1' as Year,
(case when SDBM_MONTH_YR = to_number(extract(year from sysdate) || '04')then 
round((sum(SDBM_BILLED_UNITS)/avg(sdbm_billing_days))/1000,2) 
end )AS CurrentYearVolumeBilled
FROM t_servicedetail_billing  
where SDBM_MONTH_YR= to_number(extract(year from sysdate) || '04')
group by SDBM_MONTH_YR ,
(CASE WHEN SDBM_MONTH_YR = to_number( extract(year from add_months(sysdate,-12)) || '04')
THEN round((sum(SDBM_BILLED_UNITS)/avg(sdbm_billing_days))/1000,2)
END)AS LastYearVolumeBilled
FROM t_servicedetail_billing
where SDBM_MONTH_YR= to_number( extract(year from add_months(sysdate,-12)) || '04')
group by SDBM_MONTH_YR;

CodePudding user response：

These are two different queries, so one option might be to treat them as such by using a CTE (as my example shows) or as subqueries.

WITH
   t_curr
   AS
      (  SELECT 'R-1' AS year,
                (CASE
                    WHEN sdbm_month_yr =
                         TO_NUMBER (EXTRACT (YEAR FROM SYSDATE) || '04')
                    THEN
                       ROUND (
                            (SUM (sdbm_billed_units) / AVG (sdbm_billing_days))
                          / 1000,
                          2)
                 END) AS currentyearvolumebilled
           FROM t_servicedetail_billing
          WHERE sdbm_month_yr = TO_NUMBER (EXTRACT (YEAR FROM SYSDATE) || '04')
       GROUP BY sdbm_month_yr),
   t_last
   AS
      (  SELECT 'R-1' AS year,
                (CASE
                    WHEN sdbm_month_yr =
                         TO_NUMBER (
                               EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -12))
                            || '04')
                    THEN
                       ROUND (
                            (SUM (sdbm_billed_units) / AVG (sdbm_billing_days))
                          / 1000,
                          2)
                 END) AS lastyearvolumebilled
           FROM t_servicedetail_billing
          WHERE sdbm_month_yr =
                TO_NUMBER (
                   EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -12)) || '04')
       GROUP BY sdbm_month_yr)
SELECT a.year, a.currentyearvolumebilled, b.lastyearvolumebilled
  FROM t_curr a CROSS JOIN t_last b;

How to add more subqueries (simplified):

with 
t_curr_r1 as
  (select 'R-1' as year, ... from ...),
t_curr_r2 as
  (select 'R-2' as year, ... from ...),
t_curr_r3 as
  (select 'R-3' as year, ... from ...),
--
t_last_r1
  (select 'R-1' as year, ... from ...),
t_last_r2 as
  (select 'R-2' as year, ... from ...),
t_last_r3 as
  (select 'R-3' as year, ... from ...),
--
select c1.year, c1.currentyearvolumebilled,
                c2.currentyearvolumebilled,
                c3.currentyearvolumebilled,
                ...
                l1.lastyearvolumebilled,
                l2.lastyearvolumebilled,
                l3.lastyearvolumebilled,
                ...
from t_curr_r1 c1 cross join t_curr_r2 c2
                  cross join t_curr_r3 c3
                  ...
                  cross join t_last_r1 l1
                  cross join t_last_r2 l2
                  cross join t_last_r3 l3
                  ...

CodePudding user response：

You have repeated your query twice. You don't need a case statement cause you have the same condition in where clause. So the correct query might be -

select 'R-1' as Year,
       round((sum(SDBM_BILLED_UNITS)/avg(sdbm_billing_days))/1000,2) 
       END AS CurrentYearVolumeBilled
  FROM t_servicedetail_billing  
 WHERE SDBM_MONTH_YR = to_number(extract(year from sysdate) || '04')