I'm using DB2 on an AS400, not sure the version of DB2, but the AS400 is V5R2.

I currently have a subselect as a column in my query that returns a count. Here's the subselect:

Code:
  select count(subitemsis.wxshp#) from i93file/whitemsis subitemsis     
  left join i93file/whitem subitem on subitemsis.wxshp# = subitem.wmshp#
  where subitemsis.wxobok = itemsis.wxobok                              
  and subitem.wmord# != ''
I want to somehow declare the result of this subselect as a variable and just use that variable name in my main query as needed, either for display or for arithmetic operations. I'm no expert in SQL, so not sure what I should do.

My whole query looks like this so far, but it's still a work in progress:

Code:
select                                                                        
distinct(itemsis.wxobok) as Booking, item.wmsord as Shipper_Ref,              
itemsis.wxocldt as Closing,                                                   
                                                                              
/* Count all warehouse shipments with matching booking */                     
(                                                                             
  select count(subitemsis.wxshp#) from i93file/whitemsis subitemsis           
  left join i93file/whitem subitem on subitemsis.wxshp# = subitem.wmshp#      
  where subitemsis.wxobok = itemsis.wxobok                                    
  and subitem.wmord# != ''                                                    
) as Orders,                                                                  
                                                                              
/* Calculate number of missing containers to complete booking */              
(                                                                             
                                                 
) as Missing_Cntrs                                                            

from i93file/whitemsis itemsis                                                
left join i93file/whitem item on itemsis.wxshp# = item.wmshp#                 
where itemsis.wxobok != '' and itemsis.wxocldt > '07/07/2008'                 
order by Closing asc
I'm working on a second subselect "Missing_Cntrs" that needs to calculate the number of missing containers based on how many are currently on file compared to the total amount needed. So I would need to subtract the count of what's on file from the total needed. I haven't written the SQL to retrieve what's on file, but just need to know how to use the first subselect as a variable first.

Any help is appreciated, thanks.