Use of two WITH

Asked

Viewed 79 times

1

In the following sentence, where I have to correct?

WITH ITEM AS
(SELECT 1 COD, 'Tratam./Hormônio' NOME, 1 VENDA 
UNION ALL SELECT 2, 'Vacina', 1
UNION ALL SELECT 3, 'Nutricional', 2
UNION ALL SELECT 4, 'Pastagem', 2
UNION ALL SELECT 5, 'Ração', 1
UNION ALL SELECT 6, 'Procedimento', 2
UNION ALL SELECT 7, 'Mat.Laboratório', 1)

WITH VENDA AS
(SELECT 1 COD, 'À vista' DESCRIÇÃO
UNION ALL SELECT 2, 'A prazo')

SELECT ITEM.COD, ITEM.NOME, VENDA.DESCRIÇÃO
FROM ITEM
JOIN VENDA ON ITEM.VENDA=VENDA.COD

The following error occurs:

Mensagem 156, Nível 15, Estado 1, Linha 10
Incorrect syntax near the keyword 'WITH'.
Mensagem 319, Nível 15, Estado 1, Linha 10
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I actually need to use two WITH in a slightly different situation. I created this example to make it easier. Thank you very much!

with
  • 2

    You don’t need to put twice the with, just separate by comma: with item as (...), venda as (...) select ...

1 answer

2

 WITH ITEM AS
    (SELECT 1 COD, 'Tratam./Hormônio' NOME, 1 VENDA  from dual
    UNION ALL SELECT 2, 'Vacina', 1 from dual
    UNION ALL SELECT 3, 'Nutricional', 2 from dual
    UNION ALL SELECT 4, 'Pastagem', 2 from dual
    UNION ALL SELECT 5, 'Ração', 1 from dual
    UNION ALL SELECT 6, 'Procedimento', 2 from dual
    UNION ALL SELECT 7, 'Mat.Laboratório', 1 from dual),
    VENDA AS
    (SELECT 1 COD, 'À vista' DESCRIÇÃO from dual
    UNION ALL SELECT 2, 'A prazo' from dual)

    SELECT ITEM.COD, ITEM.NOME, VENDA.DESCRIÇÃO
    FROM ITEM
    JOIN VENDA ON ITEM.VENDA=VENDA.COD;

Your Syntax is wrong, with should not repeat, just use a comma as in the above example.

Browser other questions tagged with

You are not signed in. Login or sign up in order to post.