Adding POR1 to OITM, OITB

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member]

Hi Robin,

That is because you have many lines in POR1 with the same itemcode.

Thanks,

Gordon

[kvbalakumar]

Hi Robbin,

Gordon is correct, this was due to POR1 table only.

But i don't know, why POR1 table is inner joined in your query with no 'Select' from that table (POR1).

Regards,

Bala