0
0 Comments

I can’t find a way to translate the entire code into R and actually work it Any idea how to replace the coalesce function and maybe get it working in R?

    SELECT DISTINCT m0.Moviename, COALESCE((
    SELECT ff0.NameProd FROM Distribution dd0
    INNER JOIN Movies mm0 ON dd0.MovieCode =ff0.MovieCode
    INNER JOIN Filmmaker ff0 ON dd0.ActorCode = ff0.FilmmakerCode
    WHERE mm0 = m0 ORDER BY 1 ASC LIMIT 1
    ),'N/A') AS actor_1,
COALESCE((
    SELECT ff0.NameProd FROM Distribution dd0
    INNER JOIN Movies mm0 ON dd0.MovieCode =ff0.MovieCode
    INNER JOIN Filmmaker ff0 ON dd0.ActorCode = ff0.FilmmakerCode
    WHERE mm0 = m0 ORDER BY 1 ASC OFFSET 1 LIMIT 1
    ),'N/A') AS actor_2,
COALESCE((
    SELECT ff0.NameProd FROM Distribution dd0
    INNER JOIN Movies mm0 ON dd0.MovieCode =ff0.MovieCode
    INNER JOIN Filmmaker ff0 ON dd0.ActorCode = ff0.FilmmakerCode
    WHERE mm0 = m0 ORDER BY 1 ASC OFFSET 2 LIMIT 1
    ),'N/A') AS actor_3,
COALESCE((
    SELECT COUNT (dd0.ActorCode) FROM Distribution dd0
    INNER JOIN Movies mm0 ON dd0.MovieCode =ff0.MovieCode
    INNER JOIN Filmmaker ff0 ON dd0.ActorCode = ff0.FilmmakerCode
    WHERE ff0 = f0

    ),0) AS total_actor
    FROM Movies m0

ORDER BY total_actor DESC

At least a pattern or something to do it, even if it’s a little hard to read without the db

Anonymous Asked question May 13, 2021