Different ways to pivot a column in Oracle SQL and apply format masks

Question

I work on SQL Developer and BIRT Viewer and I get this error when applying the format mask '999G999G999G999G999G999G990':

ORA-01722: μη αποδεκτός αριθμός
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

The query:

select
        *
    from

(   select  pc.description production_category,
                decode(pph.allocation,1,'GR-70B',2,'BG',3,'GR-70A',4,'GR-70Δ',6,'UK',8,'USA-KY',9,'USA-OR') alloc,   
          to_number(to_char(sum(ppd.pcs),'999G999G999G999G999G999G990')) pcs
        
from prd_production_hd pph,
            prd_production_details ppd,
            prod_categories pc
        where pph.id = ppd.production_id
        and ppd.prd_category_id in (14,15,16,17,18,20,21,51,52,56)
        and ppd.pcs is not null
        and ppd.prd_category_id = pc.id
        and pph.prod_date = :P_DATE
        group by pc.description,pph.allocation
        )

    pivot (
        sum(pcs)
        for alloc in ('GR-70B' GR_70Β, 'BG' BG, 'GR-70A' GR_70Α, 'GR-70Δ' GR_70Δ,'UK' UK,'USA-KY' USA_KY,'USA-OR' USA_OR)
    )`

Is there an alternative way to apply the format mask?

Answer 1

You appear to want to aggregate and then format the number:

SELECT production_category,
       TO_CHAR(GR_70Β,'999G999G999G999G999G999G990') AS GR_70B,
       TO_CHAR(BG,    '999G999G999G999G999G999G990') AS BG,
       TO_CHAR(GR_70Α,'999G999G999G999G999G999G990') AS GR_70Α,
       TO_CHAR(GR_70Δ,'999G999G999G999G999G999G990') AS GR_70Δ,
       TO_CHAR(UK,    '999G999G999G999G999G999G990') AS UK,
       TO_CHAR(USA_KY,'999G999G999G999G999G999G990') AS USA_KY,
       TO_CHAR(USA_OR,'999G999G999G999G999G999G990') AS USA_OR
FROM   (
  SELECT pc.description AS production_category,
         pph.allocation,
         ppd.pcs
  FROM   prd_production_hd pph
         INNER JOIN prd_production_details ppd
         ON pph.id = ppd.production_id
         INNER JOIN prod_categories pc
         ON ppd.prd_category_id = pc.id
  WHERE  ppd.prd_category_id in (14,15,16,17,18,20,21,51,52,56)
  AND    ppd.pcs is not null
  AND    pph.prod_date = :P_DATE
)
PIVOT (
  SUM(pcs)
  FOR allocation in (
    1 AS GR_70Β,
    2 AS BG,
    3 AS GR_70Α, 
    4 AS GR_70Δ,
    6 AS UK,
    8 AS USA_KY,
    9 AS USA_OR
  )
);

Note: you do not need to DECODE the allocations before PIVOTing; you can do it in the PIVOT. You also do not need to aggregate twice; again, do it once in the PIVOT.