CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Jav
Jav

Reputation: 35

Column ambiguously defined en Oracle

I'm trying to do CASE WHEN in Oracle, but I'm getting this error.

Something similar I do in SQL SERVER and it works.

 SELECT SUM(amount) as AMOUNT,Substr(con.DATES, 1,6 ) as DATES,
       CASE
          WHEN pro.cod_grup in ('54') and pro.cod_grup_2 in ('38')
            THEN 'WORKSHOPS'
            end as "ORIGIN"
   FROM AMOUNTS_B con inner join PRODUCTOS pro on con.product = pro.cod_product
        where DATES >= '20180101'
        group by FECHA, ORIGIN;

If the tables have aliases, why does the error occur?

Upvotes: 0

Views: 70

Answers (1)

MT0
MT0

Reputation: 167962

Your tables do not have a ORIGIN column, that is an alias you have defined in the SELECT clause and cannot use that alias in the GROUP BY clause.

Either repeat the CASE statement:

SELECT SUM(amount) as AMOUNT,
       Substr(con.DATES, 1,6 ) as DATES,
       CASE
         WHEN pro.cod_grup in ('54') and pro.cod_grup_2 in ('38')
         THEN 'WORKSHOPS'
       end as "ORIGIN"
FROM   AMOUNTS_B con
       inner join PRODUCTOS pro
       on con.product = pro.cod_product
WHERE  Substr(con.DATES, 1,6 ) >= '20180101'
group by FECHA,
       CASE
         WHEN pro.cod_grup in ('54') and pro.cod_grup_2 in ('38')
         THEN 'WORKSHOPS'
       end;

or use an outer query, which can use the alias:

SELECT SUM(amount) as AMOUNT,
       DATES,
       ORIGIN
FROM   (
  SELECT amount,
         fecha,
         Substr(con.DATES, 1,6 ) as DATES,
         CASE
           WHEN pro.cod_grup in ('54') and pro.cod_grup_2 in ('38')
           THEN 'WORKSHOPS'
         end as "ORIGIN"
  WHERE  Substr(con.DATES, 1, 6 ) >= '20180101'
  FROM   AMOUNTS_B con
         inner join PRODUCTOS pro
         on con.product = pro.cod_product
)
group by FECHA,
       ORIGIN;

(Note: you may also need DATES in the GROUP BY clause.)

Upvotes: 2

Related Questions