%SelectAll

If you ever need to create a view that selects all fields from a particular record, then you should be using the %SelectAll meta-sql construct.

Why? Because %SelectAll uses the underlying record definition to select the fields - so it will always return all fields from the underlying record, even if that record definition changes.

The basic syntax is:

%SelectAll(RECORD_NAME ALIAS)

There is all a %SelectDistinct construct which adds a distinct to the select clause and uses the same syntax.

%SelectAll returns all the fields for the record specified and includes a from clause.

For example, say I want the latest effective dated, active fields from PSXLATITEM. My SQL might start something like this:

select
    FIELDNAME, 
    FIELDVALUE, 
    EFFDT, 
    EFF_STATUS, 
    XLATLONGNAME, 
    XLATSHORTNAME, 
    LASTUPDDTTM, 
    LASTUPDOPRID, 
    SYNCID  
from 
    PSXLATITEM A  
where 
    EFFDT = (  
        select max(EFFDT)
        from PSXLATITEM
        where FIELDNAME = A.FIELDNAME  
        and FIELDVALUE = A.FIELDVALUE  
        and A.EFFDT <= sysdate
    )
    and A.EFF_STATUS = 'A'
;

Instead of typing out all those fields, lets use %SelectAll (that's what I did to generate this example by the way).

So our meta-sql would look like this - (I've also replaced sysdate with %CurrentDateIn)

%SelectAll(PSXLATITEM A)
where 
    EFFDT = (  
        select max(EFFDT)
        from PSXLATITEM
        where FIELDNAME = A.FIELDNAME  
        and FIELDVALUE = A.FIELDVALUE  
        and A.EFFDT <= %CurrentDateIn
    )
    and A.EFF_STATUS = 'A'

This resolves into the following Meta-SQL:

SELECT A.FIELDNAME 
 , A.FIELDVALUE 
 , TO_CHAR(A.EFFDT 
 ,'YYYY-MM-DD') 
 , A.EFF_STATUS 
 , A.XLATLONGNAME 
 , A.XLATSHORTNAME 
 , TO_CHAR(A.LASTUPDDTTM 
 ,'YYYY-MM-DD-HH24.MI.SS."000000"') 
 , A.LASTUPDOPRID 
 , A.SYNCID  
  FROM PSXLATITEM A   
 WHERE EFFDT = (  
 SELECT MAX(EFFDT)  
  FROM PSXLATITEM  
 WHERE FIELDNAME = A.FIELDNAME  
   AND FIELDVALUE = A.FIELDVALUE  
   AND A.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') )  
   AND A.EFF_STATUS = 'A'