SQLTeam.com | Weblogs | Forums

I need to do a join which will keep all rows for a crystal rpt


#1

I have one table which I want to join with another. I would create a
commmand in Crystal reports to get this output and change the names in
the formulas easily. I need something like this: only i need the sum
from the second tbl.

Select * from rapklib.shpfrt a
left outer join ralib.shpfrtc b
where idord# a = idord# b

the first is created as

CREATE VIEW Ralib.shpfrt AS SELECT T01.IDORD#, T01.IDDOCD, T01.IDPRT#,
T01.IDSFX#, T01.IDSHP#, T01.IDNTU$, T01.IDENT# ,
( T01.IDNTU$ * T01.
IDSHP# ) AS LINTOT
, T02.IARCC3, T02.IAPRLC ,
T01.IDORDT, T01.IDHCD3
FROM STDTA.OEINDLID
T01 INNER JOIN STDTA.ICPRTMIA T02 ON T01.IDPRT# = T02.IAPRT#

WHERE t01.iddocd > 20131231

the second is the same as the first but only will contain rows when
there is a value in idprt# = 'FRTCRDM' then the sum we get there means
there was a credit given for shipping charge (freight).

So I want to join them for use in a crystal report that will give me
all the rows of the first but bring in the FRTCRDM amounts as well.
Currently I am joining them when order# are = and thus only getting
orders which have a FRTCRDM.


#2

I think you want a UNION, not a JOIN