Inconsistency in query outcome

[Tejas_Thakkar3]

Hello, everyone.

We have a requirement to sort by employee number, and my query sorts it in Data Analyzer but not in Analytics Report.

'ORDER BY 'Employee ID''

Any suggestions on how to do this would be greatly appreciated.

[rushikeshvartak]

Share query 

[Tejas_Thakkar3]

SELECT
U.USERNAME AS 'Employee ID',
U.EMPLOYEETYPE,
U.FIRSTNAME,
U.LASTNAME,
U.EMAIL AS 'Email ID',
U.DEPARTMENTName AS 'DEPARTMENT',
U.REGION,
U.LOCATION AS 'HCM Location',
U.Customproperty52 AS 'Country',
CASE WHEN U.STATUSKEY = 0 THEN 'INACTIVE' ELSE 'ACTIVE' END AS 'USER STATUS',
U.STARTDATE AS 'START DATE',
U.ENDDATE AS 'END DATE',
U.CREATEDATE AS 'CREATED ON',
U.Customproperty28 AS 'AD OU Site Name',
A.ACCOUNTID AS 'Account ID',
E.ENDPOINTNAME AS 'ENDPOINTNAME'
FROM
ACCOUNTS A,
USER_ACCOUNTS UA,
USERS U,
ENDPOINTS E
WHERE
A.ACCOUNTKEY = UA.ACCOUNTKEY
AND U.USERKEY = UA.USERKEY
AND A.ENDPOINTKEY = E.ENDPOINTKEY
AND U.STARTDATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND U.STATUSKEY = 1
AND U.CUSTOMPROPERTY17 = 'Active'
ORDER BY 'Employee ID'

[rushikeshvartak]

Saviynt by default sort in ascending first column report

[Tejas_Thakkar3]

@rushikeshvartak Thank you for your response!

The analytics report I am generating does not come with sort.

[rushikeshvartak]

Saviynt by default sort in ascending first column report

SELECT
    U.USERNAME AS 'Employee ID',
    U.EMPLOYEETYPE,
    U.FIRSTNAME,
    U.LASTNAME,
    U.EMAIL AS 'Email ID',
    U.DEPARTMENTName AS 'DEPARTMENT',
    U.REGION,
    U.LOCATION AS 'HCM Location',
    U.Customproperty52 AS 'Country',
    CASE WHEN U.STATUSKEY = 0 THEN 'INACTIVE' ELSE 'ACTIVE' END AS 'USER STATUS',
    U.STARTDATE AS 'START DATE',
    U.ENDDATE AS 'END DATE',
    U.CREATEDATE AS 'CREATED ON',
    U.Customproperty28 AS 'AD OU Site Name',
    A.ACCOUNTID AS 'Account ID',
    E.ENDPOINTNAME AS 'ENDPOINTNAME'
FROM
    ACCOUNTS A
    JOIN USER_ACCOUNTS UA ON A.ACCOUNTKEY = UA.ACCOUNTKEY
    JOIN USERS U ON U.USERKEY = UA.USERKEY
    JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
WHERE
    U.STARTDATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
    AND U.STATUSKEY = 1
    AND U.CUSTOMPROPERTY17 = 'Active'
ORDER BY
    U.USERNAME; 

[Tejas_Thakkar3]

Analytics Output 

[rushikeshvartak]

Its expected behavior as column is first column 

[NM]

Try this 

SELECT

U.USERNAME AS 'Employee ID',

U.EMPLOYEETYPE,

U.FIRSTNAME,

U.LASTNAME,

U.EMAIL AS 'Email ID',

U.DEPARTMENTName AS 'DEPARTMENT',

U.REGION,

U.LOCATION AS 'HCM Location',

U.Customproperty52 AS 'Country',

CASE WHEN U.STATUSKEY = 0 THEN 'INACTIVE' ELSE 'ACTIVE' END AS 'USER STATUS',

U.STARTDATE AS 'START DATE',

U.ENDDATE AS 'END DATE',

U.CREATEDATE AS 'CREATED ON',

U.Customproperty28 AS 'AD OU Site Name',

A.ACCOUNTID AS 'Account ID',

E.ENDPOINTNAME AS 'ENDPOINTNAME'

FROM

ACCOUNTS A,

USER_ACCOUNTS UA,

USERS U,

ENDPOINTS E

WHERE

A.ACCOUNTKEY = UA.ACCOUNTKEY

AND U.USERKEY = UA.USERKEY

AND A.ENDPOINTKEY = E.ENDPOINTKEY

AND U.STARTDATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY)

AND U.STATUSKEY = 1

AND U.CUSTOMPROPERTY17 = 'Active'

ORDER BY Employee ID asc 

[Tejas_Thakkar3]

@NM Thank you for your response!

It is not coming with sort with the above report.

[NM]

@Tejas_Thakkar3 , what format do you need?