query

Question

Salam

 Dear friends

 

 I have on query can you help me

What the rung in it

SELECT        Branch_Code, Dates, Descr, InQty, ItemCode, ItemDesc, OutQty, Rate_Purch, Rate_Sale,

                             (SELECT        ItemCode AS zzz, SUM(COALESCE (InQty, 0)) - SUM(COALESCE (OutQty, 0)) AS OpnStock

                                FROM            StockMovingRpt_viw AS y

                                GROUP BY ItemCode, Dates

                                HAVING        (Dates < @d1) AND (ItemCode = x.ItemCode)) AS Expr1

FROM            StockMovingRpt_viw AS x

WHERE        (Dates BETWEEN @d1 AND @D2)

ORDER BY ItemDesc

Answer 1

Hi.

Note, having clause is used to filter groups not the rows i would suggest you to use where clause instead

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 2

Try to properly indent & format your queries, and use "Insert Code Block" for posting SQL queries:

SELECT Branch_Code,
    Dates,
    Descr,
    InQty,
    ItemCode,
    ItemDesc,
    OutQty,
    Rate_Purch,
    Rate_Sale,
    (SELECT SUM(COALESCE (InQty, 0)) - SUM(COALESCE (OutQty, 0)) AS OpnStock
    FROM  StockMovingRpt_viw AS y
    GROUP BY ItemCode,
         Dates
    HAVING ( Dates < @d1 )
        AND ( ItemCode = x.ItemCode )) AS Expr1
FROM  StockMovingRpt_viw AS x
WHERE ( Dates BETWEEN @d1 AND @D2 )
ORDER BY ItemDesc

You are trying to pull 2 columns in the Sub-query at SELECT level, which is not allowed.

I think you need OpnStock column in your select, just remove the zzz column from select in sub-query.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 3

Hi,

Yes I didn't saw that unnecessary column in the subquery.

 

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 4

SELECT Branch_Code,Dates,Descr,InQty,ItemCode,ItemDesc,
OutQty,Rate_Purch,Rate_Sale,
(
	SELECT 
	--remove below column
	--subquery return one column
	--ItemCode AS zzz, 
	SUM(COALESCE (InQty, 0)) - SUM(COALESCE (OutQty, 0)) AS OpnStock
	FROM StockMovingRpt_viw AS y
	where (Dates < @d1)
	and (ItemCode = x.ItemCode)
	GROUP BY ItemCode, Dates
) AS Expr1
FROM StockMovingRpt_viw AS x
WHERE (Dates BETWEEN @d1 AND @D2)
ORDER BY ItemDesc

---

Yogesh Bhadauriya - Sr Software Engineer in Microsoft Technologies

Answer 5

HI Almanji!

You may also use the below query to get the desired result.

SELECT  branch_code, 
     dates, 
     descr, 
     inqty, 
     itemcode, 
     itemdesc, 
     outqty, 
     rate_purch, 
     rate_sale, 
     (SELECT  Sum(Coalesce(inqty,0)) - Sum(Coalesce(outqty,0)) AS opnstock 
     FROM   stockmovingrpt_viw AS y 
     GROUP BY itemcode, 
          dates 
     HAVING  (dates < @d1) 
          AND (itemcode = x.itemcode)) AS expr1 
FROM   stockmovingrpt_viw AS x 
WHERE  (dates BETWEEN @d1 AND @D2) 
ORDER BY itemdesc 

Please let me know if this doesn’t work for you. Hope I have answered you correctly

Thanks, Hasham

Answer 6

Hi All friends

 

Thank you every think will

Best regards

 

---

almunji