Query works in PLSQL Developer but doesn't in SQL Developer

Question

So, I have this query that's just making me go crazy. For some crazy reason, the query runs and brings the desired output on PLSQL Developer, but on SQL Developer it doesn't! As far as I know, the exact same query should work on both, not just one or the other. Not everybody at my team has PLSQL Dev, so it has to work on both. This query also shows different behaviour on my BI Application. Has anyone seen this crazy behaviour before?

Here's the query:

with t1 as (
SELECT
  case  when CLG_STATUS.NM_STATUS in ('1 - ATIVO','5 - BLOQUEADO','7 - AGUARDANDO AUTENTICAÇÃO') then 'LG Club Member'
    else 'Non  Member' 
  end membership,
  Round(
    Round(SUM(NVL(CASE WHEN CLG_VENDA.CD_TP_VENDA = 'D' THEN -1*CLG_VENDA.QTD_VENDA ELSE CLG_VENDA.QTD_VENDA END,0)), 1) 
    /
   Count(distinct CLG_PARTICIPANTE.ID)
  , 1) as average_sales ,
  Round(
    SUM(NVL(CASE WHEN CLG_VENDA.CD_TP_VENDA = 'D' THEN -1*CLG_VENDA.QTD_VENDA ELSE CLG_VENDA.QTD_VENDA END,0))
  , 1) as sellout, 
  Count(distinct CLG_PARTICIPANTE.ID)
  as "qty members"
FROM
  CLG_VENDA_PONTO,
  CLG_PARTICIPANTE,
  CLG_STATUS,
  CLG_VENDA,
  CLG_CARGO  CLG_CARGO_VENDA,
  CLG_CANAL  CLG_CANAL_VENDA,
  CLG_REDE  CLG_REDE_VENDA,
  CLG_PRODUTO,
  CLG_TP_MOVIMENTO,
  DIM_PERIOD_DAY  PERIOD_VENDA_DATA,
  CLG_LOJA  CLG_LOJA_VENDA
WHERE
  ( CLG_VENDA_PONTO.ID_VENDA=CLG_VENDA.ID(+)  )
  AND  ( CLG_VENDA_PONTO.ID_PROD=CLG_PRODUTO.ID(+)  )
  AND  ( CLG_VENDA_PONTO.ID_CARGO=CLG_CARGO_VENDA.ID  )
  AND  ( CLG_LOJA_VENDA.ID=CLG_VENDA_PONTO.ID_LOJA  )
  AND  ( CLG_REDE_VENDA.ID=CLG_LOJA_VENDA.ID_REDE  )
  AND  ( CLG_CANAL_VENDA.ID=CLG_VENDA_PONTO.ID_CANAL  )
  AND  ( CLG_PARTICIPANTE.ID_LOJA=CLG_LOJA_VENDA.ID  )
  AND  ( CLG_LOJA_VENDA.ID_REDE=CLG_REDE_VENDA.ID  )
  AND  ( CLG_PARTICIPANTE.ID=CLG_VENDA_PONTO.ID_PARTCPTE  )
  AND  ( CLG_VENDA_PONTO.ID_TP_MOVIMENTO=CLG_TP_MOVIMENTO.ID  )
  AND  ( CLG_STATUS.ID=CLG_PARTICIPANTE.ID_STATUS  )
  AND  ( CLG_VENDA_PONTO.DATA_VENDA=PERIOD_VENDA_DATA.YYYYMMDD  )
  AND  ( CLG_CARGO_VENDA.NM_CARGO = '5 - VENDEDOR' OR CLG_VENDA.ID_CARGO = 8  )
  AND  
  (
   CLG_PRODUTO.NM_PRODUTO  NOT IN  ( 'M4338 - FRETE','R39745 - FRETE SOBRE VENDAS'  )
   AND
   CLG_TP_MOVIMENTO.NM_MOVIMENTO  IN  ( '1 - VENDA','3 - DEVOLUCAO'  )
   AND
   CLG_REDE_VENDA.NM_REDE  =  '21540901 - RABELO'
   AND
   PERIOD_VENDA_DATA.YYYYMMDD_DATE  BETWEEN  to_date('01-01-2013', 'DD-MM-YYYY')  AND  to_date('31-12-2013', 'DD-MM-YYYY')
   AND
   CLG_CANAL_VENDA.NM_CANAL  IN  ('1 - VAREJO')
   AND
   CLG_PRODUTO.FG_MAPEADO  IN  ( 'SIM'  )
  )
GROUP BY
  case  when CLG_STATUS.NM_STATUS in ('1 - ATIVO','5 - BLOQUEADO','7 - AGUARDANDO AUTENTICAÇÃO') then 'LG Club Member'
    else 'Non  Member' 
  end
), t2 as (
SELECT
  case  when CLG_STATUS.NM_STATUS in ('1 - ATIVO','5 - BLOQUEADO','7 - AGUARDANDO AUTENTICAÇÃO') then 'LG Club Member'
    else 'Non  Member' 
  end membership,
  Round(
    Round(SUM(NVL(CASE WHEN CLG_VENDA.CD_TP_VENDA = 'D' THEN -1*CLG_VENDA.QTD_VENDA ELSE CLG_VENDA.QTD_VENDA END,0)), 1) 
    /
   Count(distinct CLG_PARTICIPANTE.ID)
  , 1) as average_sales ,
  Round(
    SUM(NVL(CASE WHEN CLG_VENDA.CD_TP_VENDA = 'D' THEN -1*CLG_VENDA.QTD_VENDA ELSE CLG_VENDA.QTD_VENDA END,0))
  , 1) as sellout, 
  Count(distinct CLG_PARTICIPANTE.ID)
  as "qty members" 
FROM
  CLG_VENDA_PONTO,
  CLG_PARTICIPANTE,
  CLG_STATUS,
  CLG_VENDA,
  CLG_CARGO  CLG_CARGO_VENDA,
  CLG_CANAL  CLG_CANAL_VENDA,
  CLG_REDE  CLG_REDE_VENDA,
  CLG_PRODUTO,
  CLG_TP_MOVIMENTO,
  DIM_PERIOD_DAY  PERIOD_VENDA_DATA,
  CLG_LOJA  CLG_LOJA_VENDA
WHERE
  ( CLG_VENDA_PONTO.ID_VENDA=CLG_VENDA.ID(+)  )
  AND  ( CLG_VENDA_PONTO.ID_PROD=CLG_PRODUTO.ID(+)  )
  AND  ( CLG_VENDA_PONTO.ID_CARGO=CLG_CARGO_VENDA.ID  )
  AND  ( CLG_LOJA_VENDA.ID=CLG_VENDA_PONTO.ID_LOJA  )
  AND  ( CLG_REDE_VENDA.ID=CLG_LOJA_VENDA.ID_REDE  )
  AND  ( CLG_CANAL_VENDA.ID=CLG_VENDA_PONTO.ID_CANAL  )
  AND  ( CLG_PARTICIPANTE.ID_LOJA=CLG_LOJA_VENDA.ID  )
  AND  ( CLG_LOJA_VENDA.ID_REDE=CLG_REDE_VENDA.ID  )
  AND  ( CLG_PARTICIPANTE.ID=CLG_VENDA_PONTO.ID_PARTCPTE  )
  AND  ( CLG_VENDA_PONTO.ID_TP_MOVIMENTO=CLG_TP_MOVIMENTO.ID  )
  AND  ( CLG_STATUS.ID=CLG_PARTICIPANTE.ID_STATUS  )
  AND  ( CLG_VENDA_PONTO.DATA_VENDA=PERIOD_VENDA_DATA.YYYYMMDD  )
  AND  ( CLG_CARGO_VENDA.NM_CARGO = '5 - VENDEDOR' OR CLG_VENDA.ID_CARGO = 8  )
  AND  
  (
   CLG_PRODUTO.NM_PRODUTO  NOT IN  ( 'M4338 - FRETE','R39745 - FRETE SOBRE VENDAS'  )
   AND
   CLG_TP_MOVIMENTO.NM_MOVIMENTO  IN  ( '1 - VENDA','3 - DEVOLUCAO'  )
   AND
   CLG_REDE_VENDA.NM_REDE  =  '21540901 - RABELO'
   AND
   PERIOD_VENDA_DATA.YYYYMMDD_DATE  BETWEEN  add_months(to_date('01-01-2013', 'DD-MM-YYYY'), -12)  AND  add_months(to_date('31-12-2013', 'DD-MM-YYYY'), -12)
   AND
   CLG_CANAL_VENDA.NM_CANAL  IN  ('1 - VAREJO')
   AND
   CLG_PRODUTO.FG_MAPEADO  IN  ( 'SIM'  )
  )
GROUP BY
  case  when CLG_STATUS.NM_STATUS in ('1 - ATIVO','5 - BLOQUEADO','7 - AGUARDANDO AUTENTICAÇÃO') then 'LG Club Member'
    else 'Non  Member' 
  end
)
select t1.*, coalesce((t1.average_sales - t2.average_sales) / t2.average_sales, 0)    as variation
from t1 join t2 on t1.membership = t2.membership

On SQL Developer it displays this:

ORA-00928: missing SELECT keyword
00928. 00000 -  "missing SELECT keyword"

I'm totally at a loss here. How can a query parse at one tool and not the other ? I tried searching around the net but to no avail. Thanks in advance!

Answer 1

It might be the line,

    /

SQL Plus uses / by itself to mark the end of a sql query, and SQL Developer most likely follows. However, the preceding whitespace may change the meaning, so this may not be the problem.

To test this hypothesis move the / to the preceding line. Change

    Round(SUM(NVL(CASE WHEN CLG_VENDA.CD_TP_VENDA = 'D' THEN -1*CLG_VENDA.QTD_VENDA ELSE CLG_VENDA.QTD_VENDA END,0)), 1) 
    /
   Count(distinct CLG_PARTICIPANTE.ID)

To

    Round(SUM(NVL(CASE WHEN CLG_VENDA.CD_TP_VENDA = 'D' THEN -1*CLG_VENDA.QTD_VENDA ELSE CLG_VENDA.QTD_VENDA END,0)), 1) /
   Count(distinct CLG_PARTICIPANTE.ID)

If that is the problem, then it is a matter of reformatting to something that works for you that doesn't leave / on a line by itself.