Hi,
I have a programming challenge. In our database, staff can have multiple roles (represented within a field src_type). For instance, faculty have src_type 04, Faculty-Administration 02, and students have src_type 14 (and so forth for 17 roles). Src type is not in order of priority.
I needed to rank those by order of priority and get the act_uid (username/email) with the highest priority. For the above 3: Faculty (04) is 1, Faculty Admin (02) is 2, and student (14) is 11.
For instance, if a person has both faculty and student records, the acct_uid to use would be with the faculty record.
Within PowerBuilder where the coding processes one record at a time, the coding that worked for this was:
SELECT * FROM
(SELECT
STD.ACCT_UID,
STD.SRC_TYPE
INTO
:ls_ais_email,
:ls_src_type
FROM STUDENT.TSEGUIDT STD
LEFT JOIN STUDENT.TSESRCTC TYP
ON (STD.SRC_TYPE = TYP.CODE)
CROSS JOIN SYSUA.SYUNVHDC CJ
WHERE
STD.POINTER = :as_pointer AND
STD.ACCT_UID = :as_username
ORDER BY
DECODE (STD.SRC_TYPE,
'04',1,
'02',2,
'05',3,
'03',4,
'10',5,
'11',6,
'12',7,
'13',8,
'09',9,
'01',10,
'16',11,
'14',12,
'15',13,
'17',14,
'06',15,
'07',50,
'08',51
) ASC ) emails
WHERE rownum <= 1;
But in extracting all appropriate people in our database, I cannot get the code to work correctly. This is what I have (here I limited the output to one person who has 2 roles):
SELECT T1.SRC_TYPE,
T1.ACCT_UID // more fields -- eliminated for simplicity
FROM
KUALI_CM.KCM_USER_EXCEPTION
LEFT JOIN STUDENT.TSEGUIDT T1
ON ( KUALI_CM.KCM_USER_EXCEPTION.POINTER = T1.POINTER)
LEFT JOIN DEMOGRAPHIC.DMINDNMT
ON (KUALI_CM.KCM_USER_EXCEPTION.POINTER =demographic.dmindnmt.pointer )
LEFT JOIN KUALI_CM.USER_XREF
ON (KUALI_CM.KCM_USER_EXCEPTION.POINTER = kuali_cm.user_xref.pointer)
LEFT JOIN HUMAN_RESOURCE.HRDRCTYT
ON (KUALI_CM.KCM_USER_EXCEPTION.POINTER = HUMAN_RESOURCE.HRDRCTYT.POINTER)
INNER JOIN (SELECT * FROM (SELECT T2.SRC_TYPE,T2.POINTER
FROM STUDENT.TSEGUIDT T2
ORDER BY
DECODE (T2.SRC_TYPE,
'04',1,
'02',2,
'05',3,
'03',4,
'10',5,
'11',6,
'12',7,
'13',8,
'09',9,
'01',10,
'16',11,
'14',12,
'15',13,
'17',14,
'06',15,
'07',50,
'08',51
) ASC) R1 WHERE rownum <= 1) R5
ON T1.POINTER = R5.POINTER AND
T1.SRC_TYPE = R5.SRC_TYPE
WHERE
kuali_cm.user_xref.pointer is NULL AND
KUALI_CM.KCM_USER_EXCEPTION.POINTER = '1238977' ;
That returned no rows.
This is my first experience with DECODE so I am hoping this is a simple newbie mistake.
Does anyone have any suggestions?
Unfortunately, the code is within PowerBuilder datawindow object and I cannot add any temporary tables to help with this or modify the structure of existing tables.
Thank you for your time and help!