Top 10 Awesome Meta-SQL

Here is a list of my top 10 awesome meta-sql that makes my lazy life easier.

%EffdtCheck
%InsertSelect
%InsertValues
%Join
%KeyEqual
%SQL
%SelectAll
%SelectDistinct
%SelectByKey
%SelectByKeyEffDt

Now for some examples...

%EffdtCheck

%EffDtCheck will create an effective date subquery suitable for the Where clause. The date value is automatically wrapped in %DateIn. Note that EFFSEQ and EFF_STATUS are not handled by this meta-SQL.

The following code:

SQLExec("SELECT MYFIELD FROM PS_MYRECORD A WHERE %EffDtCheck(:1, A, :2)", &Rec, &Date);

Will resolve to:

select MYFIELD 
from PS_MYRECORD A 
where EFFDT = (select MAX(EFFDT) 
       from PS_MYRECORD
       where PS_MYRECORD.MYFIELD = A.MYFIELD
       and PS_MYRECORD.EFFDT <= %DateIn('2014-01-16'))

%InsertSelect

%InsertSelect will create an Insert statement with a Select statement.

The basic code:

%InsertSelect(MYSTAGINGRECORD, MYRECORD1 T1)
from PS_MYRECORD1 T1

Will resolve to:

insert into PS_MYSTAGINGRECORD (MYFIELD1, MYFIELD2, ...)
select T1.MYFIELD1, T1.MYFIELD2, ...
from PS_MYRECORD1 T1

Using JOIN, the following code:

%InsertSelect(MYSTAGINGRECORD, MYRECORD1 T1, MYRECORD2 T2)
from PS_MYRECORD1 T1, PS_MYRECORD2 T2
where %Join(COMMON_KEYS, MYRECORD1 T1, MYRECORD2 T2) ...

Will resolve to:

insert into PS_MYSTAGINGRECORD (MYFIELD1, MYFIELD2, ...)
select T2.MYFIELD1, T2.MYFIELD2, ...
from PS_MYRECORD1 T1, PS_MYRECORD2 T2
where T1.MYFIELD1 = T2.MYFIELD1
and T1.MYFIELD2 = T2.MYFIELD2 ...

Using a constant for one of the fields, the following code:

%InsertSelect(MYSTAGINGRECORD, MYRECORD1 T1, MYRECORD2 T2, MYFIELD3 = 'DOG')
from PS_MYRECORD1 T1, PS_MYRECORD2 T2
where %Join(COMMON_KEYS, MYRECORD1 T1, MYRECORD2 T2) ...

Will resolve to:

insert into PS_MYSTAGINGRECORD (MYFIELD1, MYFIELD2, MYFIELD3, ...)
select T2.MYFIELD1, T2.MYFIELD2, 'DOG', ...
from PS_MYRECORD1 T1, PS_MYRECORD2 T2
where T1.MYFIELD1 = T2.MYFIELD1
and T1.MYFIELD2 = T2.MYFIELD2 ...

Using DISTINCT, the following code:

%InsertSelect(DISTINCT, MYSTAGINGRECORD, MYRECORD1 T1, MYRECORD2 T2)
from PS_MYRECORD1 T1, PS_MYRECORD2 T2
where %Join(COMMON_KEYS, MYRECORD1 T1, MYRECORD2 T2) ...

Will resolve to:

insert into PS_MYSTAGINGRECORD (MYFIELD1, MYFIELD2 ...)
select DISTINCT T2.MYFIELD1, T2.MYFIELD2, ...
from PS_MYRECORD1 T1, PS_MYRECORD2 T2
where T1.MYFIELD1 = T2.MYFIELD1
and T1.MYFIELD2 = T2.MYFIELD2 ...

%InsertValues

%InsertValues will create a comma-separated list of the record's non-null field values. Values for Date, Time, or DateTime fields will automatically be wrapped in %Datein, %TimeIn, or %DateTimeIn, respectively. Values String fields will automatically be wrapped in quotation marks.

The following code:

SQLExec("Insert into MYRECORD1 (%List(NonNull_Fields, :1)) values (%InsertValues(:1))", &Rec);

Will resolve to:

"Insert into MYRECORD1 (MYFIELD1, MYFIELD2, MYFIELD3) values (99, 'Y', %DateIn('1901-12-30'))"

%Join

%Join will build a Where clause joining one table to another.

The following code:

%Join(COMMON_KEYS, JOB J, PER_ORG_ASGN P)

