PS Query Lists - Revision history

WikiSysop at 06:41, 2 May 2017

2017-05-02T06:41:06Z

WikiSysop at 06:39, 2 May 2017

2017-05-02T06:39:16Z

WikiSysop at 06:29, 8 July 2016

2016-07-08T06:29:50Z

WikiSysop: /* Creating the List */

2016-07-08T06:21:13Z

‎Creating the List

WikiSysop: /* Further Reading */

2016-07-08T06:07:00Z

‎Further Reading

WikiSysop at 06:05, 8 July 2016

2016-07-08T06:05:53Z

WikiSysop: 1 revision imported

2016-03-25T08:51:05Z

1 revision imported

WikiSysop at 18:01, 19 March 2015

2015-03-19T18:01:21Z

New page

[[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:
<pre>
A.DEPTID in ('SALES','MARKETING','OPERATIONS') and 'a'
</pre>
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 [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:

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
<pre>
<nowiki>([^\r]*)</nowiki>
</pre>
* Mobile Devices
<pre>
<nowiki>(.+)</nowiki>
</pre>
3: In the "Replacement Text" box, type in:
<pre>
<nowiki>'$&',</nowiki>
</pre>
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.''

← Older revision		Revision as of 06:41, 2 May 2017
Line 1:		Line 1:
−	~~You~~ can compare a field with a list of values ~~in [[PeopleSoft Query]] in~~ the ~~criteria~~. 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.	+	Within [[PeopleSoft Query]], you can compare a field with a list of values on the Criteria tab. 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:

← Older revision		Revision as of 06:39, 2 May 2017
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.	+	You can compare a field with a list of values in [[PeopleSoft Query]] in the criteria. 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 43: / Line 43: @@
 ''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.''
-==Further Reading==
+==Related Reading==
 {| class="wikitable"

← Older revision		Revision as of 06:05, 8 July 2016
Line 42:		Line 42:

	''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.''		''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.''
		+
		+	==Further Reading==
		+
		+	{\| 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]
		+
		+	\|}

@@ Line 19: / Line 19: @@
 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, using the online tool:
-: Put the raw list in the left hand "source" box.
+: Open the [http://www.springboardsolutions.com/regular_expression.html web address] in Chrome, Safari, Firefox or Internet Explorer
-: Depending on your platform, input the following into the "Regular Expression" box:
+: Put the raw list in the left hand "source" box.
 *Windows
@@ Line 33: / Line 35: @@
 <nowiki>(.+)</nowiki>
 </pre>
 : In the "Replacement Text" box, type in:
 <pre>
 <nowiki>'$&',</nowiki>
 </pre>
 : Click "Replace" and your comma separated list appears in the right hand box.
 Note that if using direct from Twitter, copy the web address to your browser rather than the Twitter version:

@@ Line 38: / Line 38: @@
 </pre>
 : Click "Replace" and your comma separated list appears in the right hand box.
 [[File:RegularExpressionList.jpg|link=http://www.springboardsolutions.com/regular_expression.html]]

← Older revision	Revision as of 08:51, 25 March 2016
(No difference)