locked
query RRS feed

  • 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

    • Moved by Bob Beauchemin Tuesday, May 24, 2011 5:23 AM Moved to the relevent forum (From:.NET Framework inside SQL Server)
    Tuesday, May 24, 2011 5:14 AM

Answers

  • 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
    • Proposed as answer by Mr. Wharty Tuesday, May 24, 2011 6:00 AM
    • Marked as answer by Kalman Toth Sunday, May 29, 2011 6:11 AM
    Tuesday, May 24, 2011 5:37 AM

All replies

  • 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
    Tuesday, May 24, 2011 5:28 AM
  • 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
    • Proposed as answer by Mr. Wharty Tuesday, May 24, 2011 6:00 AM
    • Marked as answer by Kalman Toth Sunday, May 29, 2011 6:11 AM
    Tuesday, May 24, 2011 5:37 AM
  • Hi,

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

     


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Tuesday, May 24, 2011 5:43 AM
  • 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
    Tuesday, May 24, 2011 7:58 AM

  • 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
    

    Tuesday, May 24, 2011 9:41 AM
    Answerer
  • Hi All friends

     

    Thank you every think will

    Best regards

     


    almunji
    Tuesday, May 31, 2011 8:54 AM