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.
dBforums
>
declaring select statement result as variable?
07-07-08, 10:13
declaring select statement result as variable?
Linear Mode
