Category: Oracle

Technology - What Are The SQL Minus and Except SQL Clauses

What Are The SQL Minus and Except Clauses

Structured Query Language (SQL) is the de-facto query language used in most database management systems (DBMS) such as Oracle and Microsoft SQL Server. This domain-specific language is used in programming to query and return the desired data from a database. We use SQL to write queries that declare what data to expect from a dataset without really indicating how to obtain it. We can also use SQL to update and delete information from a database. Ideally, in a relational database management system, the database usually runs on the “back end” side of a server in its structured form. By itself,

Continue reading

SQL Server Length Function Equivalent

The purpose of the Length function in SQL The SQL LENGTH function returns the number of characters in a string. The LENGTH function is available in many Database Management Systems (DBMS). The LENGTH Function Syntax LENGTH(string) LENGTH Function Notes If the input string is empty, the LENGTH returns 0. If the input string is NULL, the LENGTH returns NULL. Length Function Across Databases When working as a technical consultant, one has to work with customer’s databases and as you move from one database to another you will find that the function commands may vary–assuming the database has an equivalent function.

Continue reading

Oracle SQL Date Formatting Examples

Below is a SQL of various examples of Oracle date formats, which I have found to be a useful reference. Select SYSDATE, TO_CHAR(SYSDATE,’YYYY-MM-DD AD’) AS ANNO_DOMINI, TO_CHAR(SYSDATE,’YYYYMMDD’) AS SORT_PATTERN1, TO_CHAR(SYSDATE,’YYYY/MM/DD’) AS DATE_PATTERN1, TO_CHAR(SYSDATE,’DD/MM/YYYY’) AS DATE_PATTERN2, TO_CHAR(SYSDATE,’DD/MON/YYYY’) AS DATE_PATTERN3, TO_CHAR(SYSDATE,’DD-MON-YY’) AS DATE_PATTERN4, TO_CHAR(SYSDATE,’CC’) AS CENTURY, TO_CHAR(SYSDATE,’YYYY’) AS FOUR_DIGIT_YEAR, TO_CHAR(SYSDATE,’YY’) AS YEAR_OF_CENTURY, TO_CHAR(SYSDATE,’J’) AS JUALIAN_DATE, TO_CHAR(SYSDATE,’Q’) AS CALENDAR_QUARTER_OF_YEAR, TO_CHAR(SYSDATE,’WW’) AS CALENDAR_WEEK_OF_YEAR, TO_CHAR(SYSDATE,’DDD’) AS NUMBER_OF_CALENDAR_DAY_OF_YEAR, TO_CHAR(SYSDATE,’W’) AS WEEK_OF_MONTH, TO_CHAR(SYSDATE,’DAY’) AS NAME_OF_DAY_OF_WEEK, TO_CHAR(SYSDATE,’DY’) AS ABBREVIATED_DAY_OF_WEEK, TO_CHAR(SYSDATE,’MM’) AS NUMERIC_MONTH_OF_YEAR, TO_CHAR(SYSDATE,’MON’) AS ABBREVIATED_MONTH_OF_YEAR, TO_CHAR(SYSDATE,’MONTH’) AS SPELLED_OUT_MONTH_OF_YEAR FROM DUAL; Related References Oracle Database – Useful links Oracle SQL – How to limit the number of rows returned

Continue reading

PS Query Timestamp to Date Logic

Converting a timestamp to date in PeopleSoft Query seems to be a common item, which I have helped others with, to the point that I kept a personal note on the coding logic.  So, I thought I would post this quick note in case it may be useful to someone.  This item would seem to be straight forward, however, PS query tries to help you with the process, so, some experimentation is usually involved to get the code to out just right. Here is a sample expression I use for the timestamp to date conversion in PS Query. Create an

Continue reading

PS Query Migration Validation SQLs

While PeopleSoft PeopleTools Application Designer is the best way to validate query migrations, sometimes a few SQL scripts can be helpful when verifying that queries and associated tree exist in an environment with the necessary nodes.  This SQL can be run in the appropriate SQL editor and was written against an Oracle RDBMS. Is The Query in the Environment? SELECT DISTINCT QRYNAME FROM PSQRYFIELD WHERE QRYNAME LIKE ’<<QUERYNAME>>%’; Is Query Tree in Environment? SELECT TREE_NAME FROM PSTREENODE WHERE TREE_NAME =’<<TREENAME>>’ ORDER BY 1; Is Record in Query Security Tree? SELECT TREE_NODE FROM PSTREENODE WHERE TREE_NAME =’<<TREENAME>>’ AND TREE_NODE LIKE ’%<<RecordName>>%’;

Continue reading

PeopleSoft XLAT Value Lookup SQL

I’ve had this SQL snippet laying around for a while, so, before I lose it, I thought I would post it for future reference.  Below is SQL to lookup a XLAT values, in case you don’t want to or cannot go through PeopleSoft Application Designer. XLAT Lookup SQL SELECT XLAT.FIELDNAME, XLATFIELDVALUE, XLAT.XLATLONGNAME FROM PSXLATITEM XLAT WHERE XLAT.FIELDNAME = ‘<<FieldName>>’ AND XLAT.EFF_STATUS = ‘A’ AND XLAT.EFFDT = ( SELECT MAX(XLAT1.EFFDT) FROM PSXLATITEM XLAT1 WHERE  XLATFIELDNAME =XLAT1.FIELDNAME AND XLATFIELDVALUE = XLAT1.FIELDVALUE) ODER BY XLAT.FIELDVALUE

Continue reading
%%footer%%