Constructing a user-defined-function that returns a table (or other alternatives), to make query more readable

Question

I currently have a section in my query that looks similar to this

, T3 AS (
          select 'FSA'            as tType, b.fsacd  as tBefore, c.fsacd  as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID 
    union select 'Scale'          as tType, b.scd    as tBefore, c.scd    as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID 
    union select 'Retail Source'  as tType, b.rsc    as tBefore, c.rsc    as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID 
    union select 'Mix Match'      as tType, b.mmcd   as tBefore, c.mmcd   as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID 
    union select 'Price Entry'    as tType, b.pecd   as tBefore, c.pecd   as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID 
    union select 'Qntty Entry'    as tType, b.qecd   as tBefore, c.qecd   as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID    
    union select 'Price 3 Decs'   as tType, b.p3d    as tBefore, c.p3d    as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID    
    union select 'Tare Entry'     as tType, b.tecd   as tBefore, c.tecd   as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID    
    union select 'Undiscountable' as tType, b.undsc  as tBefore, c.undsc  as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID    
    union select 'Foodstamp'      as tType, b.fds    as tBefore, c.fds    as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID    
    union select 'WIC'            as tType, b.wic    as tBefore, c.wic    as tAfter from T1 as a , T2 as b,T2 as c where a.beforeID = b.tID and a.afterID =c.tID    
)

it works fine, but I would like to make it look more compact. Is there a way to construct a function foo such that I coul accomplish the same results by doing something that may look like this

, T3 AS (
          foo('FSA'             ,fsacd  ,T1, T2 ) 
    union foo('Scale'           ,scd    ,T1, T2 ) 
    union foo('Retail Source'   ,rsc    ,T1, T2 ) 
    union foo('Mix Match'       ,mmcd   ,T1, T2 ) 
    union foo('Price Entry'     ,pecd   ,T1, T2 ) 
    union foo('Qntty Entry'     ,qecd   ,T1, T2 )   
    union foo('Price 3 Decs'    ,p3d    ,T1, T2 )   
    union foo('Tare Entry'      ,tecd   ,T1, T2 )   
    union foo('Undiscountable'  ,undsc  ,T1, T2 )   
    union foo('Foodstamp'       ,fds    ,T1, T2 )   
    union foo('WIC'             ,wic    ,T1, T2 )   
)

I use Sybase. But if I see how it's done in a another DBMS, I might be able to figure out how it's done in Sybase. — Daniel Vaca, Jul 17 '12 at 17:07

Gordon Linoff · Answer 1 · 2012-07-17T19:56:50.597

0

Here is another option, but not sure if it is making the problem worse. This is increasing the number of aliases in the with clause. Do note, however, that when using UNION or UNION ALL, you only need to specify the new column name in the first subquery.

tbefore as (select *
            from T1 a join
                 T2 b
                 on a.beforeID = b.tID
             ),
 tafter as (select *
            from T1 a join
                 T2 c
                 on a.afterID = b.tID
             ),
 t3b as (select 'FSA' as tType, fsacd as tBefore from tbefore union all
         select 'Scale'as tType, scd from tbefore union all
         ...
        ),
 t3a as (select 'FSA' as tType, fsacd as tAfter from tafter union all
         select 'Scale'as tType, scd from tafter union all
         ...
        ),
 t3 as (select t3b.ttype, t3b.tbefore, t3a.tafter
        from t3b join t3a on t3b.ttype = t3a.ttype
       )

edited Jul 17 '12 at 19:56

answered Jul 17 '12 at 17:50

Gordon Linoff

1,242,037
58
646
786

This won't work on sybase since b,c aren't recognized by t3. What DBMS does this work for? – Daniel Vaca Jul 17 '12 at 18:46
You are right . . . I was trying to be helpful, but you would have to define separate aliases for the fields in t1t2. Does Sybase have unpivot? That is the best approach for solving this. – Gordon Linoff Jul 17 '12 at 18:55
It doesn't look like there is, but thanks for mentioning it. It might lead me to some alternatives. – Daniel Vaca Jul 17 '12 at 19:46

Constructing a user-defined-function that returns a table (or other alternatives), to make query more readable

1 Answers1