Archives: November 27, 2013

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>