Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 27 Apr 2004 @ 19:54:18 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Conditional max
 
From:   Shridhar Suvarna

Union query below works and I hope this helps you. Be aware that this Query might work with V2R5 only.


Good Luck,

--Shridhar


     ------------------------

     Select
      Parent
     ,Child
     ,Null Amt
     from Temp_Table
     Where (Parent, Child) in
     (Select Parent, Max(Child)
        From Temp_Table
        Group by 1
        having  Count(Amt)  = 0)

     Union

     Select
      Parent
     ,Child
     ,Amt
     From Temp_Table
     Where (Parent, Amt ) in
     (Select Parent, Max(Amt)
        From Temp_Table
        Where Amt is Not NULL
        Group by 1 )

     -------------------------

     Create volatile table Temp_Table
     (
        Parent       char(13) Not Null,
        Child        char(13) Not Null,
        Amt          Decimal(12, 2)
     )
     Unique primary Index (Parent, Child)
     On commit Preserve rows;

     -------------
     Insert into Temp_Table values ('X','A', NULL);
     Insert into Temp_Table values ('X','B', NULL );
     Insert into Temp_Table values ('X','C', NULL );
     Insert into Temp_Table values ('Y','F', 25.00);
     Insert into Temp_Table values ('Y','G', 40.00);
     Insert into Temp_Table values ('Y','H', NULL);
     ---------

 
 
 
 


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019