Will resolve to:

J.EMPLID = P.EMPLID
AND J.EMPL_RCD = P.EMPL_RCD
AND J.PER_ORG = P.PER_ORG
AND J.POI_TYPE = P.POI_TYPE

To override a field:

%Join(COMMON_KEYS, JOB J, PER_ORG_ASGN P, POI_TYPE)

This will resolve to:

J.EMPLID = P.EMPLID
AND J.EMPL_RCD = P.EMPL_RCD
AND J.PER_ORG = P.PER_ORG

%KeyEqual

%KeyEqual will create conditional phrase suitable for a Where clause. Basically, it accepts a record and tries to match the record fields with the target record's keyfields.

Suppose that the record &REC has three keys: EMPLID, EMPL_RCD, EFFDT, EFFSEQ.

Local record &REC;
&REC = CreateRecord(RECORD.MYRECORD);
&REC.EMPLID.Value = 000001;
&REC.EMPL_RCD.Value = 0;
&REC.EFFDT.Value = %Date;
&REC.EFFSEQ.Value = 0;

SQLExec("DELETE FROM MYRECORD A where %KeyEqual(:1, A)", &REC);

This would translate to:

"Delete from MYRECORD A
where A.EMPLID = 000001
AND A.EMPL_RCD = 0
AND A.EFFDT = %Date('2015-01-16')
AND A.EFFSEQ = 0"

%SQL

%SQL will use the text from an existing SQL object so you can reuse it in your current SQL. It also accepts values for bind parameters.

For example, MYSQL is an SQL object and inside I placed the following:

%P(1).EFFDT = (SELECT MAX(EFFDT) FROM ...)

And using this code:

UPDATE PS_JOB
SET DEPTID = 'DEPTXYZ'
WHERE EMPLID = 000001
AND %SQL(MY_SQL, PS_JOB)

This would translate to:

UPDATE PS_JOB
SET DEPTID = 'DEPTXYZ'
WHERE EMPLID = 000001
AND PS_JOB.EFFDT = (SELECT MAX(EFFDT) FROM ...)

%SelectAll

%SelectAll is shorthand for selecting all fields in the specified record, wrapping DateTime fields with %DateOut, %TimeOut, and so on.

The following code:

&SQLSelect = CreateSQL("%SelectAll(:1)", &MYRECVAR);

Will translate to:

SELECT MYFIELD1, MYFIELD2, ...
FROM PS_MYRECORD1

Most common usage of %SelectAll:

&MYRECVAR = CreateRecord(Record.MYRECORD1);
&SQLSelect = CreateSQL("%SelectAll(:1)", &MYRECVAR);
While &SQLSelect.Fetch(&MYRECVAR)
 &MYRECVAR.MYFIELD1.Value = 999;
 &MYRECVAR.MYFIELD2.Value = 'Test';
 ...
End-While;

%SelectDistinct

%SelectDistinct is similar to %SelectAll wherein is selects all the fields from the specified record, except a DISTINCT rule has been applied to it.

The following code:

&SQLSelect = CreateSQL("%SelectDistinct(:1)", &MYRECVAR);

Will translate to:

SELECT DISTINCT MYFIELD1, MYFIELD2, ...
FROM PS_MYRECORD1

%SelectByKey

%SelectByKey will create a where clause based on the key fields of the specified record.

Local Record &REC1, &REC2;
&REC1 = CreateRecord(RECORD.JOB);
&REC2 = CreateRecord(RECORD.JOB);

&REC1.EMPLID.value = (employee id value)
&REC1.EMPL_RCD.value = (employee record value);
&REC1.EFFDT.value = (effective date value);
&REC1.EFFSEQ.value = (effective sequence value);
SqlExec("%SelectByKey(:1)", &REC1, &REC2);
WinMessage("Job Code: " | &REC2.JOBCODE.value);

%SelectByKeyEffDt

%SelectByKeyEffDt is similar to %SelectByKey except it adds the effective date criteria to limit the data based on the given date value.

Local Record &REC1, &REC2;
&REC1 = CreateRecord(RECORD.NAMES);
&REC2 = CreateRecord(RECORD.NAMES);

&REC1.EMPLID.value = (employee id value)
&REC1.NAME_TYPE.value = (name type value);
SqlExec("%SelectByKeyEffdt(:1)", &REC1, %Date, &REC2);
WinMessage("First Name: " | &REC2.FIRST_NAME.value);