on 03-27-2018 8:50 AM
hey!
i wrote a quarry that shows inventory amount for items
only for items where QryGroup17 = 'Y' i want to show the return from the quarry below for the onhand field/column
SELECT T0.[OnHand] FROM OITW T0 inner join ITT1 T1 on t0.itemcode = T1.code WHERE T0.[WhsCode] ='01'
how do i do that ?
thank you 🙂
Hi,
Why do you need sub query?
Try this,
SELECT T0.DocDate , T0.DocNum , T0.CardCode , T0.CardName , T1.ItemCode , T1.Dscription , T1.OpenQty , T4.[OnHand]
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN ITM1 T3 ON T2.[ItemCode] = T3.[ItemCode]
INNER JOIN OITW T4 ON T2.[ItemCode] = T4.[ItemCode]
WHERE T0.DocStatus = N'O'
AND (T0.DocDate >= CONVERT(DATETIME, '[%0]', 112)
AND T0.DocDate <= CONVERT(DATETIME, '[%1]', 112) )
AND T4.[WhsCode] ='01'
AND T1.OpenQty > 0
and ( T2.[QryGroup2] ='Y' or T2.[QryGroup17] = 'Y' or T2.[QryGroup64] = 'Y' OR T2.[QryGroup32] = 'Y')
Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
i tried this - it doesn't show the right data
it shows me only a small amount of items when i have a lot more
within the items i want to show i have a small group QryGroup32 that i want to have the onhand column show the result of (SELECT T0.[OnHand] FROM OITW T0 inner join ITT1 T1 on T0.itemcode=T1.code WHERE T0.[WhsCode]='01')
Hi,
Try this,
SELECT T0.[OnHand] FROM OITW T0 inner join ITT1 T1 on t0.itemcode = T1.code WHERE T0.[WhsCode] ='01' and T0.[QryGroup17] = 'Y'
Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thank you for trying to help 🙂
this is the quarry im trying to add this to:
SELECT T0.DocDate , T0.DocNum , T0.CardCode , T0.CardName , T1.ItemCode , T1.Dscription , T1.OpenQty , T4.[OnHand]
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN ITM1 T3 ON T2.[ItemCode] = T3.[ItemCode]
INNER JOIN OITW T4 ON T2.[ItemCode] = T4.[ItemCode]
WHERE T0.DocStatus = N'O'
AND (T0.DocDate >= CONVERT(DATETIME, '[%0]', 112)
AND T0.DocDate <= CONVERT(DATETIME, '[%1]', 112) )
AND T4.[WhsCode] ='01'
AND T1.OpenQty > 0
and ( T2.[QryGroup2] ='Y' or T2.[QryGroup17] = 'Y' or T2.[QryGroup64] = 'Y')
and
--if
T2.[QryGroup32] = 'Y'
--then
(SELECT T0.[OnHand] FROM OITW T0 inner join ITT1 T1 on T0.itemcode=T1.code WHERE T0.[WhsCode]='01' )
GROUP BY T0.[DocDate], T0.[DocNum], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[OpenQty], T4.[OnHand]
i know this is incorrect,
im getting the Error :
1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'GROUP'.
2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'User defined values' (CSHS) (s) could not be prepared.
how should i correct it ?
thanks again !!
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.