AW: Another order by question ?

["Dusold, Manfred" <M.Dusold@xxxxxxxxxxx>]

hi Heba,

 

use the "ifnull" - Function to avoid undefined value.

 

 

C/EXEC SQL                                                        
   C+   DECLARE @CURSOR1 CURSOR FOR                                  
   C+   SELECT  file1.flda , file1.fldb , file1.fldc, ifnull( file2.seqno, 0 ) dummy                    
   C+   FROM file1 LEFT JOIN file2                              
   C+   ON file1.fldb  = file2.fldb                        
   C+   AND file1.fldc = file2.fldc                        
   C+   ORDER BY dummy

   C/EXEC SQL          

Manfred

 

 

 

 -----Ursprüngliche Nachricht-----
Von: Refaie.Heba@khb.hu [mailto:Refaie.Heba@khb.hu]
Gesendet: Dienstag, 26. Juni 2001 11:59
An: RPG400-L@midrange.com
Betreff: Another order by question ?

> Hi Group, 
>
>       I have a SQLRPGLE program which runs this SQL 
>   statement 
>
>    C/EXEC SQL 
>                         
>                         
>               
>    C+   DECLARE @CURSOR1 CURSOR FOR       
>                         
>         
>    C+ 
>     SELECT  file1.flda , file1.fldb , file1.fldc, file2.seqno   
>                     
>   
>    C+   FROM file1 LEFT JOIN 
>   file2                     
>             
>    C+   ON file1.fldb  = file2.fldb     
>                       
>   
>    C+   AND file1.fldc 
>   = file2.fldc                   
>         
>    C+ 
>     ORDER BY file2.seqno 
>   
>    C/EXEC SQL           
>
>
> Obviously, I want to simulate the selection of the 
>   matched records with the primary file (like a query join option 2). I made 
>   this join just because I want to process ALL the records in file1 according to 
>   the seqno in file2 which is a (normal numeric field). those records in file A 
>   that don't have corresopndance in file B I want to process them at the end (in 
>   the normal sort sequence).   
>
> It works fine until it comes to a record from the primary file that 
>   does not have a correspondence on the right side, in this case the fetch 
>   returns -305 sqlcod (Indicator variable required). I think this is because the 
>   host variable to store the seqno which is also a normal numeric variable, can 
>   not handle the undefined value coming from the join. It is not important to me 
>   to include the file2.seqno in the select. I did so just to be able to use the 
>   order by clause. How can I overcome this? 
>
>
> Thanks in advance 
> Heba