AnsweredAssumed Answered

SQL to NQSL: How to use DISTINCT with ROWNUMBER

Question asked by Nei_Lojas_Renner_SA on Jun 1, 2016
Latest reply on Jun 2, 2016 by Nei_Lojas_Renner_SA

Hello Communitie,

 

For each project, I need to list Properties>Access to this Project>Resource;

Using SQL Trace, I build this query (works fine...) :

 

SELECT DISTINCT (a.right_id),

                    u.user_name,

                    p.unique_name,

                    p.name,

                    p.is_active,

                    r.first_name first_name,

                    r.last_name last_name,

                    r.user_id,

                    g.group_name group_name,

                    g.description,

                    g.is_automatic

   FROM cmn_sec_assgnd_obj_perm a,

              srm_resources r,

              cmn_sec_groups_v g,

       srm_projects p,

              cmn_sec_users u

   WHERE a.principal_id = r.user_id

  AND r.user_id = u.id

        AND a.object_instance_id = p.id

        AND a.right_id = g.id

        AND g.right_type = 'SRM_PROJECTS'

        AND g.language_code = 'pt'  order by a.right_id

 

This query does not have a column with unique values, so to avoid duplicate entries I think that i have to use someting like ROWNUMBER. In other NQSL ( diferent one, not related to this...) I have used:

select

@SELECT:DIM:USER_DEF:IMPLIED:PERMISSOES:ROW_NUMBER() OVER(ORDER BY u.first_name):LINHA@,

 

But now how can a convert to NSQL using DISTINCT?

 

Best regards

Outcomes