Update using case in Application engine SQL

Please find the sample code to update a table using case :

UPDATE TABLE1 MAIN
  SET MAIN.CHAR_TO = (CASE WHEN MAIN.REG_TEMP = 'R'
   AND MAIN.POSTAL2 IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE1' WHEN MAIN.REG_TEMP = 'R'
   AND MAIN.POSTAL2 NOT IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE2' WHEN MAIN.REG_TEMP = 'S'
   AND MAIN.POSTAL2 IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE3' WHEN MAIN.REG_TEMP = 'S'
   AND MAIN.POSTAL2 NOT IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE4' WHEN MAIN.REG_TEMP = 'W'
   AND MAIN.POSTAL2 IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE5' WHEN MAIN.REG_TEMP = 'W'
   AND MAIN.POSTAL2 NOT IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE6' ELSE ' ' END )
 WHERE MAIN.PROCESS_INSTANCE = %ProcessInstance

Where value1 to value6 varies based on the case statement.Here the CHAR_TO field is assigned different value based on the case statements.

Concatenation of strings in application Engine using SQL

Please find the sample code to Concatenate using Application Engine SQL

UPDATE Table1 MAIN
  SET MAIN.PASSWD = (
 SELECT 'TXT'|| SUBSTR(REPLACE(NID.NATIONAL_ID %Comma '-' %Comma'') %Comma -4 ) || EXTRACT(YEAR
  FROM SYSDATE)
  FROM Table2  NID
 WHERE NID.EMPLID = MAIN.EMPLID
   AND NID.NATIONAL_ID_TYPE = 'PR')
 WHERE EXISTS(
 SELECT 'X'
  FROM Table2  NID1
 WHERE NID1.EMPLID = MAIN.EMPLID
   AND NID1.NATIONAL_ID_TYPE = 'PR')
   AND MAIN.PROCESS_INSTANCE = %ProcessInstance

IF NID.NATIONAL_ID=1234567
AND YEAR=2015

Then Output will be -->TXT12345672015

Application Engine Trace-Peoplecode & SQL

Steps to  trace an application engine process using the Process Definitions

1. Navigate to PeopleTools > Process Scheduler > Processes
2. Enter your Application Engine name
3. Navigate to override options tab.
4. Enter the parameter list as listed below:-Trace 7 -TOOLSTRACEPC 4044

This will trace SQLs inside of your application engine as well as peoplecode. Save the process definition and go ahead and run your program. To examine the trace files, just navigate to the process monitor, click details on your process, then View Log/Trace.