Important PeopleSoft SQLs

• MicroSsoft SQL Server

Top few rows from a table, following will select
first 301 rows from a table.

SELECT
DISTINCT TOP 301 RUN_CNTL_ID,
LANGUAGE_CD
FROM
PS_PRCSRUNCNTL
WHERE
OPRID='PS'
AND
UPPER(RUN_CNTL_ID)
LIKE UPPER('%act')

• Oracle

Escape characters used in data.

Select * From STUDENTS Where STU_NAME Like '%\_%' Escape '\'

• Delete duplicate rows
  
  Delete From CLIENT_MASTER
  Where ROWID NOT IN
  ( Select min (ROWID)
  From CLIENT_MASTER
  Group By CLIENT_NO, NAME, BAL_DUE )

• In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

Select ID, FIRST_NAME, LAST_NAME, NVL(PHONE, 'Unknown Phone Number') As Phone From CUSTOMER