Advanced META SQL

Table of Contents

Embedded conditional SQL fragment IF
Escaped control characters
Collections of input values
Dynamic/Static SQL operators

All conditional SQL fragments can be embedded. The basic rule is that there must be at least one space between the consecutive left curly braces, which delimit the beginning of the related conditional SQL fragments. In the following paragraph is one example for it.

Embedded conditional SQL fragment IF

Logical expressions can be embedded. It means the alternative SQL fragments can be itself META SQL statements.

Let's have a query

QUERY(QRY)=
  select l.ID, l.NAME, pm.LOCATION
  {? :media |
    {? :fname | , m.TITLE, e.ROLE, p.NAME_FIRST first, p.NAME_LAST last
              | , m.TITLE, e.ROLE, '' first, '' last
    }
            |  , '' title, '' role, '' first, '' last
  }
  from LIBRARY l left join PHYSICALMEDIA pm on pm.LIBRARY = l.ID
  {? :media |
    {? :fname | left join MEDIA_PHYSICALMEDIA mpm on mpm.PHYSICALMEDIA = pm.ID
                left join MEDIA m on mpm.MEDIA = m.ID
                left join ENGAGEMENT e on e.MEDIA = m.ID
                left join PERSON p on e.PERSON = p.ID |
                left join MEDIA_PHYSICALMEDIA mpm on mpm.PHYSICALMEDIA = pm.ID
                left join MEDIA m on mpm.MEDIA = m.ID
                left join ENGAGEMENT e on e.MEDIA = m.ID
    }
  }
  {= where
   {& UPPER(m.TITLE) = :+media}
   {? :media | {& UPPER(p.NAME_FIRST) = :+fname}}
  }
  order by l.ID, pm.LOCATION
;

• in the case both the input values :media and :fname are true, the very complicated join of several tables (LIBRARY, PHYSICALMEDIA, MEDIA_PHYSICALMEDIA, MEDIA, ENGAGEMENT, PERSON) is used to obtain personal information (p.NAME_FIRST first, p.NAME_LAST last)
• in the case where only the input value :media is true, the less complicated join of several tables (LIBRARY, PHYSICALMEDIA, MEDIA_PHYSICALMEDIA, MEDIA, ENGAGEMENT) is used to obtain personal roles and media titles (m.TITLE, e.ROLE)
• in the case where both the input values :media and :fname are false, only the basic join of several tables (LIBRARY, PHYSICALMEDIA) is used to obtain basic information (l.ID, l.NAME, pm.LOCATION)

Escaped control characters

To prevent special control characters treatment, they can be escaped. In this case they have their literal value. The next characters can be escaped:

• COLON :
• SEMICOLON ;
• STRING $
• LBRACE {
• RBRACE }
• BOR |
• HASH #
• AT @
• PERCENT %
• DOT .
• SLASH /

Collections of input values

Dynamic or static input values can also be an instances of any collection.

Let's have a query

QUERY(QRY)=
    select p.ID, p.NAME_FIRST first, p.NAME_LAST last
  from PERSON p
  {= where
   {& p.ID in $idSet}
   {& p.NAME_LAST in :nameSet}
  }
  {#1 order by id}
;

In this example :nameSet and $idSet are collections. The next fragments p.ID in (...) and p. NAME_LAST in (? ? ...) will become a part of the final SQL query. Between round brackets there will be a comma separated list of all values in the collection for static input values. Or there will be a list of question marks for all values in the collection for dynamic input values. This is true only in the case these collections are not null and not empty.

This behaviour can be changed using the modifiers any and anyset. For their description please see the tutorial Input values.

Dynamic/Static SQL operators

The WHERE part of the META SQL query can be written in the next fashion

{= where
  {& %c.ID ::= :id }
  {& %c.PERSON_ID ::= :person.id }
  {& %c.TYPE $$= :type }
  {& UPPER(%c.ADDRESS) like :+address }
  {& %c.PHONE_NUMBER ::= :phoneNumber }
}

The SQLP generates the final ANSI SQL query based on dynamic or static input values. The operators

• ::= - dynamic SQL operator
• $$= - static SQL operator

are evaluated to value = by default. It can be changed in the case the POJO for dynamic/static input values has special attributes.

Every SQL operator is evaluated in the context of the related dynamic/static input attribute. So in the SQL fragment

  {& %c.ID ::= :id }

the dynamic SQL operator is related to the dynamic input attribute :id. In the case there's a dynamic input attribute idOp, which is not null, it's value is used for this operator. In the SQL fragment

  {& %c.TYPE $$= :type }

the static SQL operator is related to the dynamic input attribute :type. In the case there's a static input attribute typeOp, which is not null, it's value is used for this operator. The advanced SQL operator notation can be

  {& %c.ID ::Op1 :id }

The Op1 can be in fact any identifier. In this case the value of the input attribute idOp1 is taken.

The tutorial is updated for SQLP 2.2