Sub select question
Last Post 21 Nov 2012 03:48 PM by Craig R. Lockhart. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
harlechguy
New Member
New Member
Posts:2

--
21 Nov 2012 12:24 PM

Hi - this is my first post - so be gentle!

I have two tables - one which holds Order No, Order Line, M/O No and two which holds details relating to the M/O

One Sales Order / Order line links to two M/Os (a network order - upper level, lower level)

I am seeking to retrieve information from both M/Os relating to the same Sales order / line (the consumed material from M/O 1 and the produced quantity from M/O 2

I have written the following SQL

select aybmnb, aywdnb, aya4nb, (select sum(rebaqt) from sms452bfel.z2oorext where rea4nb = aya4nb and reacti = 'OUT' and rea9dt = 20121121) as consumed, (select sum(rebqqt) from sms452bfel.z2oorext where rea4nb = aya4nb and rebatc<>'' and reaqnb = 25) as packed
from sms452bfel.mfmohr
where aybmnb = 36424
group by aybmnb, aywdnb, aya4nb

and this produces the information below - how can I consolidate this to show on one line (without the M/O)?

Sales Order | Line | M/O| Consumed|Produced

 

36424| 10| 5016917| 10.0000|
36424| 10| 5016918| |230.000

Many thanks

 

 

SoftwareTrend
Basic Member
Basic Member
Posts:107

--
21 Nov 2012 02:22 PM
You probably should just drop the column aya4nb from the main select and the group by. You could also use and IFNULL() test around the two embedded selections to set the column value to 0 if no records exist.
harlechguy
New Member
New Member
Posts:2

--
21 Nov 2012 02:27 PM
Thanks, can drop aya4nb from select list - no problem but query errors if removed from group
Craig R. Lockhart
Veteran Member
Veteran Member
Posts:1167
Avatar

--
21 Nov 2012 03:48 PM

SoftwareTrend is mostly correct.  The problem, though, is that your summing would return more than one result per row.  Normally, I would suggest a join, but since your selection criteria is different for the two summed fields, I will suggest a CTE.  (Two CTEs with a join in the main statement is better, but just so as not to throw you for a loop ...) Try:

with myFile as (
  select aybmnb, aywdnb, aya4nb,
         coalesce( (select sum(rebaqt) from sms452bfel.z2oorext
                    where rea4nb = aya4nb
                      and reacti = 'OUT'
                      and rea9dt = 20121121), 0) as consumed,
         coalesce( (select sum(rebqqt) from sms452bfel.z2oorext
                    where rea4nb = aya4nb
                      and rebatc<>''
                      and reaqnb = 25), 0) as produced
  from sms452bfel.mfmohr
  where aybmnb = 36424
  group by aybmnb, aywdnb, aya4nb
)
select aybmnb, aywdnb, sum(consumed) as consumed,
       sum(produced) as produced
from myFile
group by aybmnb, aywdnb
order by aybmnb, aywdnb;

 HTH
You are not authorized to post a reply.

Acceptable Use Policy