Oggy997
ClioSport Club Member
997.1, Caddy, e208
I am writing a query for data retreival from an AS400 using ODBC, currently writing the query in SQL Commander because the UI is nicer than STRSQL in DB2 / Microsoft Query.
If I do;
I get two records;
Yet when I run my full query;
I only get the one result ?!? The result being the first record found.
Why am I not getting the result from the second select in the union?
If I do;
Code:
select pal#pm as palletID, clsspm as pallet_classification, clsqpm as qty_at_class from warpall where pal#pm = '1005462503'
union all
select pal#pq, clsspq, clsqpq from warpalq where pal#pq = '1005462503'
Code:
1005462503 31 2
1005462503 71 25
Yet when I run my full query;
Code:
select
clntwf, commpm, fdsccd, clsqwf, pal#wf, dtcdpm, clsswf, srb#pm, descsc, cor#or
from (select substr(warpall.clntpm,1,2) as clntwf, pal#pm as pal#wf, commpm, substr(warpall.clsspm,1,2) as clsswf, warpall.clsqpm * 1 as clsqwf, srb#pm, dtcdpm, srd#pm from warpall
where locnpm <> 'ASSEMBLED PALLET'
and commpm <> 'ASSEMBLED PALLET'
and clsqpm <> 0
and clntPM <> 'D2'
and pal#pm = '1005462503'
union all
select substr(clntpq,1,2) as clntwf, pal#Pq as pal#wf, '0', substr(clsspq,1,2) as clsswf, clsqpq * 1 as clsqwf, '0', '0', '0' from warpalq
where clsqpq <> 0
and clntPQ <> 'D2'
and pal#pq = '1005462503'
) as t9
inner join
warcmdt as t3
on
t3.clntcd = t9.clntwf and t3.commcd = t9.commpm
left join
warclss as t4
on
t4.clsssc = t9.clsswf
left join
warohrh as t5
on
t5.clntor = t9.clntwf and t5.srn#or = t9.srd#pm
order by pal#wf asc
I only get the one result ?!? The result being the first record found.
Why am I not getting the result from the second select in the union?