cancel
Showing results for 
Search instead for 
Did you mean: 

Adding POR1 to OITM, OITB

Former Member
0 Kudos
358

I'm on a big learning curve.........can anyone please explain why when I add the 3rd inner join it reports multiple lines against each Item No.?

I need to clearly understand how and where to make various joins.

SELECT t0.itemcode,t1.itemname, t1.frgnname, t1.u_prx_ptyp, cast(t0.onhand as int) as 'Stock', cast (t0.iscommited as int) as 'Sales Orders', (cast(t0.onhand as int) - cast(t0.iscommited as int)) as 'Free Stock', cast(t0.onorder as int) as 'Purchase Orders', (cast(t0.onhand as int) - cast(t0.iscommited as int) + cast(t0.onorder as int)) as 'Total'

FROM

oitw t0

inner join oitm t1 on t0.itemcode = t1.itemcode

inner join oitb t2 on t1.itmsgrpcod = t2.itmsgrpcod

inner join por1 t3 on t0.itemcode = t3.itemcode

WHERE

t0.whscode = 'ingh' and t1.u_prx_ptyp = '[%0]'

ORDER BY t1.u_prx_ptyp, t1.itemcode asc

Many thanks,

Robin

View Entire Topic
Former Member
0 Kudos

Hi Robin......

Try this......

SELECT t0.itemcode,t1.itemname, t1.frgnname, t1.u_prx_ptyp, cast(t0.onhand as int) as 'Stock', cast (t0.iscommited as int) as 'Sales Orders', (cast(t0.onhand as int) - cast(t0.iscommited as int)) as 'Free Stock', cast(t0.onorder as int) as 'Purchase Orders', (cast(t0.onhand as int) - cast(t0.iscommited as int) + cast(t0.onorder as int)) as 'Total'

FROM

oitw t0
inner join oitm t1 on t0.itemcode = t1.itemcode
inner join oitb t2 on t1.itmsgrpcod = t2.itmsgrpcod
inner join por1 t3 on t0.itemcode = t3.itemcode

WHERE

t0.whscode = 'ingh' and t1.u_prx_ptyp = '[%0]'
Group By t0.itemcode,t1.itemname, t1.frgnname, t1.u_prx_ptyp,t0.onorder, t0.onhand, t0.iscommited, t0.onorder, t0.whscode, t1.itemcode

ORDER BY t1.u_prx_ptyp, t1.itemcode asc

Regards,

Rahul