Useful Functions

From TekiWiki

Jump to: navigation, search

This whole wiki has now moved to http://www.springboardsolutions.com/wiki

Go to this page Useful Functions

(Created page with "<pre> /************************************************************************************** aaaaUsefulFunctions.sql - a useful set of global temporary procedures for use with ...")
 
Line 1: Line 1:
 +
[[Microsoft SQL Server]] script useful for [[PeopleSoft]]:
<pre>
<pre>
/**************************************************************************************
/**************************************************************************************
Line 10: Line 11:
               ##emp '0024'
               ##emp '0024'
***************************************************************************************/
***************************************************************************************/
 +
if exists (select * from tempdb..sysobjects where xtype='P' and name='##emp')
drop procedure ##emp
drop procedure ##emp
go
go
Line 38: 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 56: Line 58:
               ##findemp 'Ander'
               ##findemp 'Ander'
***************************************************************************************/
***************************************************************************************/
 +
if exists (select * from tempdb..sysobjects where xtype='P' and name='##findemp')
drop procedure ##findemp
drop procedure ##findemp
go
go
Line 74: Line 77:
upper(EMPLID) like upper(@in_value)
upper(EMPLID) like upper(@in_value)
or upper(OPRID) like upper(@in_value)
or upper(OPRID) like upper(@in_value)
 +
