PS Query Lists

From TekiWiki
Revision as of 19:01, 19 March 2015 by WikiSysop (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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:

1: Create a new criterion - and make the left hand side an expression.

2: Leave the expression settings as their defaults - a character expression with a length of 1.

3: As an example, to make a list of Departments containing Sales, Marketing, Operations put the following into the expression list:

A.DEPTID in ('SALES','MARKETING','OPERATIONS') and 'a'

4: Click OK and save the expression and return to your new criterion.

5: Add the right hand side of the criterion as the constant value "a" (without the quotes)

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.

To convert a list of items, one to a line into a SQL "in" list:

1: Put the raw list in the left hand "source" box.

2: Depending on your platform, input the following into the "Regular Expression" box:

  • Windows
([^\r]*)
  • Mobile Devices
(.+)

3: In the "Replacement Text" box, type in:

'$&',

4: Click "Replace" and your comma separated list appears in the right hand box.

RegularExpressionList.jpg

The PeopleSoft Users Companion is a Kindle eBook packed with hints and tips on using the PeopleSoft web more effectively. Get your copy today.