AW: Create View

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

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von phil Kestenbaum
Gesendet: Montag, 1. August 2005 20:49
An: Midrange Systems Technical Discussion
Betreff: RE: Create View

It seems to me you have to parse it out one way or another. You can do so in
a staging area on the ISeries, or At the moment you are looking for an
instance of a keyword like select or insert and then using substring to pull
the command out of the row.. (maybe more than one row or maybe more than 1
command per row.)


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Marco Facchinetti
Sent: Monday, August 01, 2005 11:50 AM
To: Midrange Systems Technical Discussion
Subject: RE: Create View


1. the file is on Iseries and is continously updated.

2. when I says "hundreds Sql" I mean each doing something like this:

select * from x,y where x.field1 = y.field1 and y.field2 in('01' '03') order
by....

The point is that the "flat" file contains a lot of data very usefull to
select records and I
don't want to substring all the times with hardocde positions.

Thanks in advance
Marco

--- phil Kestenbaum <pkestenbaum@xxxxxxxxx> wrote:

> Mario,
> 1. Are you importing the "flat file" into SQL and then querying it? or Are
you setting it up as
> a linked server?
> 
> 2. When you say "file with (hundreds) of sql." do you mean you have
hundreds of "rows" of data,
> or is it sql statements (querys) 
> 
> Phil
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Marco Facchinetti
> Sent: Monday, August 01, 2005 9:11 AM
> To: Midrange Systems Technical Discussion
> Subject: Create View
> 
> 
> I need to read a "flat" file with (hundreds) of sql.
> 
> The file is very simple:
> 
> A status field
> A record type field
> A key field
> A data field
> 
> the data field is the problem since all RPG programs read it via external
DS but of course Sql
> cannot.
> 
> I'd like to define a view for each type of DS so I can use them freely via
Sql without having to
> hardcode positional substring in the statements.
> 
> Basically I know how to do it:
> 
> CREATE VIEW MUTABCAB0F (TACDCABINA, TADSCABINA, TADATARILA)
> AS SELECT                                                 
> SUBSTR(TBCDELEME, 1, 5) ,                                  
> SUBSTR(TBDATITAB, 1, 50) ,                                 
> DATE(SUBSTR(TBDATITAB, 51, 10))                            
> FROM MUTABEL00F                                           
> WHERE TBSETTORE = 'CAB'
> 
> The view is working but it's really poor since the fields generated are
not referenced to the
> original DS.
> 
> How can I mimic the LIKE statement of the CREATE TABLE on CREATE VIEW?
> 
> Thanks in advance
> Marco
> 



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