SQL - SQLExec returns null instead of a blank field

Sam found that the SQLExec function has some unexpected behaviour when used to retrieve a blank field. If the field it returns contains a blank space in the database PeopleSoft trims it and returns it as an empty string.

This means you can’t tell whether the query returned a row but the field was blank or the query didn’t return any rows at all.

You have to either check for the existence of a required field or use the %SQLRows variable to work it out.

Peoplecode comparisons

When comparing the current component/page/record with a particular component/page/record name, use COMPONENT.<> or PAGE. in the comparison. This way, if the object is renamed PeopleSoft will automatically update the name for us. It also allows the ‘Find Definition References’ to work better.

Recommended

If %Component = COMPONENT.UQ_COMPONENT_NAME then…

SQL - Nested query referencing.

Values in nested queries should be linked to the parent SQL statement via aliases/qualifiers. Do not pass/code the values directly into the nested query if the parent also uses the values.

Recommended

SELECT A.NAME_DISPLAY
FROM PS_NAMES A
WHERE A.EMPLID = ‘12345678’
AND A.NAME_TYPE = ‘PRI’
AND A.EFFDT = (
SELECT MAX(B.EFFDT)
FROM PS_NAMES B
WHERE B.EMPLID = A.EMPLID
AND B.NAME_TYPE = A.NAME_TYPE
AND B.EFFDT <= SYSDATE
)
AND A.EFF_STATUS = ‘A’;

Recording exactly what is dont to Vanilla objects.

If modifying a vanilla object, it is handy to record (perhaps in the properties section if straightforward, or the SIR overview otherwise) exactly what was done to the object.

This makes it easier to handle maintenance pack upgrade etc, when we are trying to reimplement the mod.

SQL - Example of Sorting Tables that have config table in Golden

PeopleSoft delivered the config table for Address Type, it can be found via Home > Set Up HRMS > Foundation Tables > Personal > Address Type.

The table name is PS_ADDRESS_TYP_TBL. This table is currently setup as HOME, MAIL, BUSN and PERM etc…

The SQL statement as follow:

SELECT *
FROM PS_ADDRESSES A
WHERE A.EMPLID = '40752994'
AND A.ADDRESS_TYPE =
(SELECT B.ADDRESS_TYPE
FROM PS_ADDRESS_TYP_TBL B
WHERE B.ORDER_BY_SEQ =
(SELECT MIN(B_SQ.ORDER_BY_SEQ)
FROM PS_ADDRESS_TYP_TBL B_SQ, PS_ADDRESSES C
WHERE C.EMPLID = A.EMPLID
AND C.ADDRESS_TYPE = B_SQ.ADDRESS_TYPE
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT)
FROM PS_ADDRESSES C_ED
WHERE C_ED.EMPLID = C.EMPLID
AND C_ED.ADDRESS_TYPE = C.ADDRESS_TYPE
AND C_ED.EFFDT <= SYSDATE))) AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_ADDRESSES A_ED WHERE A_ED.EMPLID = A.EMPLID AND A_ED.ADDRESS_TYPE = A.ADDRESS_TYPE AND A_ED.EFFDT <= SYSDATE) ;
If students have Home address it will return Home Address, if not Mailing address and so on.

Quotes for strings in SQL

If the field is character, we need to ensure the variable is enclosed by quotes, this will allow Oracle to utilise the indexed key for lookup.

Example - &Condition = “where BUSINESS_UNIT = ‘UQUNI’ and EMPLID = ‘“ &Emplid “’”; i.e. putting single quotes around the variable &Emplid

A better solution is to use the bind variables as shown below:&rsCharge.Fill(“where BUSINESS_UNIT = :1 and EMPLID = :2”, “UQUNI”, &Emplid);For Oracle, EMPLID = 12345678 is less efficient than EMPLID = ‘12345678’

FieldChange Vs FieldEdit

Use FieldChange PeopleCode to recalculate page field values, change the appearance of page controls, or perform other processing that results from a field change other than data validation. To validate the contents of the field, use the FieldEdit event.

The reason this topic comes up is because we have found that FieldChange does behave different to FieldEdit.

Recently, wehave seen some design that put the validation into FieldChange, it is incorrect. PeopleSoft has made it clear that FieldEdit is for data validation and FieldChange is for anything else other than data validation. Alternatively you can put it into SaveEdit (not SavePreChange or SavePostChange), please see the PeopleSoft Component Process Flow.

For example, if you have Field A, B and C and you put your data validation against Field A in FieldChange event, Here is what happened:

1) You deliberately typed an invalid data into Field A.
2) PeopleSoft popped up an error message
3) You clicked ok
4) You ignore the data error in Field A (even though it is highlighted in Red)
5) You continue type in data in Field B and C
6) I believe you can even Save it! Or in the case of Add mode, you can even Add it.
However, if you put your data validation in FieldEdit, then every time you try to save it, it will always popped up the error message regarding data error in Field A.