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 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> | <pre> | ||
| - | 'PS_' || A.RECNAME | + | 'PS_' || A.RECNAME /* Gives the standard table name from the record name. */ |
</pre> | </pre> | ||
| - | + | ||
| Line 25: | Line 26: | ||
'''Examples''' | '''Examples''' | ||
| - | + | <pre> | |
| - | + | Substr(A.DESCR,3) /* The description without the first three characters */ | |
| - | + | </pre> | |
===Function: Translate === | ===Function: Translate === | ||
| Line 39: | Line 40: | ||
Show the format of NATIONAL_ID - useful in a group by query: | 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== | ==More About Functions== | ||
| - | For more information about functions in [[PeopleSoft Query]], | + | {| 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] | [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] | ||
| + | |||
| + | |} | ||
