locked
Error in queries RRS feed

  • Question

  •  string SubQuerTransfer = "ISNULL ((SELECT Tbl_ItemTransferInformation.ITI_ITEM_TRANSFER_QUANTITY FROM Tbl_Stock INNER JOIN " +
                    " Tbl_ItemTransferInformation ON Tbl_Stock.STK_ITM_ITEM_ID = Tbl_ItemTransferInformation.ITI_ITM_ITEM_ID AND " +
                    " Tbl_Stock.STK_SERIAL_ID = Tbl_ItemTransferInformation.ITI_STK_OUT_SERIAL_ID INNER JOIN Tbl_Stock AS Tbl_Stock_1 " +
                    " ON Tbl_Stock.STK_ITM_ITEM_ID = Tbl_Stock_1.STK_ITM_ITEM_ID AND Tbl_Stock.STK_STK_SERIAL_ID = Tbl_Stock_1.STK_SERIAL_ID " +
                    " WHERE (Tbl_Stock_1.STK_SERIAL_ID = A.STK_SERIAL_ID) AND (Tbl_Stock_1.STK_ITM_ITEM_ID = A.STK_ITM_ITEM_ID)), 0) ";

     string SubQuerSales = "ISNULL ((SELECT SUM(Tbl_SalesFIFODetails.SFF_OUT_TOTAL_OR_PARTIAL_QTY) FROM Tbl_Stock INNER JOIN " +
                    " Tbl_SalesFIFODetails ON Tbl_Stock.STK_PIN_INVOICE_SERIAL = Tbl_SalesFIFODetails.SFF_PURCHASE_INVOICE_ID AND " +
                    " Tbl_Stock.STK_ITM_ITEM_ID = Tbl_SalesFIFODetails.SFF_STK_ITEM_ID INNER JOIN Tbl_Stock AS Tbl_Stock_1 ON " +
                    " Tbl_SalesFIFODetails.SFF_STOCK_OUT_REF_ID = Tbl_Stock_1.STK_STOCK_OUT_REF_ID AND Tbl_SalesFIFODetails.SFF_STK_ITEM_ID " +
                    " = Tbl_Stock_1.STK_ITM_ITEM_ID AND Tbl_Stock.STK_STA_STATION_ID = Tbl_Stock_1.STK_STA_STATION_ID WHERE " +
                    " (Tbl_Stock.STK_STOCK_IN > 0) AND (Tbl_Stock.STK_QTY_AVAILABLE_STATUS IN (0, 1)) AND (Tbl_Stock.STK_STA_STATION_ID = " +
                    ViewState["SelectedStationId"].ToString() + ") AND (Tbl_Stock.STK_SERIAL_ID = A.STK_SERIAL_ID) AND " +
                    " (Tbl_Stock.STK_ITM_ITEM_ID = A.STK_ITM_ITEM_ID) AND (Tbl_SalesFIFODetails.SFF_STK_SERIAL_ID = " +
                    " Tbl_Stock.STK_SERIAL_ID)), 0) ";

     string SubQuerPurReturn = "ISNULL((SELECT Tbl_StockReturnManager.SRM_RETURN_QUANTITY FROM Tbl_Stock INNER JOIN " +
                        "Tbl_StockReturnManager ON Tbl_Stock.STK_SRM_SERIAL_ID = Tbl_StockReturnManager.SRM_SERIAL_ID AND " +
                        "Tbl_Stock.STK_ITM_ITEM_ID = Tbl_StockReturnManager.SRM_ITM_ITEM_ID AND Tbl_Stock.STK_PIN_INVOICE_SERIAL = " +
                        "Tbl_StockReturnManager.SRM_PSL_INVOICE_NO AND Tbl_Stock.STK_STA_STATION_ID = " +
                        "Tbl_StockReturnManager.SRM_STA_STATION_ID INNER JOIN Tbl_Stock AS Tbl_Stock_1 ON Tbl_Stock.STK_STK_SERIAL_ID = " +
                        "Tbl_Stock_1.STK_SERIAL_ID AND Tbl_Stock.STK_ITM_ITEM_ID = Tbl_Stock_1.STK_ITM_ITEM_ID WHERE (" +
                        "Tbl_Stock.STK_QTY_AVAILABLE_STATUS IN (0, 1)) AND (Tbl_StockReturnManager.SRM_STATUS = 5) AND (" +
                        "Tbl_Stock.STK_ITM_ITEM_ID = A.STK_ITM_ITEM_ID) AND (Tbl_Stock_1.STK_SERIAL_ID = A.STK_SERIAL_ID)), 0) ";
    Tuesday, May 29, 2012 9:28 AM

