Meta-SQL Shortcuts

Take advantage of the following shortcuts to use the entire list of key fields for a record.

Note: The meta-SQL shortcuts can only be used in PeopleCode programs, not in Application Engine SQL actions. Also, none of the meta-SQL shortcuts are implemented for COBOL.

Syntax

%Delete(:num)

Description

This is a shorthand for:

Delete from %Table(:num) where %KeyEqual(:num)

Syntax

%Insert(:num)

Description

This is a shorthand for:

Insert into %Table(:num) (%List(Nonnull_Fields :num)) values (%InsertValues(:num))

Syntax

%SelectAll(:num [ correlation _id])

Description

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

The pseudocode looks like this:

Select(AllFields, :num correlation_id) from %Table(:num) prefix

This shortcut is only appropriate if the statement is being used in PeopleCode or Application Engine to read data into memory. Dynamic views should retain the internal database formats for DateTime fields.

Using %SelectAll with CreateSQL

You can use %SelectAll with the CreateSQL function without a record object. It must subsequently be executed with the record object with which you want to do the Select statement. Here is an example:

    &REC_PROJ_FUNDING = CreateRecord(Record.PROJ_FUNDING); /* free standing record
 object */ 
    /* Create SQL objects */ 
    &SQL_PROJ_FUNDING_SEL = CreateSQL("%SelectAll(:1)" /* bind this later */); 
        /* bind the %SelectAll */ 
        &SQL_PROJ_FUNDING_SEL.Execute(&REC_PROJ_FUNDING); 
    While &SQL_PROJ_FUNDING_SEL.Fetch(&REC_PROJ_FUNDING);
		/* Process row content ... /*
		End-While;

You could also move the CreateRecord SQL statements out of the loop (and then move the close statements out of the loop too).

Syntax

%SelectDistinct(:num [ prefix])

Description

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

The pseudocode looks like this:

Select DISTINCT(AllFields, :num correlation_id) from %Table(:num) prefix

This shortcut is only appropriate if the statement is being used in PeopleCode or Application Engine to read data into memory. Dynamic views should retain the internal database formats for DateTime fields.

Syntax

%SelectByKey(:num [ correlation_id ])

Description

This is a shorthand for:

Select %List(Select_List, :num correlation_id) from %Table(:num) correlation_id where %KeyEqual(:num, correlation_id)

Syntax

%SelectByKeyEffDt(:num1, :num2)

Description

This is a shorthand for:

Select %List(Select_List, :num1) from %Table(:num1) A where %KeyEqualNoEffDt(:num1 A) and %EffDtCheck(:num1 B, A, :num2)

Syntax

%Update(:num [ , :num2 ])

Description

This is a shorthand for:

Update %Table(:num) set %UpdatePairs(:num) where %KeyEqual(:num2)

If num2 is omitted, the value defaults to num.