From the ceiling to the floor

As many of you know, you can use any XSL expressions in your templates, thats all well and good but you need to know XSL and being an Oracle product you are probably all SQL experts. What some of you may have missed in the documentation is that XMLP supports over 20 more familiar SQL expressions in the RTF template.
You can pad left and right, replace a maximum with a minimum, go from ceiling to the floor and finally work out the sub string with the greatest length ... OK OK we get the SQL references already!


So here they are, there is a caveat to using them thou, you can not mix these SQL expressions with XSL expressions. What do I mean by that? Well,

<?xdofx:rpad(concat(FIRST_NAME,LAST_NAME),30, 遅?)?>


will not work and XMLP will fall over, gracefully of course. You are mixing the 'rpad' function with the XSL concat expression ... thats  a no no! To get around it think SQL.

<?xdofx:rpad(FIRST_NAME ||LAST_NAME),30, 遅?)?>

Other than this rule you can use them to your hearts content and avoid a lot of heartache working out how to achieve say, 'rpad' in XSL. It can be done but whats the point when XMLP can do it for you.
















































































































































SQL Statement Usage XSL Equivalent Description
2+3 <?xdofx:2+3?> 2+3 Addition ... duh! I'd stick with the XSL version which happens to be the same
2-3 <?xdofx:2-3?> 2-3 Subtraction - as above
2*3 <?xdofx:2*3?> 2*3 Multiplication - as above again
2/3 <?xdofx:2/3?> 2 div 3 Division - use XSL 2 div 3 instead
2**3 <?xdofx:2**3?> No Exponential
2||3 <?xdofx:2||3?> concat(2,3) Concatenation
lpad(誕aa?,10,?.?) <?xdofx:lpad(誕aa?,10,?.?)?> No The lpad function pads the left side of a string with a specific set of characters. The syntax for the lpad function is:
lpad(string1,padded_length,[pad_
string])
string1 is the string to pad characters to (the left-hand side).
padded_length is the number of characters to return.
pad_string is the string that will be padded to the left-hand side of string1
rpad(誕aa?,10,?.?) <?xdofx:rpad(誕aa?,10,?.?)?> No The rpad function pads the right side of a string with a specific set of characters. The syntax for the rpad function is:
rpad(string1,padded_length,[pad_
string]).
string1 is the string to pad characters to (the right-hand side).
padded_length is the number of characters to return.
pad_string is the string that will be padded to the right-hand side of string1
decode(遅xx?,鍛bb?,団cc?,
遅xx?,壇dd?)
<?xdofx:decode(遅xx?,鍛bb?,団cc?,遅xx?,
壇dd?)?>
No The decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the
decode function is:
decode(expression, search, result
[,search, result]...[, default])
expression is the value to compare.
search is the value that is compared against
expression.
result is the value returned, if expression is equal to search.
default is returned if no matches are found.
Instr(誕bcabcabc?,誕?,2) <?xdofx:Instr(誕bcabcabc?,誕?,2)?> No The instr function returns the location of a substring in a string. The syntax for the instr
function is:
instr(string1,string2,[start_
position],[nth_appearance])
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. The first position in the
string is 1. If the start_position is negative, the function counts back start_position number of
characters from the end of string1 and then searches towards the beginning of string1.
nth appearance is the nth appearance of string2.
substr(誕bcdefg?,2,3) <?xdofx:substr(誕bcdefg?,2,3)?> substring() The substr function allows you to extract a substring from a string. The syntax for the substr function is:
substr(string, start_position,
[length])
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is the number of characters to extract.
replace(name,谷ohn?,谷on?) <?xdofx:replace(name,谷ohn?,谷on?)?> translate() The replace function replaces a sequence of characters in a string with another set of
characters. The syntax for the replace function
is:
replace(string1,string_to_replace,[replacement_
string])
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced with
replacement_string in string1.
to_number(?12345?) <?xdofx:to_number(?12345?)?> number() Function to_number converts char, a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.
to_char(12345) <?xdofx:to_char(?12345?)?> N/A Use the TO_CHAR function to translate a value of NUMBER datatype to VARCHAR2 datatype.
to_date <?xdofx:to_date ( char [, fmt[, 地lsparam綻] ) No TO_DATE converts char of CHAR,
VARCHAR2, NCHAR, or NVARCHAR2
datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, then char must be in the default date format. If fmt is 谷?, for Julian, then char must be an integer.
sysdate() <?xdofx:sysdate()?> No SYSDATE returns the current date and time. The datatype of the returned value is DATE. The function requires no arguments.
minimum <?xdoxslt:minimum(ELEMENT_
NAME)?>
No Returns the minimum value of the element in the set.
maximum <?xdoxslt:maximum(ELEMENT_
NAME)?>
No Returns the maximum value of the element in the set
chr <?xdofx:chr(n)?> No CHR returns the character having the binary equivalent to n in either the database character set or the national character set.
ceil <?xdofx:ceil(n)?> ceiling() CEIL returns smallest integer greater than or equal to n.
floor <?xdofx:floor(n)?> floor() FLOOR returns largest integer equal to or less than n.
round <?xdofx:round ( number [, integer ] )?> No ROUND returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0
places. integer can be negative to round off digits left of the decimal point. integer must be an integer.
lower <?xdofx:lower (char)?> No LOWER returns char, with all letters lowercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2,
CLOB, or NCLOB. The return value is the same datatype as char.
upper <?xdofx:upper(char)?> No UPPER returns char, with all letters
uppercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char.
length <?xdofx:length(char)?> string-length() The "length" function returns the length of char. LENGTH calculates length using characters as defined by the input character set.
greatest <?xdofx:greatest ( expr [, expr]... )?> No GREATEST returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison.
least <?xdofx:least ( expr [, expr]... )?> No LEAST returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison.


Thats great but can I combine them ... of course you can:


 decode(誕?,鍛?,団?,壇?,弾?,?1?)||instr(?321?,1,1)


in SQL becomes


<?xdofx:decode(誕?,鍛?,団?,壇?,弾?,?1?)||instr(?321?,1,1)?>


in XMLP speak.


Great rule of thumb for these functions, if there is an XSL equivalent ... use it!


Have a good one!

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today