%SelectAll

     

%SelectAll函数相当于select * from tablename。可以返回表中所有的字段。

select * from PSXLATITEM  的两种写法：

1，%SelectAll(PSXLATITEM A)

2，名为getAll的SQL中写%SelectAll(:1)  A，可以用函数调用方式

SQL getAll = CreateSQL(SQL.getAll,Record.PSXLATITEM)。

http://peoplesoft.wikidot.com/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.

%SelectDistinct 函数相当于在%SelectAll 上加一个distinct语句，筛除重复数据。

%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 btw ;)

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'

Note that %SelectAll wraps date, time and date/time fields with %DateOut, %TimeOut, %DateTimeOut as well. This resolvesinto the following meta-sql:

可以使用%DateOut, %TimeOut, %DateTimeOut 函数表示与时间有关的数据。例如上下文中红字表示的。

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'

Note that the example includes an alias of A in the parameters to %SelectAll. If you are using %SelectAll you might have to play with your SQL a bit to get it work, especially if you are using joins to other tables. It may not work in all cases, but if nothing else, its a time saver!

注意from中表别名的使用，并非所有情况下都会生效，尤其是在使用连接语句时，如join等，但它的使用会有效节省时间。