Skip to main content

Hey all the sql experts out there...I am trying to pivot 2 fields using the IFS budget_period_amount1_cfv  view (custom view created with one extra field).  I keep getting an oracle error: ORA-00198: column ambiguously defined on the first line.  It works fine when pivoting one field, but when adding another field in the same table (custom field), I get the error.  Here is the statement:

select * from
(select account as "Account", account_desc as "Account Desc", 
budget_period,amount,cf$_sp_actual_costs,budget_year as "Year", code_d as "Dept" from 
budget_period_amount1_cfv where code_d like nvl(upper('&Dept'),'%')
and budget_year like nvl(upper('&Year'),'%') and budget_version = 2)
pivot(sum(amount),sum(cf$_sp_actual_costs) for budget_period in (1,2,3,4,5,6,7,8,9,10,11,12))

 

Any ideas on the problem?

Thanks!

 

 

 

 

 

Hi @svrmhuebner,

 

Here’s a sample query that gets the same error as your query.

SELECT * FROM
   (SELECT cf$_workcenter10 AS production_line,
           order_no AS orders,
           revised_qty_due AS lot_size,
           objstate AS status
    FROM IFSAPP.SHOP_ORD_CFV
    WHERE UPPER(objstate) IN ('STARTED','RELEASED','PLANNED')
      AND UPPER(contract) = UPPER('&SITE'))
   PIVOT ( COUNT(orders), AVG(lot_size) FOR production_line IN ('2CYCL','DRUM','GAL','OFFLN','PAIL','QUART','TOTE','UTLTY'))


Here’s the modified query that’s working now using multiple pivots.

SELECT * FROM
   (SELECT cf$_workcenter10,
           order_no,
           revised_qty_due,
           objstate           
    FROM IFSAPP.SHOP_ORD_CFV
    WHERE UPPER(objstate) IN ('STARTED','RELEASED','PLANNED')
      AND UPPER(contract) = UPPER('&SITE'))
   PIVOT ( COUNT(order_no) AS ORDERS, AVG(revised_qty_due) AS LOT_SIZE FOR cf$_workcenter10 IN ('2CYCL','DRUM','GAL','OFFLN','PAIL','QUART','TOTE','UTLTY'))

I think if you modify your query as follows it will work.

SELECT * FROM
(SELECT account, 
        account_desc, 
        budget_period,amount,
        cf$_sp_actual_costs,
        budget_year,
        code_d
FROM budget_period_amount1_cfv 
WHERE code_d like nvl(upper('&DEPT'),'%')
  AND budget_year like nvl(upper('&YEAR'),'%')
  AND budget_version = 2)
PIVOT(SUM(amount),SUM(cf$_sp_actual_costs) FOR budget_period IN (1,2,3,4,5,6,7,8,9,10,11,12))

 

William Klotz

Many thanks William, the changes seemed to have worked.  Appreciate the help.

Mark

Reply


Terms & ConditionsCookie settings