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.
%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:
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
%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'