cancel
Showing results for 
Search instead for 
Did you mean: 

how to write sub quarry for some cases

0 Kudos

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 🙂

Accepted Solutions (0)

Answers (2)

Answers (2)

kothandaraman_nagarajan
Active Contributor
0 Kudos

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

0 Kudos

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')

kothandaraman_nagarajan
Active Contributor
0 Kudos

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

0 Kudos

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 !!