how to convert postgresql query to oracle

Mugilan Sukumar_86k6ac

Help me to convert postgresql query to oracle query convers

 

SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
u.active AS "Active",
to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login"
FROM cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM cwd_membership m
JOIN licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID
) AS m ON m.child_name = u.user_name
LEFT JOIN (
SELECT *
FROM cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;

Fabio Racobaldo _Catworkx_

Hi @Mugilan Sukumar ,

please try the following :

SELECT d.directory_name AS "Directory", u.user_name AS "Username", u.active AS "Active", to_timestamp(CAST(attribute_value AS NUMBER)/1000) AS "Last Login" FROM cwd_user u JOIN (SELECT DISTINCT child_name FROM cwd_membership m JOIN licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID ) m ON m.child_name = u.user_name LEFT JOIN (SELECT * FROM cwd_user_attributes ca WHERE attribute_name = 'login.lastLoginMillis' ) a ON a.user_id = u.ID JOIN cwd_directory d ON u.directory_id = d.id ORDER BY "Last Login" DESC;

In case of error please share it.

Fabio