Createsql in Peoplecode

How to use Createsql in Peoplecode? You can use createsql for dynamic SQL or static sql of each purpose. There are many ways you can create SQL text.

Syntax of Create SQL

CreateSQL([{sqlstring | SQL.SqlName}[, paramlist]])

Where paramlist is an arbitrary-length list of values in the form:

inval1 [, inval2] ..

Use the CreateSQL function to instantiate a SQL object from the SQL class and opens it on the given sqlstring and input values. sqlstring is a PeopleCode string value giving the SQL statement.

Any errors in the SQL processing cause the PeopleCode program to be terminated with an error message.

You can use CreateSQL with no parameters to create an empty SQL object that can be used to assign properties before being populated and executed.

Example 1: You want to select all employee name and blood of deptid and mgrid.

You can create dynamic sql to text in peopolecode such as

&RPT = “select emplid,name,blood form table where deptid = :1 and MGRID=:2”

Local SQL &sql2 = CreateSQL(&RPT, TABLE.DEPTID, TABLE.MGRID);

While (&sql2.Fetch(&emplid,&name,&blood))
&file.WriteLine(&emplid | “‘”|&name | “‘”| &blood);
End-While;

Example 2: Create Sql Text in Message Catalog

You can use PeopleCode function all sql text in a message and pass the value in the message catalog.

&SQL_RPT = MsgGetExplainText(20003, 107, “Message not found.”, TABLE.DEPTID, TABLE.MGRID);

&sql1 = CreateSQL(&SQL_RPT);

How to Convert Date Format using Peoplecode in Peoplesoft

How to Convert Date Format using Peoplecode in Peoplesoft by function  is DateTimeToLocalizedString of peoplesoft

&StartDT = DateTimeToLocalizedString(DERIVED_TT.KTB_START_DT, “yyyyMMdd”);

&DT= DateTimeToLocalizedString(%date, “dd-MM-yyyy”);

For example on how to apply the above function.

%date in as 2018-09-07 out as 20180907:
&date = DateTimeToLocalizedString(%Date, “yyyyMMdd”);
%Datetime in as 2018-09-07.15.41.000000 out as 20180907-154109:
&datetime = DateTimeToLocalizedString(%Datetime, “yyyyMMdd-HHmmss”);

Date and Time in Meta-SQL

For input or output of the current date or time:

• %CurrentDateIn

The %CurrentDateIn meta-SQL variable expands to a platform-specific SQL substring representing the current date in the Where clause of a SQL Select or Update statement, or when the current date is passed in an Insert statement.

• %CurrentDateOut

The %CurrentDateOut meta-SQL variable expands to platform-specific SQL for the current date in the Select clause of a SQL query.

• %CurrentDateTimeIn

The %CurrentDateTimeIn meta-SQL variable expands to a platform-specific SQL substring representing the current datetime in the Where clause of a SQL Select or Update statement, or when the current date time is passed in an Insert statement.

• %CurrentDateTimeOut

The %CurrentDateTimeOut meta-SQL variable expands to platform-specific SQL for the current datetime in the Select clause of a SQL query.

• %CurrentTimeIn

The %CurrentTimeIn meta-SQL variable expands to a platform-specific SQL substring representing the current time in the Where clause of a SQL Select or Update statement, or when the current time is passed in an Insert statement.

• %CurrentTimeOut

The %CurrentTimeOut meta-SQL variable expands to platform-specific SQL for the current time in the Select clause of a SQL query.

For input or output of any date or time:

• %DateIn

The %DateIn meta-SQL variable expands into platform-specific SQL syntax for the date. Use %DateIn whenever a date literal or Date bind variable is used in a comparison in the Where clause of a Select or Update statement, or when a Date value is passed in an Insert statement.

• %DateOut

The %DateOut meta-SQL variable expands to either a platform-specific SQL substring or datetime value, depending on the database platform, representing a datetime column in the Select clause of a SQL query

• %DateTimeIn

The %DateTimeIn meta-SQL variable expands to platform-specific SQL for a DateTime value in the Where clause of a SQL Select or Update statement, or when a DateTime value is passed in an Insert statement.

• %DateTimeOut

Restrictions Using COBOL

You can only use string literals when using this construct in COBOL. You cannot use it with bind parameters in COBOL. For example, the following works in COBOL:

The following SQL fails:

• %TimeIn

%TimeIn expands to platform-specific SQL for a Time value in the Where clause of a SQL Select or Update statement, or when a time value is passed in an Insert statement.

Restrictions Using COBOL

You can only use string literals when using this construct in COBOL. You cannot use it with bind parameters in COBOL. For example, the following works in COBOL:

The following SQL fails:

• %TimeOut

When  input date/time goes in the WHERE clause (conditions) and output date/time goes in the SELECT clause (returned data).

For input/output of a null date/time:

• %DateNull
• %DateTimeNull
• %TimeNull

For getting part of a date/time:

• %DatePart gets the date
• %TimePart gets the time

For date arithmetic:

• %DateAdd(date_from, days_to_add) gives the resulting date. You can use negative numbers to get a past date. So this is also the DateSubtract function.

• %DateDiff(date_from, date_to) gives the difference between two dates in days

The following usage is illegal (always use %Datein for inputting date literals):

• %DateTimeDiff(date_from, date_to) gives the difference between two date/times in minutes

The following example returns the difference in hours between the current datetime and the requested datetime:

The following example returns the difference in minutes:

References:

http://peoplesoft.wikidot.com/date-and-time-in-meta-sqlhttps://docs.oracle.com/cd/E57990_01/pt853pbh2/eng/pt/tape/langref_UsingApplicationEngineMeta-SQL-0771d9.html#topofpage

How to use LongTranslateValue and ShortTranslateValue

How to use LongTranslateValue and ShortTranslateValue for get description of translate value of field.

ShortTranslateValue Systax :
&VALUE = &MYFIELD.ShortTranslateValue;

LongTranslateValue Systax :
&VALUE = &MYFIELD.LongTranslateValue;