How to insert a constant in a PL/SQL line?

Asked

Viewed 62 times

0

Hello, I’m having some difficulties in data presentation.

with atores(nome, data_nasc) as (select nome, data_nasc from representa inner join artistas using(n_artista))
    ,realizadores(nome, data_nasc) as (select nome, data_nasc from realiza inner join artistas using(n_artista))
    ,autores(nome, data_nasc) as (select nome, data_nasc from escreve inner join artistas using(n_artista))

select nome,
case 
     when exists(select * from realizadores where realizadores.nome = artistas.nome and realizadores.data_nasc = artistas.data_nasc) then 'Realizador'
     when exists(select * from autores where autores.nome = artistas.nome and autores.data_nasc = artistas.data_nasc) then 'Autor'
     when exists(select * from atores where atores.nome = artistas.nome and atores.data_nasc = artistas.data_nasc) then 'Ator'
end as tipo_artista
from artistas;

This is the code that I have at the moment and it works partially, I want the name of each artist to appear and then also the type of artist that he is, only the code fails when I have an artist who is more than a type of artist, for example if he is both author and actor at the same time, only appears the line that says he is Author I suppose to be because it is the first that appears in the CASE clause.

Is there a way for me to insert artist type_in another way?

Thanks in advance.

sql oracle pl-sql

  • and when he’s more of a type what do you want to do? has some criteria to choose one, which has more priority?

  • In place of a case "artist type" three cases one for director , author and actor, without entering the merit of the query itself that maybe would be better with three more joins.

  • no, I wanted to appear the two types, in two different lines appeared the name repeated but the different type

  • Maybe you can make it easier with the use of UNION and ORDER BY name.

  • I’ve even been putting order by name but it says invalid identifier

1 answer

0

with atores(nome, tipo) as (select nome, case when 1=1 then 'Ator' end as tipo from representa inner join artistas using(n_artista))
    ,realizadores(nome, tipo) as (select nome, case when 1=1 then 'Realizador' end as tipo from realiza inner join artistas using(n_artista))
    ,autores(nome, tipo) as (select nome,case when 1=1 then 'Autor' end as tipo from escreve inner join artistas using(n_artista))
select nome nome_artista, tipo tipo_artista
from (select * from autores) union all (select * from realizadores) union all (select * from atores);

I did so and gave but I do not know if it will be accepted lol

However as I said in a comment, I can not put "order by name" at the end, gives invalid identifier

Browser other questions tagged sql oracle pl-sql

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