Aron's Tech Blog

This is a query that returns the top X elements in the study class

<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
 <quer:or>
 <quer:lessThanOrEqual>
 <quer:field path="ProfileInformation,Studies,DisplaySequence"/>
 <quer:integer>1</quer:integer>
 </quer:lessThanOrEqual>
 <quer:isNull>
 <quer:query projectedClass="Study" alias="My_Study_SubQuery">
 <quer:projections>
 <quer:projection alias="My_Study_Alias">
 <quer:field path="DisplaySequence"/>
 </quer:projection>
 </quer:projections>
 <quer:filterings>
 <quer:filtering>
 <quer:equal>
 <quer:field path="Number"/>
 <quer:field ownerQuery="NewHire" path="ProfileInformation,Studies"/>
 </quer:equal>
 </quer:filtering>
 </quer:filterings>
 </quer:query>
 </quer:isNull>
 </quer:or>
</quer:filtering>

Had a field that either needed to be an employee number or 777777 to signify that it’s not a rehire.  I had to use a case statement based on criterion, so I set the default value to 777777 and if it’s isNotNull then it’s the number, I was stoked when it finally worked.

<quer:projection alias="EmployeeID" xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:switchByCriterion>
    <quer:cases>
      <quer:case>
        <quer:isNotNull>
          <quer:field path="Offers,Employee_20ID"/>
        </quer:isNotNull>
        <quer:field path="Offers,Employee_20ID"/>
      </quer:case>
    </quer:cases>
    <quer:defaultValue>
      <quer:string>999999</quer:string>
    </quer:defaultValue>
  </quer:switchByCriterion>
</quer:projection>

Here is an example of how to do the substring function in TCC:

</pre>
<quer:projection alias="JobCode">
 <quer:substring>
 <quer:field path="JobCode"/>
 <quer:integer>0</quer:integer>
 <quer:integer>20</quer:integer>
 </quer:substring>
 </quer:projection>
<pre>

Writing SQL is kind of hard if your filtering is limited to and under the where clause.  Here’s an example of how to filter using OR, in this case we had 14 items:)

<quer:filterings>
 <quer:filtering>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712187</quer:string>
 </quer:containsIgnoreCase>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712188</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712186</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712865</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712183</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712386</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712063</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712063</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712084</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712089</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712185</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712604</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712657</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712839</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 </quer:filtering>
</quer:filterings>

This is one that I’m not 100% familiar with the Oracle counterpart of the Decode function.  In this particular instance it’s used to parse out and assign the gender component of the EEO questions.  This is very hard to extract from the system and is only done so after a hire is made so that the information can be passed to the HRIS for hiring and payroll purposes.

<quer:projection id="idGender" alias="Gender">
 <quer:query projectedClass="Candidate" alias="CandGenderNo">
 <quer:projections>
 <quer:projection alias="EEOGender_AnswerNo">
 <quer:decode>
 <quer:field path="QuestionAnswers,Answer"/>
 <quer:string>-11</quer:string>
 <quer:string>M</quer:string>
 <quer:string>-12</quer:string>
 <quer:string>F</quer:string>
 <quer:string>Not Specified</quer:string>
 </quer:decode>
 </quer:projection>
 </quer:projections>
 <quer:filterings>
 <quer:filtering>
 <quer:equal>
 <quer:field path="QuestionAnswers,Question"/>
 <quer:integer>-3</quer:integer>
 </quer:equal>
 </quer:filtering>
 <quer:filtering>
 <quer:equal>
 <quer:field path="Number"/>
 <quer:field ownerQuery="MainQuery" path="Candidate"/>
 </quer:equal>
 </quer:filtering>
 </quer:filterings>
 </quer:query>
</quer:projection>

This is an example of how to use the Oracle TO_CHAR function in TCC:

<quer:projection alias="DateOfBirth">
 <quer:customFunction name="TO_CHAR">
 <quer:field path="Candidate,Birthday"/>
 <quer:string>MM/dd/yyyy</quer:string>
 </quer:customFunction>
</quer:projection>

This is an example of how to use the Oracle SUBSTR function in TCC:

<quer:projection alias="work_state">
 <quer:customFunction name="SUBSTR">
 <quer:field path="Requisition,JobInformation,WorkmansCompCode,Code"/>
 <quer:integer>1</quer:integer>
 <quer:integer>2</quer:integer>
 </quer:customFunction>
</quer:projection>