PS Query Lists
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page PS Query Lists
| Line 1: | Line 1: | ||
| - | + | [[PeopleSoft Query]] has a way of comparing a list of values in the criteria to a particular column (or expression). You have to add each value individually - this is a bit of fun for a handful of values, but when the number of values in the list hit serious double figures, it becomes a bit tedious. | |
The list of values makes use of the "in" operator in SQL. If we already have the list in an email or spreadsheet, we can edit the list into the SQL format without having to rete the actual values, but then we need to get it into PS Query: | The list of values makes use of the "in" operator in SQL. If we already have the list in an email or spreadsheet, we can edit the list into the SQL format without having to rete the actual values, but then we need to get it into PS Query: | ||
| Line 9: | Line 9: | ||
3: As an example, to make a list of Departments containing Sales, Marketing, Operations put the following into the expression list: | 3: As an example, to make a list of Departments containing Sales, Marketing, Operations put the following into the expression list: | ||
<pre> | <pre> | ||
| - | A.DEPTID in ('SALES','MARKETING','OPERATIONS') and 'a' | + | A.DEPTID in ('SALES','MARKETING','OPERATIONS') and 'a' |
</pre> | </pre> | ||
4: Click OK and save the expression and return to your new criterion. | 4: Click OK and save the expression and return to your new criterion. | ||
| Line 17: | Line 17: | ||
==Creating the List== | ==Creating the List== | ||
| - | Creating a quoted, comma separated list can be done in a text editor, in a spreadsheet or in an online tool such as Regular Expression Calculator. | + | Creating a quoted, comma separated list can be done in a text editor, in a spreadsheet or in an online tool such as [http://www.springboardsolutions.com/regular_expression.html Regular Expression Calculator]. |
To convert a list of items, one to a line into a SQL "in" list: | To convert a list of items, one to a line into a SQL "in" list: | ||
| Line 27: | Line 27: | ||
*Windows | *Windows | ||
<pre> | <pre> | ||
| - | <nowiki>([^\r]*)< | + | <nowiki>([^\r]*)</nowiki> |
| + | </pre> | ||
| + | * Mobile Devices | ||
<pre> | <pre> | ||
<nowiki>(.+)</nowiki> | <nowiki>(.+)</nowiki> | ||
| Line 36: | Line 38: | ||
</pre> | </pre> | ||
4: Click "Replace" and your comma separated list appears in the right hand box. | 4: Click "Replace" and your comma separated list appears in the right hand box. | ||
| + | |||
| + | [[File:RegularExpressionList.jpg|link=http://www.springboardsolutions.com/regular_expression.html]] | ||
| + | |||
| + | ''The [http://www.amazon.co.uk/gp/product/B00S74S9MM/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=B00S74S9MM&linkCode=as2&tag=springboardso-21&linkId=X4GHKEBKKMKPCO24 PeopleSoft Users Companion] is a Kindle eBook packed with hints and tips on using the PeopleSoft web more effectively. Get your copy today.'' | ||
