Useful Functions
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page Useful Functions
| Line 1: | Line 1: | ||
| + | [[Microsoft SQL Server]] script useful for [[PeopleSoft]]: | ||
<pre> | <pre> | ||
/************************************************************************************** | /************************************************************************************** | ||
| Line 39: | Line 40: | ||
select 'PS_JOB_JR',* from PS_JOB_JR where EMPLID=@in_emplid | select 'PS_JOB_JR',* from PS_JOB_JR where EMPLID=@in_emplid | ||
| - | order by 1,2,3,4 | + | order by 1,2,3,4,5 |
select 'PS_JOB',* from PS_JOB where EMPLID=@in_emplid | select 'PS_JOB',* from PS_JOB where EMPLID=@in_emplid | ||
| - | order by 1,2,3,4 | + | order by 1,2,3,4,5 |
select 'PS_COMPENSATION',* from PS_COMPENSATION where EMPLID=@in_emplid | select 'PS_COMPENSATION',* from PS_COMPENSATION where EMPLID=@in_emplid | ||
| - | order by 1,2,3,4 | + | order by 1,2,3,4,5,6 |
go | go | ||
| Line 87: | Line 88: | ||
from PS_PERS_DATA_EFFDT | from PS_PERS_DATA_EFFDT | ||
where upper(EMPLID) like upper(@in_value) | where upper(EMPLID) like upper(@in_value) | ||
| + | order by 1,2,3 | ||
select left('PS_NAMES (v8)' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,NAME_TYPE,NAME | select left('PS_NAMES (v8)' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,NAME_TYPE,NAME | ||
| Line 516: | Line 518: | ||
| - | ##comptab 'PS_DEPT_TBL','" | + | -- ##comptab 'PS_DEPT_TBL','"SERVER1"','"SERVER1".DEV.dbo.PS_DEPT_TBL','DEPTID' |
| - | go | + | -- go |
select * from sys.servers | select * from sys.servers | ||
| Line 712: | Line 714: | ||
AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME | AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME | ||
WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' COLLATE DATABASE_DEFAULT ) | WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' COLLATE DATABASE_DEFAULT ) | ||
| - | SELECT | + | SELECT PORTAL_LABEL |
, PORTAL_URI_SEG2 | , PORTAL_URI_SEG2 | ||
| - | |||
, PATH1 | , PATH1 | ||
, PORTAL_OBJNAME | , PORTAL_OBJNAME | ||
, PORTAL_REFTYPE | , PORTAL_REFTYPE | ||
| + | , PORTAL_NAME | ||
FROM PORTAL_REGISTRY | FROM PORTAL_REGISTRY | ||
WHERE PORTAL_REFTYPE != 'F' COLLATE DATABASE_DEFAULT | WHERE PORTAL_REFTYPE != 'F' COLLATE DATABASE_DEFAULT | ||