Answers

  • Hi, 

    In addtion to Mike's, 

    1> You don't need to concat when your string spans to next line. You can use @ for this (@ says don't process escape sequences) 

    2> Always follow a readable format for writing sql query (saves time when you think any changes to apply)

    • Select statement in 1st line
    • Inner join next line with On 
    • Where clause on next line 

    3> Remove unnecessary "(" and ")"

    I changed your first query , 

    string SubQuerTransfer = @"ISNULL (
      SELECT Tbl_ItemTransferInformation.ITI_ITEM_TRANSFER_QUANTITY FROM Tbl_Stock 
       INNER JOIN Tbl_ItemTransferInformation ON Tbl_Stock.STK_ITM_ITEM_ID = Tbl_ItemTransferInformation.ITI_ITM_ITEM_ID  
      					     AND Tbl_Stock.STK_SERIAL_ID = Tbl_ItemTransferInformation.ITI_STK_OUT_SERIAL_ID 
       INNER JOIN Tbl_Stock AS Tbl_Stock_1 ON Tbl_Stock.STK_ITM_ITEM_ID = Tbl_Stock_1.STK_ITM_ITEM_ID 
     					  AND Tbl_Stock.STK_STK_SERIAL_ID = Tbl_Stock_1.STK_SERIAL_ID 
       WHERE Tbl_Stock_1.STK_SERIAL_ID = A.STK_SERIAL_ID AND Tbl_Stock_1.STK_ITM_ITEM_ID = A.STK_ITM_ITEM_ID, 0) ";
     

    And, If you have a big query always , i feel , it's better you execute at sql server and copy here, saves lots of time to debug looking for error in query formation.

    I hope this helps you....


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".


    • Edited by Kris444 Wednesday, May 30, 2012 8:33 AM
    • Proposed as answer by Mike Feng Thursday, May 31, 2012 3:25 AM
    • Marked as answer by Mike Feng Wednesday, June 6, 2012 12:01 PM
    Wednesday, May 30, 2012 8:30 AM

All replies

  • Hi,

     Can you provide what error exactly your getting..??


    Sai Kumar K (MCP)
    Blog: Sai's Stuff.
    WebSite: SantoshTechnologies.

    Tuesday, May 29, 2012 12:52 PM
  • Hi Anum,

    Welcome to the MSDN Forum.

    This statement is a little long. I suggest you try to splice this string a small part by a small one. Once you join one part, print it and check whether it is right or not. This will be more easy than find an error in such long string.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 30, 2012 8:03 AM
  • Hi, 

    In addtion to Mike's, 

    1> You don't need to concat when your string spans to next line. You can use @ for this (@ says don't process escape sequences) 

    2> Always follow a readable format for writing sql query (saves time when you think any changes to apply)

    • Select statement in 1st line
    • Inner join next line with On 
    • Where clause on next line 

    3> Remove unnecessary "(" and ")"

    I changed your first query , 

    string SubQuerTransfer = @"ISNULL (
      SELECT Tbl_ItemTransferInformation.ITI_ITEM_TRANSFER_QUANTITY FROM Tbl_Stock 
       INNER JOIN Tbl_ItemTransferInformation ON Tbl_Stock.STK_ITM_ITEM_ID = Tbl_ItemTransferInformation.ITI_ITM_ITEM_ID  
      					     AND Tbl_Stock.STK_SERIAL_ID = Tbl_ItemTransferInformation.ITI_STK_OUT_SERIAL_ID 
       INNER JOIN Tbl_Stock AS Tbl_Stock_1 ON Tbl_Stock.STK_ITM_ITEM_ID = Tbl_Stock_1.STK_ITM_ITEM_ID 
     					  AND Tbl_Stock.STK_STK_SERIAL_ID = Tbl_Stock_1.STK_SERIAL_ID 
       WHERE Tbl_Stock_1.STK_SERIAL_ID = A.STK_SERIAL_ID AND Tbl_Stock_1.STK_ITM_ITEM_ID = A.STK_ITM_ITEM_ID, 0) ";
     

    And, If you have a big query always , i feel , it's better you execute at sql server and copy here, saves lots of time to debug looking for error in query formation.

    I hope this helps you....


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".


    • Edited by Kris444 Wednesday, May 30, 2012 8:33 AM
    • Proposed as answer by Mike Feng Thursday, May 31, 2012 3:25 AM
    • Marked as answer by Mike Feng Wednesday, June 6, 2012 12:01 PM
    Wednesday, May 30, 2012 8:30 AM