Jan 12, 2017

PeopleSoft : SQL to find special character in Names

SELECT *
FROM SYSADM.PS_NAMES A
WHERE (A.NAME                        !=   CONVERT(A.NAME,'JA16SJIS')
OR A.LAST_NAME                      !=   CONVERT(A.LAST_NAME,'JA16SJIS')
OR A.LAST_NAME_SRCH         !=   CONVERT(A.LAST_NAME_SRCH,'JA16SJIS')
OR A.FIRST_NAME_SRCH        !=   CONVERT(A.FIRST_NAME_SRCH,'JA16SJIS')
OR A.FIRST_NAME                     !=   CONVERT(A.FIRST_NAME,'JA16SJIS')
OR A.MIDDLE_NAME                !=   CONVERT(A.MIDDLE_NAME,'JA16SJIS')
OR A.NAME_DISPLAY               !=   CONVERT(A.NAME_DISPLAY,'JA16SJIS')
OR A.NAME_FORMAL               !=   CONVERT(A.NAME_FORMAL,'JA16SJIS')
OR A.SECOND_LAST_NAME    !=   CONVERT(A.SECOND_LAST_NAME,'JA16SJIS')
OR A.PREF_FIRST_NAME         !=    CONVERT(A.PREF_FIRST_NAME,'JA16SJIS')
OR A.NAME_AC                          !=    CONVERT(A.NAME_AC ,'JA16SJIS')
OR A.SECOND_LAST_SRCH     !=    CONVERT(A.SECOND_LAST_SRCH,'JA16SJIS')
OR A.NAME_DISPLAY_SRCH   !=    CONVERT(A.NAME_DISPLAY_SRCH,'JA16SJIS'))
AND EFFDT=(SELECT MAX(B.EFFDT) FROM SYSADM.PS_NAMES B WHERE B.EMPLID=A.EMPLID AND B.NAME_TYPE=A.NAME_TYPE);

No comments:

Post a Comment