ALL, SOME and ANY :- These operators are not widely used in the SQL, these keywords exists in both Oracle and MS SQL. These keywords can be very useful in developing many PeopleSoft related query. It makes the query simple to write and understand.
Eg:- If you want the list of employees who are terminated in all the assignments (employee records).
1) Select EMPLID from PS_PERSON AWhere ‘I’ = ALL (Select HR_STATUS from PS_CURRENT_JOB JWhere J.EMPLID = A.EMPLID )
2) Select EMPLID from PS_PERSON AWhere ‘A’ > ANY (Select HR_STATUS from PS_CURRENT_JOB JWhere J.EMPLID = A.EMPLID )
ANY or SOME: Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows.ALL: Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, < , <=, >=. Evaluates to TRUE if the query returns no rows.
Jan 09 2011
PeopleSoft – Special SQL operators make the SQL simple
Apr 21 2010
Get all the fields from table
SELECT [name] AS [FIELD] FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = ‘U’ AND [NAME] = ‘PS_JOB’)
Apr 16 2010
While SQL Select using CASE like IF-Condition
SELECT CASE WHEN MAX(NOTE_SEQ_NBR) IS NULL THEN 1 ELSE MAX(NOTE_SEQ_NBR) + 1 END FROM PS_RC_CASE_NOTE
Apr 14 2010
To Check PeopleSoft Process Status
SELECT a.prcsname, a.oprid, a.servernamerun, b.xlatlongname AS STATUS,
a.rundttm,
TRUNC (MOD ((a.enddttm – a.rundttm) * 24, 24)) “Hr”,
TRUNC (MOD ((a.enddttm – a.rundttm) * 24 * 60, 60)) mi,
TRUNC (MOD ((a.enddttm – a.rundttm) * 24 * 60 * 60, 60)) sec,
a.prcsinstance, c.outputdir
FROM psprcsrqst a, xlattable b, ps_cdm_list c
WHERE b.fieldname = ‘RUNSTATUS’
AND b.fieldvalue = a.runstatus
AND a.prcsinstance = c.prcsinstance
AND a.prcsname = ‘YourProcessName’
Apr 14 2010
Show Users Assigned a Specific Role
SELECT C.OPRID ,
C.OPRDEFNDESC ,
A.ROLENAME ,
A.DESCR
FROM PSROLEDEFN A,
PSROLEUSER B ,
PSOPRDEFN C
WHERE B.ROLENAME = A.ROLENAME
AND C.OPRID = B.ROLEUSER
AND B.ROLENAME LIKE UPPER(:1)
GROUP BY C.OPRID,
C.OPRDEFNDESC ,
A.ROLENAME ,
A.DESCR
ORDER BY C.OPRID;
Apr 14 2010
Get the Roles Assigned to a Specific User
SELECT C.OPRID,
C.OPRDEFNDESC ,
A.ROLENAME ,
A.DESCR
FROM PSROLEDEFN A,
PSROLEUSER B ,
PSOPRDEFN C
WHERE B.ROLENAME = A.ROLENAME
AND C.OPRID = B.ROLEUSER
AND (C.OPRID =:1
OR C.OPRDEFNDESC LIKE upper(:2))
GROUP BY C.OPRID,
C.OPRDEFNDESC ,
A.ROLENAME ,
A.DESCR;
Apr 14 2010
Roles Permissions Users Assigned to Portal Content Reference or Component
SELECT DISTINCT c.portal_name, c.portal_objname, b.rolename, e.descr
FROM psroleclass b, psprsmperm c, psprsmdefn d, psroledefn e
WHERE c.portal_objname = d.portal_objname
AND b.classid = c.portal_permname
AND b.rolename = e.rolename
AND (d.portal_uri_seg2 = :1 OR d.portal_objname = :2);
Apr 14 2010
Get the Permission Lists that Assigned a User
SELECT D.OPRID, D.OPRDEFNDESC, C.ROLEUSER, A.ROLENAME, A.CLASSID,
B.CLASSDEFNDESC
FROM PSROLECLASS A, PSCLASSDEFN B, PSROLEUSER C, PSOPRDEFN D
WHERE A.CLASSID = B.CLASSID
AND C.ROLENAME = A.ROLENAME
AND D.OPRID = C.ROLEUSER
AND D.OPRID = ‘VP1′
GROUP BY D.OPRID,
D.OPRDEFNDESC,
C.ROLEUSER,
A.ROLENAME,
A.CLASSID,
B.CLASSDEFNDESC;
Apr 14 2010
List of Pages PeopleSoft Role Name Can Access
SELECT B.MENUNAME, B.BARNAME, B.BARITEMNAME, D.PNLNAME, C.PAGEACCESSDESCR
FROM PSROLECLASS A,
PSAUTHITEM B,
PSPGEACCESSDESC C,
PSPNLGROUP D,
PSMENUITEM E
WHERE A.CLASSID = B.CLASSID
AND D.PNLGRPNAME = E.PNLGRPNAME
AND B.MENUNAME = E.MENUNAME
Next Page »