2
Problem :
Resolution:
SELECT NOME, DECODE(COUNT(CODIGO_LIVRO),0,'NENHUM',COUNT(CODIGO_LIVRO))"NR LIVROS"
FROM AUTORES A,LIVROS L
WHERE A.CODIGO_AUTOR=L.CODIGO_AUTOR(+)
GROUP BY NOME
ORDER BY 1;
Tables:
Doubt:
Because I need the second COUNT(CODIGO_LIVRO) within the function DECODE and what the (+)? If I’m not mistaken (+) is a Join but I do not understand how it works and why it is necessary there...
1
Good afternoon Droopy!
The symbol (+) indicates an Outer Join. As it is next to the BOOKS table field, it means that it will bring the composed record of Join even if there is no book of that author. For example, if you register my name as Author and do not register any books, my name will be in the result with the total of "NONE". If you take out "(+)" and select again, my name will not appear in the selection.
As for DECODE, from what I understand, it is being used only to not return 0 when there are no books. When this happens he returns "NONE", but when he finds some he just puts the number.
Browser other questions tagged sql oracle
You are not signed in. Login or sign up in order to post.
The operator (+) is used to define OUTER JOIN in clause WHERE. Vide https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm
– José Diz
If I asked the authors who did not have BOOKS to come out as "none" for this it took an "Outer Join" that in the case brings the AUTHORS without BOOKS , DECODE is to exchange the "0" for the "none".
– Motta
@Motta right, I understood the usefulness of Code, I just don’t understand why I need the second COUNT inside Code, if I take the last COUNT the different entries of 0 are NULL
– Droopy
the Decode works by pair of parameters , in the case if Count is 0 returns "none", but Count again, causes strangeness because it counts twice
– Motta