%SELECTALL

最新推荐文章于 2022-12-23 14:49:58 发布

转载最新推荐文章于 2022-12-23 14:49:58 发布 · 35 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/bryanchen/p/3385946.html

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 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 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
   '
  

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!

转载于:https://www.cnblogs.com/bryanchen/p/3385946.html