Friday, January 6, 2017

Tips for SQL Queries

Some Tips while Using SQL Queries:
===========================================
When Comparing Date with SQL Date in SQL Query from Pega, we need to follow either of the approach:
1)Convert the pega Date format which u want to compare to string in SQL Date format. ex (20010101) to '01-Jan-2001'
@FormatDateTime(Param.OriginalApprovalDateTime, "dd-MMM-yyyy", "", "en_US")

example query:
In activity need to set the formattted value to ExpirationDateTxt property and then in SQL
select * From table_name where pyExpiration < { .ExpirationDateTxt}

2)Else in the SQL Query u can add the Date when passing pega property.
pyExpiration < {.pyExpirationDate Date}

same u can use for DateTime also.
pyExpiration< {.pyExpiration DateTime}

sample SQL query:

DELETE FROM {CLASS:Data-Admin-Security-OAuth-Server-Token} WHERE  PXOBJCLASS = 'Data-Admin-Security-OAuth-Server-Token-Request' AND pyExpiration< {.pyExpiration DateTime}