Re: AW: Create View

Hi Birgitta.

Yes this was my first option but (I hope I'm wrong) with UDTF the table is 
created on the fly, I
mean you "call" it and the function runs. Right? If so I'm quite afraid about 
performance.

With UTDF the table name (splitf) is not library qualified so I just need to 
create the function
and use it with the appropriate library list as if I'm executing a normal 
program?

By the way, what's wrong with the View approach?

Thanks very much
Marco

--- "Hauser, Birgitta" <Birgitta.Hauser@xxxxxxxxxxx> wrote:

> Hi Marco,
> 
> have a look at user defined table functions.
> I'd suggest to use a User Defined Table Function.
> An UDTF can be used in each SELECT statement like a table or view.
> 
> The following example shows an UTDF, that splits a S/36 file in seperated
> fields. 
> 
> ZipCode was a packed format and the numeric Birthday is converted into a
> real date.
> CREATE FUNCTION HAUSER/SplitAdress                     
>       (  )                                             
>        RETURNS TABLE                                   
>               (FirstName CHARACTER( 15 ) ,             
>                Name      CHARACTER( 15 ) ,             
>                ZipCode   DECIMAL  (5, 0),              
>                City      CHARACTER( 15 ) ,             
>                BirthDay  DATE               )          
>        LANGUAGE SQL                                    
>        NOT DETERMINISTIC                               
>        MODIFIES SQL DATA                               
>        CALLED ON NULL INPUT                            
>        NO EXTERNAL ACTION                              
>        DISALLOW PARALLEL                               
>                                                        
>    BEGIN                                               
>        DECLARE Error DECIMAL(1, 0);                    
>        DECLARE Continue Handler FOR SQLEXCEPTION       
>                Set Error = 1;                          
>                                                                  
>       Return (SELECT SUBSTR ( SplitF ,  1 , 15 ) ,               
>                      SUBSTR ( SplitF , 16 , 15 ) ,               
>                      DECIMAL( SUBSTR (HEX (SplitF), 61,  5)),    
>                      SUBSTR ( SplitF , 34 , 15 ) ,               
>                      DATE   ( SUBSTR (HEX (SplitF), 98,  4)      
>                               concat '-' concat                  
>                               SUBSTR (HEX (SplitF), 102,  2)     
>                               concat '-' concat                  
>                               SUBSTR (HEX (SplitF), 104,  2))    
>                 From SplitF);                                    
>                                                                  
>   END  ;                                                         
> 
> You call the UDTF in an SELECT-statement as follows:
> Select * from Table(SplitAdress()) as MyTable
> Where Name = 'HAUSER' ...
> 
> I hope this helps
> 
> Birgitta
> 



                
____________________________________________________
Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs