Skip to Content

how to write sub quarry for some cases

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 27 at 08:03 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

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

  • Mar 27 at 10:01 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

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