Using Expressions in PS Query
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page Using Expressions in PS Query
| Line 1: | Line 1: | ||
| - | Expressions in PeopleSoft Query are based on the functions and capabilities of the underlying database platform. For instance, if you are using the Oracle database, you can make use of Oracle SQL functions in your Query, if using SQL Server - you can use SQL Server's SQL functions and so on. | + | Expressions in [[PeopleSoft Query]] are based on the functions and capabilities of the underlying database platform. For instance, if you are using the [[Oracle Database|Oracle database]], you can make use of Oracle SQL functions in your Query, if using [[Microsoft SQL Server|SQL Server]] - you can use SQL Server's SQL functions and so on. |
Although there are SQL standards, many of the SQL functions are not part of those standards. For this reason, the expressions listed below are Oracle SQL functions (hope to include SQL Server and other plaforms later). | Although there are SQL standards, many of the SQL functions are not part of those standards. For this reason, the expressions listed below are Oracle SQL functions (hope to include SQL Server and other plaforms later). | ||
| Line 6: | Line 6: | ||
===Function: || (Concatenate) === | ===Function: || (Concatenate) === | ||
'''Usage: 'Putting ' || A.COLUMN || ' and strings together' ''' | '''Usage: 'Putting ' || A.COLUMN || ' and strings together' ''' | ||
| + | |||
This concatenates (puts together) column values and fixed character values. This has got to be one of the most useful functions. | This concatenates (puts together) column values and fixed character values. This has got to be one of the most useful functions. | ||
'''Examples''' | '''Examples''' | ||
| - | + | <pre> | |
| + | 'PS_' || A.RECNAME /* Gives the standard table name from the record name. */ | ||
| + | </pre> | ||
| + | |||
| + | |||
===Function: Substr === | ===Function: Substr === | ||
'''Usage: substr(A.DESCR,start_position,number_of_characters) ''' | '''Usage: substr(A.DESCR,start_position,number_of_characters) ''' | ||
| + | |||
'''Usage: substr(A.DESCR,start_position) ''' | '''Usage: substr(A.DESCR,start_position) ''' | ||
| + | |||
'''Usage: substr(A.DESCR,-start_position,number_of_characters) ''' | '''Usage: substr(A.DESCR,-start_position,number_of_characters) ''' | ||
| + | |||
This extracts a part of a text column. | This extracts a part of a text column. | ||
'''Examples''' | '''Examples''' | ||
| - | + | <pre> | |
| + | Substr(A.DESCR,3) /* The description without the first three characters */ | ||
| + | </pre> | ||
| + | |||
| + | ===Function: Translate === | ||
| + | '''Usage: translate(A.DESCR,'List1','List2') ''' | ||
| + | |||
| + | '''Usage: translate(A.DESCR,'X1234567890','X') ''' | ||
| + | |||
| + | Translates characters in the column found in first list with corresponding character in the second list. | ||
| + | |||
| + | '''Examples''' | ||
| + | |||
| + | Show the format of NATIONAL_ID - useful in a group by query: | ||
| + | <pre> | ||
| + | translate(NATIONAL_ID, | ||
| + | '0123456789' ||'abcdefghijklmnopqrstuvwxyz'||'ABCDEFGHIJKLMNOPQRSTUVWXYZ' | ||
| + | ,rpad('9',10)||rpad('x',26) ||rpad('X',26)) /* Format of NATIONAL_ID */ | ||
| + | |||
| + | translate(DESCR,'-0123456789','-') /* Removes digits from descriptiont field. */ | ||
| + | </pre> | ||
| + | |||
| + | ==More About Functions== | ||
| + | |||
| + | {| class="wikitable" | ||
| + | |- | ||
| + | |[[File:UsingFunctionsInPeopleSoftQuerySmall.jpg|90px|link=http://www.amazon.co.uk/gp/product/B013ZRND0I/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=B013ZRND0I&linkCode=as2&tag=springboardso-21]] | ||
| + | |||
| + | |For more information about functions in [[PeopleSoft Query]] and some other interesting stuff too, read our eBook: | ||
| + | |||
| + | [http://www.amazon.co.uk/gp/product/B013ZRND0I/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=B013ZRND0I&linkCode=as2&tag=springboardso-21 Using Functions in PeopleSoft Query] | ||
| + | |||
| + | |} | ||
