how to convert postgresql query to oracle

[Mugilan Sukumar]

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 _Herzum_]

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

[Mugilan Sukumar]

Hi @Fabio Racobaldo _Herzum_ 
Thanks for you response.

when i tired to execute above query getting below error message.

ORA-00932: inkonsekvente datatyper: forventet -, fikk NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 1 Column: 134