Creating a function to implement full outer joins in Sybase

Question

I have a long nested full outer join sequence I need to implement in Sybase Adaptive Server Enterprise. Is it possible to write a function for this?

I want to simplify the code below:

select t1.col_01, t1.col_02, TOT_Jan, TOT_Feb 
from  
  (select a.col_01, a.col_02, sum(b.col_XX) as TOT_Jan 
   FROM vw_aux a ...
    and year(b.tms_mydate) = 2012 
    and month(b.tms_mydate) = 1 
   ... ) t1 
left outer join 
  (select a.col_01, a.col_02, sum(b.col_XX) as TOT_Feb 
    ... and year(b.tms_mydate) = 2012 
    and month(b.tms_mydate) = 2 
   ... ) t2 on t1.col_aux = t2.col_aux 
UNION 
(select a.col_01, a.col_02, sum(b.col_XX) as TOT_Jan ...) t1 
right outer join (select a.col_01, a.col_02, sum(b.col_XX) as TOT_Feb ...) t2 
on ...

Creating a function like this:

funcion myfullouterjoin (vw_aux, table_01, col_aux, col_01, col_02, tms_mydate, TOT_Jan, TOT_Feb)

I am trying to avoid a kind of Spaghetti code here. The function would make the code more readable. — José Silva, Mar 19 '13 at 19:18
What exactly do you want to achieve? Can you Provide an example? — Ofir Winegarten, Mar 20 '13 at 07:51
Don't do it in the comments, edit your question and add this information there please. — Ofir Winegarten, Mar 20 '13 at 15:27

score 0 · Answer 1 · answered Mar 20 '13 at 19:55

0

Functions do not have output parameters, therefore, you cannot implement your query there.
However, it seems to me that a Stored Procedure or View perfectly fit your needs.

You can check Sybase documentation: Functions, Stored Procedures.

answered Mar 20 '13 at 19:55

Ofir Winegarten

9,215
2
21
27

Thanks OfirW. I'll try to write one. – José Silva Mar 20 '13 at 20:47

Creating a function to implement full outer joins in Sybase

1 Answers1