Query with sub functions

[Former Member]

Hi Experts,

I have tried the below query with sub select function. could any one help me to fix this ?

SELECT T0.[ItemCode], T0.[Dscription], T1.[OnHand],

(Select distinct sum(T0.InQty) from OINM where T0.ItemCode = OINM.ItemCode and T0.DocDate = '2012/08/18' ) as 'OB Stock'

FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.[OnHand] > 0   GROUP BY T0.[ItemCode], T0.[Dscription],  T1.[OnHand]

thanks in advance,

Regards,

Dwarak

[KennedyT21 · Accepted solution]

Try This

SELECT T0.[ItemCode], T0.[Dscription], T1.[OnHand],

(Select distinct sum(T0.InQty) from OINM c where T0.ItemCode = c.ItemCode and c.DocDate = '2012/08/18' ) as 'OB Stock'

FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.[OnHand] > 0   GROUP BY T0.[ItemCode], T0.[Dscription],  T1.[OnHand]

[Former Member]

Hi,

This is for your Query.

SELECT T0.[ItemCode], T0.[Dscription], T1.[OnHand],

(Select distinct sum(T0.InQty) from OINM t0 where T0.ItemCode = T0.ItemCode and T0.DocDate = '2012/08/18'  ) as 'OB Stock'

FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.[OnHand] > 0   GROUP BY T0.[ItemCode], T0.[Dscription],  T1.[OnHand]

Regards,

Kalaiyarasan.v

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT T0.[ItemCode], T0.[Dscription], T1.[OnHand],

(Select distinct sum(T0.InQty) from OINM t0 where T0.ItemCode = T0.ItemCode and T0.DocDate = '2012/08/18'  ) as 'OB Stock'

FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.[OnHand] > 0   GROUP BY T0.[ItemCode], T0.[Dscription],  T1.[OnHand]

Thanks & Regards,

Nagarajan