8Advanced Queries
Advanced Queries
Introduction
In this section you will find examples of queries in SQL and their equivalent in XML. These examples can be used as models to create custom queries. Since the XML queries are converted to Oracle SQL queries during the extraction process, you will find a table of reserved words that can not be used as an alias in your XML queries.
Inserting Complex Queries
Taleo Connect Client is running and ready for use.
An export editor based on the entity on which the complex queries will be applied is open.
This procedure applies to complex projections, complex filters, complex sorting, complex projection sorting, and all advanced tab functions.
Click on Projections tab.
Enter or paste code in Complex projection pane.
<quer:projection alias="Candidate_Anonymous" xmlns:quer="http://www.taleo.com/ws/integration/query">
<quer:switchByValue>
<quer:baseValue>
<quer:field path="Candidate.Anonymous"/>
</quer:baseValue>
<quer:cases>
<quer:case>
<quer:integer>1</quer:integer>
<quer:string>Anonymous candidate</quer:string>
</quer:case>
<quer:case>
<quer:integer>0</quer:integer>
<quer:string>Non anonymous candidate</quer:string>
</quer:case>
</quer:cases>
<quer:defaultValue>
<quer:string>Unknown</quer:string>
</quer:defaultValue>
</quer:switchByValue>
</quer:projection>
Examples
Between
SQL
SELECT CSUSER01.CSUSERNO AS CSUSERNO01 FROM CSUSER CSUSER01 WHERE (CSUSER01.CSUSERNO BETWEEN 1 AND 10))
XML query API
<filtering>
<between
<field path="no"/>
<integer>1</integer>
<integer>10</integer>
</between>
</filtering>
Case (Switch by criterion)
SQL
SELECT CASE
WHEN (CSUSER01.ANONYMOUS=1) THEN
'Anonymous candidate'
WHEN (CSUSER01.ANONYMOUS=0) THEN
'Non anonymous candidate'
ELSE
'Unknown candidate'
END AS firstName01
FROM CSUSER CSUSER01
XML query API
<projection projectedField="firstName">
<switchByCriterion>
<cases>
<case>
<isTrue>
<field path="anonymous"/>
</isTrue>
<string>Anonymous candidate</string>
</case>
<case>
<isFalse>
<field path="anonymous"/>
</isFalse>
<string>Non anonymous candidate</string>
</case>
</cases>
<defaultValue>
<string>Unknown candidate</string>
</defaultValue>
</switchByCriterion>
</projection>
Case (Switch by value)
SQL
SELECT CSUSER01.CSUSERNO AS CSUSERNO01,
CASE CSUSER01.ANONYMOUS
WHEN 1 THEN
'Anonymous candidate'
WHEN 0 THEN
'Non anonymous candidate'
ELSE
'Unknonw candidate'
END AS firstName01
FROM CSUSER CSUSER01
XML query API
<projection projectedField="firstName">
<switchByValue>
<baseValue>
<field path="anonymous"/>
</baseValue>
<cases>
<case>
<boolean>true</boolean>
<string>Anonymous candidate</string>
</case>
<case>
<boolean>false</boolean>
<string>Non anonymous candidate</string>
</case>
</cases>
<defaultValue>
<string>Unknown candidate</string>
</defaultValue>
</switchByValue>
</projection>
Concatenate
SQL
SELECT ((CSUSER01.FIRSTNAME || ' ') || CSUSER01.LASTNAME) AS FIRSTNAME01 FROM CSUSER CSUSER01
XML query API
<query alias="testConcatenate" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection projectedField="firstName">
<concatenate>
<concatenate>
<field path="firstName"/>
<string> </string>
</concatenate>
<field path="lastName"/>
</concatenate>
</projection>
</projections>
</query>
Connect By
SQL
SELECT STUDYLEVEL01.STUDYLEVELNO AS STUDYLEVELNO01 FROM STUDYLEVEL STUDYLEVEL01 WHERE 1=1 CONNECT BY PRIOR STUDYLEVEL01.STUDYLEVELNO=STUDYLEVEL01.PARENTSTUDYLEVELNO START WITH (STUDYLEVEL01.STUDYLEVELNO=5)
XML query API
<query alias="testHierarchicalFiltering" projectedClass="com.taleo.akirademo.entity.StudyLevel">
<projections>
<projection>
<field path="no"/>
</projection>
</projections>
<hierarchicalFilterings>
<hierarchicalFiltering>
<hierarchicalFilter>
<equal>
<field path="no"/>
<field path="parent"/>
</equal>
</hierarchicalFilter>
<startWithFilter>
<equal>
<field path="no"/><integer>5</integer>
</equal>
</startWithFilter>
</hierarchicalFiltering>
</hierarchicalFilterings>
</query>
Count
SQL
SELECT COUNT((CSUSER01.CSUSERNO)) AS keyCount FROM CSUSER CSUSER01
XML query API
<query alias="testCount" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="keyCount">
<count>
<field path="no"/>
</count>
</projection>
</projections>
</query>
dbms_lob.getlength
SQL
SELECT dbms_lob.getlength(CSUSERATTACHEDFILE01.FILECONTENT) AS blobLength FROM CSUSERATTACHEDFILE CSUSERATTACHEDFILE01 WHERE (CSUSERATTACHEDFILE01.CSUSERATTACHEDFILENO=10291)
XML query API
<query alias="testLobLength" projectedClass="com.taleo.akirademo.entity.AttachedFile">
<projections>
<projection alias="blobLength">
<lobLength>
<field path="fileContent"/>
</lobLength>
</projection>
</projections>
<filterings>
<filtering>
<equal>
<field path="no"/>
<integer>10291</integer>
</equal>
</filtering>
</filterings>
</query>
dbms_lob.instr
SQL
SELECT DBMS_LOB.INSTR(CSUSERATTACHEDFILE01.FILECONTENT, 'a') AS blobIndexOf FROM CSUSERATTACHEDFILE CSUSERATTACHEDFILE01 WHERE (CSUSERATTACHEDFILE01.CSUSERATTACHEDFILENO=10291)
XML query API
<query alias="testLobIndexOf" projectedClass="com.taleo.akirademo.entity.AttachedFile" >
<projections>
<projection alias="blobIndexOf">
<lobIndexOf>
<field path="fileContent"/>
<string>205465737420</string>
</lobIndexOf>
</projection>
</projections>
<filterings>
<filtering>
<equal>
<field path="no"/>
<integer>10291</integer>
</equal>
</filtering>
</filterings>
</query>
dbms_lob.substr
SQL
SELECT DBMS_LOB.SUBSTR(CSUSERATTACHEDFILE01.FILECONTENT, 3, 4) AS blobSubstring FROM CSUSERATTACHEDFILE CSUSERATTACHEDFILE01 WHERE (CSUSERATTACHEDFILE01.CSUSERATTACHEDFILENO=10291)
XML query API
<query alias="testLobSubString" projectedClass="com.taleo.akirademo.entity.AttachedFile">
<projections>
<projection alias="blobSubstring" projectedValueType="string">
<lobSubstring>
<field path="fileContent"/>
<integer>4</integer>
<integer>3</integer>
</lobSubstring>
</projection>
</projections>
<filterings>
<filtering>
<equal>
<field path="no"/>
<integer>10291</integer>
</equal>
</filtering>
</filterings>
</query>
Decode
SQL
SELECT DECODE(CSUSER01.FIRSTNAME, 'Sebastien', 'Seb', 'Benoit', 'Ben', CSUSER01.FIRSTNAME) AS FIRSTNAME01 FROM CSUSER CSUSER01 WHERE 1=1
XML query API
<query alias="testDecode" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<decode>
<field path="firstName"/>
<string>Sebastien</string>
<string>Seb</string>
<string>Benoit</string>
<string>Ben</string>
<field path="firstName"/>
</decode>
</projection>
</projections>
</query>
Distinct
SQL
select distinct currency from csuser
XML query API
<query alias="subQuery" preventDuplicates="true" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="currency">
<field path="currency"/>
</projection>
</projections>
</query>
Dual
SQL
SELECT 1 FROM DUAL
XML query API
<query alias="MyDualQuery" projectedClass="DUAL_META_CLASS">
<projections>
<projection alias="myProjection">
<integer>1</integer>
</projection>
</projections>
</query>
Exists / Not Exists
SQL
SELECT CSUSER01.FIRSTNAME AS FIRSTNAME01
FROM CSUSER CSUSER01
WHERE (EXISTS
(SELECT CSUSER02.SALARY AS SALARY01 FROM CSUSER CSUSER02 WHERE 1=1))
SELECT CSUSER01.FIRSTNAME AS FIRSTNAME01
FROM CSUSER CSUSER01
WHERE NOT (EXISTS
(SELECT CSUSER02.SALARY AS SALARY01 FROM CSUSER CSUSER02 WHERE 1=1))
XML query API
<query alias="testExists" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="firstName"/>
</projection>
</projections>
<filterings>
<filtering>
<returnsResults>
<query alias="subQuery" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="salary"/>
</projection>
</projections>
</query>
</returnsResults>
</filtering>
</filterings>
</query>
<query alias="testExists" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="firstName"/>
</projection>
</projections>
<filterings>
<filtering>
<not>
<returnsResults>
<query alias="subQuery" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="salary"/>
</projection>
</projections>
</query>
</returnsResults>
</not>
</filtering>
</filterings>
</query>
Greater Than
SQL
SELECT CSUSER01.CSUSERNO AS CSUSERNO01 FROM CSUSER CSUSER01 WHERE (CSUSER01.SALARY > 50)
XML query API
<query alias="testGreatherThan" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="no"/>
</projection>
</projections>
<filterings>
<filtering>
<greaterThan>
<field path="salary"/>
<integer>50</integer>
</greaterThan>
</filtering>
</filterings>
</query>
Group By
SQL
SELECT APPLICATION01.CANDIDATENO AS candidate,
COUNT(APPLICATION01.CANDIDATENO) AS appCount
FROM APPLICATION APPLICATION01
WHERE 1=1
GROUP BY APPLICATION01.CANDIDATENO
XML query API
<query alias="testReportQueryWithGrouping" projectedClass="com.taleo.akirademo.entity.BaseApplication">
<projections>
<projection alias="candidate">
<field path="candidate"/>
</projection>
<projection alias="appCount">
<count>
<field path="candidate"/>
</count>
</projection>
</projections>
<groupings>
<grouping>
<field path="candidate"/>
</grouping>
</groupings>
</query>
Having
SQL
SELECT CSUSER01.CITY AS CITY01, COUNT(CSUSER01.CITY) AS cityCount FROM CSUSER CSUSER01 WHERE 1=1 GROUP BY CSUSER01.CITY HAVING(COUNT(CSUSER01.CITY) > 5)
XML query API
<query alias="testGroupingFilteringInReportQuery" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="city"/>
</projection>
<projection alias="cityCount">
<count>
<field path="city"/>
</count>
</projection>
</projections>
<groupings>
<grouping>
<field path="city"/>
</grouping>
</groupings>
<groupingFilterings>
<groupingFiltering>
<greaterThan>
<count>
<field path="city"/>
</count>
<integer>5</integer>
</greaterThan>
</groupingFiltering>
</groupingFilterings>
</query>
In
SQL
CSUSER01.CSUSERNO AS entityKey
FROM CSUSER CSUSER01
WHERE (CSUSER01.CSUSERNO IN (SELECT CSUSER02.CSUSERNO AS entityKey
FROM CSUSER CSUSER02
WHERE (CSUSER02.FIRSTNAME LIKE 's%' ESCAPE '\')))
SELECT CSUSER01.CSUSERNO AS entityKey FROM CSUSER CSUSER01 WHERE (CSUSER01.CSUSERNO IN (1, 2, 3))
XML query API
<query alias="testIncludedIn" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<includedIn>
<field path="no"/>
<query alias="candidateSubQuery" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>s*</string>
</matches>
</filtering>
</filterings>
</query>
</includedIn>
</filtering>
</filterings>
</query>
<query alias="testIncludedIn" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<includedIn>
<field path="no"/>
<list>
<integer>1</integer>
<integer>2</integer>
<integer>3</integer>
</list>
</includedIn>
</filtering>
</filterings>
</query>
Inner Join
SQL
SELECT APPLICATIONTRACKINGHISTORY01.APPLICATIONTRACKINGHISTORYNO AS APPLICATIONTRACKINGHISTORYNO01
FROM APPLICATIONTRACKINGHISTORY APPLICATIONTRACKINGHISTORY01,
APPLICATION APPLICATION01,
CSUSER CSUSER01
WHERE APPLICATION01.CANDIDATENO=APPLICATIONTRACKINGHISTORY01.CANDIDATENO
AND APPLICATION01.REQUISITIONNO =
APPLICATIONTRACKINGHISTORY01.REQUISITIONNO
AND APPLICATION01.PROFILELOCALE =
APPLICATIONTRACKINGHISTORY01.PROFILELOCALE
AND CSUSER01.CSUSERNO=APPLICATION01.CANDIDATENO
AND ((CSUSER01.CSUSERNO) > 0)
XML query API
<query alias="testInnerJoin" projectedClass="com.taleo.akirademo.entity.ApplicationTrackingHistoryItem">
<projections>
<projection>
<field path="no"/>
</projection>
</projections>
<filterings>
<filtering>
<greaterThan><key path="application=,candidate="/>
<integer>0</integer>
</greaterThan>
</filtering>
</filterings>
</query>
Intersect
SQL
SELECT CSUSER01.CSUSERNO AS entityKey FROM CSUSER CSUSER01 WHERE (CSUSER01.FIRSTNAME LIKE 'a%' ESCAPE '\') INTERSECT SELECT CSUSER02.CSUSERNO AS entityKey FROM CSUSER CSUSER02 WHERE (CSUSER02.FIRSTNAME LIKE 's%' ESCAPE '\')
XML query API
<intersect alias="testIntersect">
<queries>
<query alias="testIntersect2" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>a*</string>
</matches>
</filtering>
</filterings>
</query>
<query alias="candidateSubQuery" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>s*</string>
</matches>
</filtering>
</filterings>
</query>
</queries>
</intersect>
Less Than
SQL
SELECT CSUSER01.CSUSERNO AS CSUSERNO01 FROM CSUSER CSUSER01 WHERE (CSUSER01.SALARY < 50)
XML query API
<query alias="testGreatherThan" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="no"/>
</projection>
</projections>
<filterings>
<filtering>
<lessThan>
<field path="salary"/>
<integer>50</integer>
</lessThan>
</filtering>
</filterings>
</query>
Like
SQL
SELECT CSUSER01.CSUSERNO AS entityKey FROM CSUSER CSUSER01 WHERE (CSUSER01.FIRSTNAME LIKE 's%' ESCAPE '\')
XML query API
<query alias="testLike" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>s*</string>
</matches>
</filtering>
</filterings>
</query>
Minus
SQL
SELECT CSUSER01.CSUSERNO AS entityKey FROM CSUSER CSUSER01 WHERE (CSUSER01.FIRSTNAME LIKE 'a%' ESCAPE '\') MINUS SELECT CSUSER02.CSUSERNO AS entityKey FROM CSUSER CSUSER02 WHERE (CSUSER02.FIRSTNAME LIKE 's%' ESCAPE '\')
XML query API
<minus alias="testMinus">
<queries>
<query alias="candidate" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>a*</string>
</matches>
</filtering>
</filterings>
</query>
<query alias="candidate2" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>s*</string>
</matches>
</filtering>
</filterings>
</query>
</queries>
</minus>
NVL
SQL
SELECT NVL(CSUSER01.FIRSTNAME, 'Unknown') AS FIRSTNAME01 FROM CSUSER CSUSER01 WHERE 1=1
XML query API
<query alias="testReplaceNull" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<replaceNull>
<field path="firstName"/>
<string>Unknown</string>
</replaceNull>
</projection>
</projections>
</query>
Or
SQL
SELECT CSUSER01.CSUSERNO AS entityKey
FROM CSUSER CSUSER01
WHERE ((CSUSER01.FIRSTNAME LIKE 'a%' ESCAPE '\') OR
(CSUSER01.FIRSTNAME='Sam'))
XML query API
<query alias="testIntersect2" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<or>
<matches>
<field path="firstName"/>
<string>a*</string>
</matches>
<equal>
<field path="firstName"/>
<string>Sam</string>
</equal>
</or>
</filtering>
</filterings>
</query>
Order By
SQL
select csuserno from csuser order by csuserno asc
select csuserno from csuser order by csuserno desc
XML query API
<query alias="testOrderByAsc" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="no" />
</projection>
</projections>
<sortings>
<sorting ascending="true">
<field path="no" />
</sorting>
</sortings>
</query>
<query alias="testOrderByDesc" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="no" />
</projection>
</projections>
<sortings>
<sorting ascending="false">
<field path="no" />
</sorting>
</sortings>
</query>
Outer Join
SQL
SELECT APPLICATION01.CANDIDATENO AS CANDIDATENO01
FROM APPLICATIONTRACKINGHISTORY APPLICATIONTRACKINGHISTORY01,
APPLICATION APPLICATION01
WHERE APPLICATION01.CANDIDATENO(+) =
APPLICATIONTRACKINGHISTORY01.CANDIDATENO
AND APPLICATION01.REQUISITIONNO(+) =
APPLICATIONTRACKINGHISTORY01.REQUISITIONNO
AND APPLICATION01.PROFILELOCALE(+) =
APPLICATIONTRACKINGHISTORY01.PROFILELOCALE
XML query API
<query alias="testOuterJoin" projectedClass="com.taleo.akirademo.entity.ApplicationTrackingHistoryItem">
<projections>
<projection>
<field path="application+,candidate+"/>
</projection>
</projections>
</query>
Rownum
SQL
SELECT CSUSER01.CSUSERNO AS CSUSERNO01, rowNum AS rowIndex FROM CSUSER CSUSER01 WHERE 1=1
XML query API
<query alias="testRowindex" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<field path="no"/>
</projection>
<projection alias="rowIndex">
<customValue>
<expression>rowNum</expression>
</customValue>
</projection>
</projections>
</query>
SubString
SQL
SELECT SUBSTR(CSUSER01.LASTNAME, 0, 2) AS LASTNAME01 FROM CSUSER CSUSER01 WHERE 1=1
XML query API
<query alias="testSubString" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<substring>
<field path="lastName"/>
<integer>0</integer>
<integer>2</integer>
</substring>
</projection>
</projections>
</query>
ToChar
SQL
SELECT TO_CHAR(LOCALTIMESTAMP(0)) AS tocharnow,
TO_CHAR(LOCALTIMESTAMP(0), 'yyyy-MM-dd') AS tocharnowFormat,
TO_CHAR(2.24567, '0.99') AS tochardouble
FROM Dual Dual01
WHERE 1=1
XML query API
<query alias="testToCharFunction" projectedClass="DUAL_META_CLASS">
<projections>
<projection alias="tocharnow">
<toChar>
<date type="now" xsi:nil="true"/>
</toChar>
</projection>
<projection alias="tocharnowFormat">
<toChar>
<date type="now" xsi:nil="true"/>
<string bound="false">yyyy-MM-dd</string>
</toChar>
</projection>
<projection alias="tochardouble">
<toChar>
<double>2.7689340174</double>
<string bound="false">0.99</string>
</toChar>
</projection>
</projections>
</query>
Trim
SQL
SELECT TRIM(CSUSER01.LASTNAME) AS LASTNAME01 FROM CSUSER CSUSER01 WHERE 1=1
XML query API
<query alias="testTrim" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<trim>
<field path="lastName"/>
</trim>
</projection>
</projections>
</query>
Truncate
SQL
SELECT TRUNC(CSUSER01.SALARY) AS SALARY01 FROM CSUSER CSUSER01 WHERE 1=1
XML query API
<query alias="testTruncate" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<truncate>
<field path="salary"/>
</truncate>
</projection>
</projections>
</query>
Union
SQL
SELECT CSUSER01.CSUSERNO AS entityKey FROM CSUSER CSUSER01 WHERE (CSUSER01.FIRSTNAME LIKE 'a%' ESCAPE '\') UNION SELECT CSUSER02.CSUSERNO AS entityKey FROM CSUSER CSUSER02 WHERE (CSUSER02.FIRSTNAME LIKE 's%' ESCAPE '\')
XML query API
<union alias="testUnion">
<queries>
<query alias="testUnion2" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>a*</string>
</matches>
</filtering>
</filterings>
</query>
<query alias="candidateSubQuery" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>s*</string>
</matches>
</filtering>
</filterings>
</query>
</queries>
</union>
Union All
SQL
SELECT CSUSER01.CSUSERNO AS entityKey FROM CSUSER CSUSER01 WHERE (CSUSER01.FIRSTNAME LIKE 'a%' ESCAPE '\') UNION ALL SELECT CSUSER02.CSUSERNO AS entityKey FROM CSUSER CSUSER02 WHERE (CSUSER02.FIRSTNAME LIKE 's%' ESCAPE '\')
XML query API
<unionAllowDuplicates alias="testUnionAll">
<queries>
<query alias="testUnion2" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>a*</string>
</matches>
</filtering>
</filterings>
</query>
<query alias="candidateSubQuery" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection alias="entityKey" tag="entityKey">
<key path=""/>
</projection>
</projections>
<filterings>
<filtering>
<matches>
<field path="firstName"/>
<string>s*</string>
</matches>
</filtering>
</filterings>
</query>
</queries>
</unionAllowDuplicates>
Other Custom Functions
SQL
SELECT CUSTOM_FUNCTION_NAME(myField,'param1','param2') AS myField01 FROM CSUSER CSUSER01 WHERE 1=1
XML query API
<query alias="testCustomFunction" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<customFunction name="CUSTOM_FUNCTION_NAME">
<field path="myField"/>
<string>param1</string>
<string>param2</string>
</customFunction>
</projection>
</projections>
</query>
Custom Function Example
SQL
SELECT REGEXP_REPLACE(CSUSER01.USERNAME,?,?) AS USERNAME01 FROM CSUSER CSUSER01 WHERE 1=1
XML query API
<query alias="testRegexpReplace" projectedClass="com.taleo.akirademo.entity.Candidate">
<projections>
<projection>
<customFunction name="REGEXP_REPLACE">
<field path="username"/>
<string>a</string>
<string>b</string>
</customFunction>
</projection>
</projections>
</query>
Oracle Reserved Words
Oracle reserved words cannot be used as an Alias for an export query. The following table list words that are reserved in Oracle.
| Oracle Reserved Words | |||
|---|---|---|---|
ABORT |
ACCEPT |
ACCESS |
ADD |
ALL |
ALTER |
AND |
ANY |
ARRAY |
ARRAYLEN |
AS |
ASC |
ASSERT |
ASSIGN |
AT |
AUDIT |
AUTHORIZATION |
AVG |
BASE_TABLE |
>BEGIN |
BETWEEN |
BINARY_INTEGER |
BODY |
BOOLEAN |
BY |
CASE |
CHAR |
CHAR_BASE |
CHECK |
CLOSE |
CLUSTER |
CLUSTERS |
COLAUTH |
COLUMN |
COMMENT |
COMMIT |
COMPRESS |
CONNECT |
CONSTANT |
CRASH |
CREATE |
CURRENT |
CURRVAL |
CURSOR |
DATA_BASE |
DATABASE |
DATE |
DBA |
DEBUGOFF |
DEBUGON |
DECIMAL |
DECLARE |
DEFAULT |
DEFINITION |
DELAY |
DELETE |
DESC |
DIGITS |
DISPOSE |
DISTINCT |
DO |
DROP |
ELSE |
ELSIF |
END |
ENTRY |
EXCEPTION |
EXCEPTION_INIT |
EXCLUSIVE |
EXISTS |
EXIT |
FALSE |
FETCH |
FILE |
FLOAT |
FOR |
FORM |
FROM |
FUNCTION |
GENERIC |
GOTO |
GRANT |
GROUP |
HAVING |
IDENTIFIED |
IF |
IMMEDIATE |
IN |
INCREMENT |
INDEX |
INDEXES |
INDICATOR |
INITIAL |
INSERT |
INTEGER |
INTERFACE |
INTERSECT |
INTO |
IS |
LEVEL |
LIKE |
LIMITED |
LOCK |
LONG |
LOOP |
MAX |
MAXEXTENTS |
MIN |
MINUS |
MLSLABEL |
MOD |
MODE |
MODIFY |
NATURAL |
NATURALN |
NETWORK |
NEW |
NEXTVAL |
NOAUDIT |
NOCOMPRESS |
NOT |
NOWAIT |
NULL |
NUMBER |
NUMBER_BASE |
OF |
OFFLINE |
ON |
ONLINE |
OPEN |
OPTION |
OR |
ORDER |
OTHERS |
OUT |
PACKAGE |
PARTITION |
PCTFREE |
PLS_INTEGER |
POSITIVE |
POSITIVEN |
PRAGMA |
PRIOR |
PRIVATE |
PRIVILEGES |
PROCEDURE |
PUBLIC |
RAISE |
RANGE |
RAW |
REAL |
RECORD |
REF |
RELEASE |
REMR |
RENAME |
RESOURCE |
RETURN |
REVERSE |
REVOKE |
ROLLBACK |
ROW |
ROWID |
ROWLABEL |
ROWNUM |
ROWS |
ROWTYPE |
RUN |
SAVEPOINT |
SCHEMA |
SELECT |
SEPERATE |
SESSION |
SET |
SHARE |
SIGNTYPE |
SIZE |
SMALLINT |
SPACE |
SQL |
SQLCODE |
SQLERRM |
START |
STATEMENT |
STDDEV |
SUBTYPE |
SUCCESSFUL |
SUM |
SYNONYM |
SYSDATE |
TABAUTH |
TABLE |
TABLES |
TASK |
TERMINATE |
THEN |
TO |
TRIGGER |
TRUE |
TYPE |
UID |
UNION |
UNIQUE |
UPDATE |
USE |
USER |
VALIDATE |
VALUES |
VARCHAR |
VARCHAR2 |
VARIANCE |
VIEW |
VIEWS |
WHEN |
WHENEVER |
WHERE |
WHILE |
WITH |
WORK |
WRITE |
XOR |
|||