Just the other day, I needed to set a Date to null or blank, and I couldn’t remember how to do it. Here’s my notes so that I can remember next time. Javier’s blog came to the rescue:
Javier’s PeopleSoft blog: Setting Date Variables to Null in PeopleCode
The short version is: use the Date(0) function:
Local Date &hireDate;
&hireDate = Date(0);
Here’s some more information to explore a little more in depth…
I have been tinkering around with a little Java application I started for running queries against a PeopleSoft database. I was just curious if there was any interest in such an application. Here is a screenshot of what I have so far:
So far the program connects to SQL Server databases and Apache Databases. It runs queries and loads the data into the JTable at the bottom.
I have a SQL “preprocessor” that evaluates the SQL right before it passes it to the database. Currently, the only thing it does is to pull the semi-colon off of the end. But, I plan to have it resolve meta-sql such as %Table() and %CurrentDateIn, etc.
Also, I would like to build SQL tools in that understand the SQL syntax. I would like tools that automatically generate effective date criteria. Another feature would be nice to have where it would join a table into a SQL statement.
Once I learned about SQL Developer, I switched from using Toad as my Query Tool. If I were to improve one thing though, it would be that the query tool understand Meta-SQL. Now, if I copy SQL from PeopleSoft, say from an Application Engine, I have to remove all of the Meta-SQL tags that PeopleTools understands before I can run it. Then, I have to replace them before copying back to PeopleTools.
I have thought about trying to write an extension for SQL Developer. Does anyone know of any documentation about writing extensions or plug-ins for SQL Developer? This might be a place to start. Another route I have thought about is an open source tool called Squirrel SQL hosted on Source Forge. It looks like it has a way to write plug-ins or extensions, but I haven’t been able to get it to connect to any of my databases yet. To be fair, I haven’t given it a thorough run-through yet.
If you have any ideas or information, please let me know.
If you were to learn just a select few Meta-SQL, these are the ones that I would recommend:
%SelectAll
%SelectAllByKey & %SelectAllByKeyEffdt
%Table
%DateIn & %DateOut
%Insert
%Update
%Delete
There are many others. Please refer to the PeopleTools PeopleBook for more information.
Update 5/7 — Fixed the links to point to the most resent version of PeopleTools
generates an update statement to change the database to match the values in the record object.
Example
Local Record &recJob;
&recJob.EMPLID.value = &emplid;
&recJob.EMPL_RCD.value = &empl_rcd;
SQLExec(“%SelectByKeyEffdt(:1)”, &recJob, %Date, &recJob);
&recJob.DEPTID.value = &deptid;
SQLExec(“%Update(:1)”, &recJob);
generates a delete statement using the values from a record object
This command will pull the record name, the key fields, and the values for those key fields to generate the delete statement. This will result in the statement deleting one row from the database since the statement includes all of the keys for the record.
Example
Local Record &recJob;
&recJob.EMPLID.value = &emplid;
&recJob.EMPL_RCD.value = &empl_rcd;
&recJob.EFFDT.value = &effdt;
&recJob.EFFSEQ.value = 0;
SqlExec(“%Delete(:1)”, &recJob);
generates an Insert statement to insert the data from a record object into the database.
Remember that a record object (not a record definition) stores both the record structure (table name and field name) and the field values. Therefore, the system has everything it needs to write an insert statement: insert( … field list … ) values ( … options …).
Examples
Local Record &myRecord;
&myRecord.KEY1.value = “Key Value”;
&myRecord.FIELD1.value = “Field Value”;
SQLExec(“%Insert(:1)”, &myRecord);
generates a select statement based on the given record definition.
Remember that a record object (not a record definition) stores both the record structure (table name and field name) and the field values. Therefore, the system has everything it needs to write a select statement: select … list of fields … from … table name … where … key field name … = … value for that field … and so on.
Related: %SelectByKeyEffdt
does the same thing except it adds the effective dating criteria to limit it to the most recent rows.
Examples
Local Record &recJob;
&recJob = CreateRecord(RECORD.JOB);
&recJob.EMPLID.value = &emplid;
&recJob.EMPL_RCD.value = &empl_rcd;
&recJob.EFFDT.value = &effdt;
&recJob.EFFSEQ.value = &effseq;
SqlExec(“%SelectByKey(:1)”, &recJob, &recJob);
WinMessage(“Job Code: ” | &recJob.JOBCODE.value);
Local Record &recNames, &recOutput;
&recNames = CreateRecord(RECORD.NAMES);
&recOutput = CreateRecord(RECORD.NAMES);
&recNames.EMPLID.value = &emplid;
&recNames.NAME_TYPE.value = “PRI”;
SqlExec(“%SelectByKeyEffdt(:1)”, &recNames, %Date, &recOutput);
Generates a select statement that selects all the fields from a chosen record.
Typically, when you use the %SelectAll, you would use a record object to store the results. That way you don’t have to know how many fields your result will return.
Examples
%SelectAll(JOB)
Local Record &myRecord;
&myRecord = CreateRecord(RECORD.MY_RECORD);
SQLExec(“%SelectAll(:1) WHERE MY_KEY = :2”, &myRecord, “My Key”, &myRecord);
Local Record &myInstallation;
&myRecord = CreateRecord(RECORD.INSTALLATION);
SQLExec(“%SelectAll(:1)”, &RECORD.INSTALLATION, &myInstallation);
%Table
Replaces with the actual table name of the given record.
Generally, this just means adding a “PS_” to the front of the record name. But, PeopleTools actually checks the alternate table name from the Record Type tab to see if a value is there first.
Two Uses:
Examples:
SELECT * FROM %Table(JOB) WHERE EMPLID = :1
SqlExec(“SELECT NAME FROM %Table(:1) WHERE EMPLID = :2”, Record.NAMES, &emplid, &name);
&sql = CreateSql(“SELECT * FROM %Table(:1) WHERE EMPLID = :2”, &MyRecord, &emplid);
INSERT INTO %Table(MYTEMP_TAO)
SELECT * FROM PS_MYDATATABLE