order by 1,2,3,4
select left('PS_PERSONAL_DATA' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,NAME  
select left('PS_PERSONAL_DATA' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,NAME  
Line 79: Line 83:
where upper(EMPLID) like upper(@in_value)
where upper(EMPLID) like upper(@in_value)
or upper(NAME) like upper(@pct + @in_value + @pct)
or upper(NAME) like upper(@pct + @in_value + @pct)
 +
order by 1,2,3
select left('PS_PERS_DATA_EFFDT' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,EFFDT
select left('PS_PERS_DATA_EFFDT' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,EFFDT
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 88: Line 94:
where upper(EMPLID) like upper(@in_value)
where upper(EMPLID) like upper(@in_value)
or upper(NAME) like upper(@pct + @in_value + @pct)
or upper(NAME) like upper(@pct + @in_value + @pct)
 +
order by 1,2,3,4
select left('PS_PERS_NID' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,COUNTRY,NATIONAL_ID_TYPE,NATIONAL_ID  
select left('PS_PERS_NID' COLLATE DATABASE_DEFAULT + @pad,30) as Record,EMPLID,COUNTRY,NATIONAL_ID_TYPE,NATIONAL_ID  
Line 93: Line 100:
where upper(EMPLID) like upper(@in_value)
where upper(EMPLID) like upper(@in_value)
or upper(NATIONAL_ID) like upper(@pct + @in_value + @pct)
or upper(NATIONAL_ID) like upper(@pct + @in_value + @pct)
 +
order by 1,2,3,4
select left('PS_HR_TBH_DATA' COLLATE DATABASE_DEFAULT + @pad,30) as Record,TBH_SEQ,RECNAME,FIELDNAME,TBH_CHAR  
select left('PS_HR_TBH_DATA' COLLATE DATABASE_DEFAULT + @pad,30) as Record,TBH_SEQ,RECNAME,FIELDNAME,TBH_CHAR  
from PS_HR_TBH_DATA  
from PS_HR_TBH_DATA  
where upper(TBH_CHAR) like upper(@pct + @in_value + @pct)
where upper(TBH_CHAR) like upper(@pct + @in_value + @pct)
 +
order by 1,2,3,4
select left('PSOPRDEFN' COLLATE DATABASE_DEFAULT + @pad,30) as Record,OPRID,EMPLID,OPRDEFNDESC  
select left('PSOPRDEFN' COLLATE DATABASE_DEFAULT + @pad,30) as Record,OPRID,EMPLID,OPRDEFNDESC  
Line 104: Line 113:
or upper(OPRDEFNDESC) like upper(@pct + @in_value + @pct)
or upper(OPRDEFNDESC) like upper(@pct + @in_value + @pct)
or upper(EMPLID) like upper(@pct + @in_value + @pct)
or upper(EMPLID) like upper(@pct + @in_value + @pct)
 +
order by 1,2,3,4
go
go
Line 114: Line 124:
               ##jstar '0024'
               ##jstar '0024'
***************************************************************************************/
***************************************************************************************/
 +
if exists (select * from tempdb..sysobjects where xtype='P' and name='##jstar')
drop procedure ##jstar
drop procedure ##jstar
go
go
Line 134: Line 145:
               ##msg 'text'
               ##msg 'text'
***************************************************************************************/
***************************************************************************************/
 +
if exists (select * from tempdb..sysobjects where xtype='P' and name='##msg')
drop procedure ##msg
drop procedure ##msg
go
go
Line 155: Line 167:
where
where
upper(HR_SSTEXT_TEXT) like upper('%' COLLATE DATABASE_DEFAULT +@in_text+'%' COLLATE DATABASE_DEFAULT)
upper(HR_SSTEXT_TEXT) like upper('%' COLLATE DATABASE_DEFAULT +@in_text+'%' COLLATE DATABASE_DEFAULT)
 +
order by 1,2,3,4
go
go
Line 170: Line 183:
               ##posstar '6000008'
               ##posstar '6000008'
***************************************************************************************/
***************************************************************************************/
 +
if exists (select * from tempdb..sysobjects where xtype='P' and name='##posstar')
drop procedure ##posstar
drop procedure ##posstar
go
go
Line 191: Line 205:
               where 321 is the TBH Sequence number (blank is latest)
               where 321 is the TBH Sequence number (blank is latest)
***************************************************************************************/
***************************************************************************************/
 +
if exists (select * from tempdb..sysobjects where xtype='P' and name='##tbh')
drop procedure ##tbh
drop procedure ##tbh
go
go
Line 234: Line 249:
               ##mgr '0024'
               ##mgr '0024'
***************************************************************************************/
***************************************************************************************/
-
 
+
if exists (select * from tempdb..sysobjects where xtype='P' and name='##mgr')
drop procedure ##mgr
drop procedure ##mgr
go
go
Line 308: Line 323:
               ##psquery 'Query'
               ##psquery 'Query'
***************************************************************************************/
***************************************************************************************/
-
 
+
if exists (select * from tempdb..sysobjects where xtype='P' and name='##psquery')
drop procedure ##psquery
drop procedure ##psquery
go
go
Line 415: Line 430:
               ##comparetables 'table1','table2','column1,column2...'
               ##comparetables 'table1','table2','column1,column2...'
***************************************************************************************/
***************************************************************************************/
-
 
+
if exists (select * from tempdb..sysobjects where xtype='P' and name='##comparetables')
drop procedure ##comparetables
drop procedure ##comparetables
go
go
Line 461: Line 476:
               ##comptab 'table1','table2','column1,column2...'
               ##comptab 'table1','table2','column1,column2...'
***************************************************************************************/
***************************************************************************************/
-
 
+
if exists (select * from tempdb..sysobjects where xtype='P' and name='##comptab')
drop procedure ##comptab
drop procedure ##comptab
go
go
Line 503: Line 518:
-
##comptab 'PS_DEPT_TBL','"SEVER1"','"SERVER1".DEV.dbo.PS_DEPT_TBL','DEPTID'
+
-- ##comptab 'PS_DEPT_TBL','"SERVER1"','"SERVER1".DEV.dbo.PS_DEPT_TBL','DEPTID'
-
go
+
-- go
select * from sys.servers
select * from sys.servers
Line 517: Line 532:
               returns 1 if field there, otherwise 0
               returns 1 if field there, otherwise 0
***************************************************************************************/
***************************************************************************************/
-
 
+
if exists (select * from tempdb..sysobjects where xtype='P' and name='##hasField')
drop procedure ##hasField
drop procedure ##hasField
go
go
Line 605: Line 620:
               ##abs '00007'
               ##abs '00007'
***************************************************************************************/
***************************************************************************************/
 +
if exists (select * from tempdb..sysobjects where xtype='P' and name='##abs')
drop procedure ##abs
drop procedure ##abs
go
go
Line 623: Line 639:
order by 1,2,3,4,5,6
order by 1,2,3,4,5,6
-
print 'PS_GPGB_KIT_DTL'
+
-- print 'PS_GPGB_KIT_DTL'
-
select 'PS_GPGB_KIT_DTL',* from PS_GPGB_KIT_DTL where EMPLID=@in_emplid
+
-- select 'PS_GPGB_KIT_DTL',* from PS_GPGB_KIT_DTL where EMPLID=@in_emplid
-
order by 1,2,3
+
-- order by 1,2,3
print 'PS_GPGB_ABS_CMTS'
print 'PS_GPGB_ABS_CMTS'
Line 662: Line 678:
               ##findmenu Establishment
               ##findmenu Establishment
***************************************************************************************/
***************************************************************************************/
 +
if exists (select * from tempdb..sysobjects where xtype='P' and name='##findmenu')
drop procedure ##findmenu
drop procedure ##findmenu
go
go
Line 697: 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 PORTAL_NAME
+
SELECT PORTAL_LABEL
-
     , PORTAL_LABEL
+
     , 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

Latest revision as of 20:52, 27 May 2015

Retrieved from "http://www.homemarketeer.com/wiki/index.php?title=Useful_Functions"
Personal tools