Using Expressions in PS Query - Revision history

WikiSysop at 06:39, 4 January 2016

2016-01-04T06:39:01Z

← Older revision		Revision as of 06:39, 4 January 2016
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.

WikiSysop at 20:40, 28 August 2015

2015-08-28T20:40:59Z

← Older revision		Revision as of 20:40, 28 August 2015
Line 53:		Line 53:
	\|[[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]]		\|[[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]], ~~get a copy of~~ our eBook:	+	\|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]

	\|}		\|}

WikiSysop at 19:36, 28 August 2015

2015-08-28T19:36:32Z

← Older revision		Revision as of 19:36, 28 August 2015
Line 49:		Line 49:
	==More About Functions==		==More About Functions==

-	For more information about functions in [[PeopleSoft Query]], get a copy of our eBook:	+	{\| 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]], get a copy of 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]
		+
		+	\|}

WikiSysop at 22:38, 27 August 2015

2015-08-27T22:38:48Z

← Older revision		Revision as of 22:38, 27 August 2015
Line 10:		Line 10:
	'''Examples'''		'''Examples'''
	<pre>		<pre>
-	'PS_' \|\| A.RECNAME	+	'PS_' \|\| A.RECNAME /* Gives the standard table name from the record name. */
	</pre>		</pre>
-	~~Gives the standard table name from the record name.~~	+


Line 26:		Line 26:
	'''Examples'''		'''Examples'''
	<pre>		<pre>
-	Substr(A.DESCR,3) - The description without the first three characters	+	Substr(A.DESCR,3) /* The description without the first three characters */
	</pre>		</pre>

Line 42:		Line 42:
	translate(NATIONAL_ID,		translate(NATIONAL_ID,
	'0123456789' \|\|'abcdefghijklmnopqrstuvwxyz'\|\|'ABCDEFGHIJKLMNOPQRSTUVWXYZ'		'0123456789' \|\|'abcdefghijklmnopqrstuvwxyz'\|\|'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
-	,rpad('9',10)\|\|rpad('x',26) \|\|rpad('X',26))	+	,rpad('9',10)\|\|rpad('x',26) \|\|rpad('X',26)) /* Format of NATIONAL_ID */

-	translate(~~TextField~~,'-0123456789','-') - Removes digits from ~~text~~ field.	+	translate(DESCR,'-0123456789','-') /* Removes digits from descriptiont field. */
	</pre>		</pre>

WikiSysop at 22:32, 27 August 2015

2015-08-27T22:32:29Z

← Older revision		Revision as of 22:32, 27 August 2015
Line 42:		Line 42:
	translate(NATIONAL_ID,		translate(NATIONAL_ID,
	'0123456789' \|\|'abcdefghijklmnopqrstuvwxyz'\|\|'ABCDEFGHIJKLMNOPQRSTUVWXYZ'		'0123456789' \|\|'abcdefghijklmnopqrstuvwxyz'\|\|'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
-	,rpad('9',10)\|\|rpad('x',26) \|\|rpad('X',26))	+	,rpad('9',10)\|\|rpad('x',26) \|\|rpad('X',26))

	translate(TextField,'-0123456789','-') - Removes digits from text field.		translate(TextField,'-0123456789','-') - Removes digits from text field.

WikiSysop at 22:30, 27 August 2015

2015-08-27T22:30:54Z

← Older revision		Revision as of 22:30, 27 August 2015
Line 41:		Line 41:
	<pre>		<pre>
	translate(NATIONAL_ID,		translate(NATIONAL_ID,
-	'0123456789'\|\|'abcdefghijklmnopqrstuvwxyz'\|\|'ABCDEFGHIJKLMNOPQRSTUVWXYZ'	+	'0123456789' \|\|'abcdefghijklmnopqrstuvwxyz'\|\|'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
-	,rpad('9',10)\|\|rpad('x',26)\|\|rpad('X',26))	+	,rpad('9',10)\|\|rpad('x',26) \|\|rpad('X',26))

	translate(TextField,'-0123456789','-') - Removes digits from text field.		translate(TextField,'-0123456789','-') - Removes digits from text field.

WikiSysop at 22:26, 27 August 2015

2015-08-27T22:26:55Z

← Older revision		Revision as of 22:26, 27 August 2015
Line 25:		Line 25:

	'''Examples'''		'''Examples'''
-		+	<pre>
-	:Substr(A.DESCR,3) - The description without the first three characters	+	Substr(A.DESCR,3) - The description without the first three characters
-		+	</pre>

	===Function: Translate ===		===Function: Translate ===
Line 39:		Line 39:

	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,	+	translate(NATIONAL_ID,
	'0123456789'\|\|'abcdefghijklmnopqrstuvwxyz'\|\|'ABCDEFGHIJKLMNOPQRSTUVWXYZ'		'0123456789'\|\|'abcdefghijklmnopqrstuvwxyz'\|\|'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
	,rpad('9',10)\|\|rpad('x',26)\|\|rpad('X',26))		,rpad('9',10)\|\|rpad('x',26)\|\|rpad('X',26))

-	:translate(TextField,'-0123456789','-') - Removes digits from text field.	+	translate(TextField,'-0123456789','-') - Removes digits from text field.
-		+	</pre>

	==More About Functions==		==More About Functions==

WikiSysop at 22:23, 27 August 2015

2015-08-27T22:23:55Z

← Older revision		Revision as of 22:23, 27 August 2015
Line 9:		Line 9:

	'''Examples'''		'''Examples'''
-	:'PS_' \|\| A.RECNAME - Gives the standard table name from the record name.	+	<pre>
		+	'PS_' \|\| A.RECNAME
		+	</pre>
		+	Gives the standard table name from the record name.
		+

	===Function: Substr ===		===Function: Substr ===
Line 23:		Line 27:

	:Substr(A.DESCR,3) - The description without the first three characters		:Substr(A.DESCR,3) - The description without the first three characters
		+

	===Function: Translate ===		===Function: Translate ===
Line 40:		Line 45:

	:translate(TextField,'-0123456789','-') - Removes digits from text field.		:translate(TextField,'-0123456789','-') - Removes digits from text field.
		+

	==More About Functions==		==More About Functions==

WikiSysop at 21:22, 25 August 2015

2015-08-25T21:22:01Z

← Older revision		Revision as of 21:22, 25 August 2015
Line 39:		Line 39:
	,rpad('9',10)\|\|rpad('x',26)\|\|rpad('X',26))		,rpad('9',10)\|\|rpad('x',26)\|\|rpad('X',26))

-
-
	:translate(TextField,'-0123456789','-') - Removes digits from text field.		:translate(TextField,'-0123456789','-') - Removes digits from text field.
		+
		+	==More About Functions==
		+
		+	For more information about functions in [[PeopleSoft Query]], get a copy of 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]

WikiSysop at 21:05, 25 August 2015

2015-08-25T21:05:47Z

← Older revision		Revision as of 21:05, 25 August 2015
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).