Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    6

    Red face Unanswered: Subquery return more than one value error

    I appreciate anyone who can give me help with the following error:


    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    and below is the code

    Code:
    USE [Jessy]
    GO
    /****** Object:  View [dbo].[View_POVsPR]    Script Date: 10/22/2010 19:44:48 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    CREATE VIEW [dbo].[View_POVsPR]
    /*  
    Copyright (c) 2007 Jatis Solutions
    Menara Jamsostek Floor 16th
    
    All Rights Reserved.
    
    This software is the confidential and proprietary
    information of Jatis Solutions ("Confidential	Information").
    
    You shall not disclose such Confidential Information and
    shall use it only in accordance with the terms of the
    license agreement you entered into with Jatis.
    
    @version $Revision: 1.2 $
    */
    AS
    
    select PR.EmployeeId
    	,E.Name
    	,E.EmployeeNo
    	,L4.LookupName as LevelName
    	,L5.LookupName as Division
    	,L6.LookupName as Company
    	,PR.PRNumber
    	,PR.ChargeCodeID
    	,PRL.PRStatusCode
    	,PRL.RequiredDate
    	,PRL.ItemId
    	,IV.ItemName
    	,L9.LookupName as ItemType
    	,PRL.CostCategoryId
    	,CC.COANumber
    	,CC.PayCode
    	,PRL.Quantity as QuantityPR
    	,L7.LookupName as UOMPR
    	,L8.LookupName as Currency
    	,PRL.Price
    	,(PRL.Price*PRL.Quantity) as TotalPR
    	,PRL.Description
    	,PRL.Purpose
    	,(select ApprovalDate from TRX_Allworkflow WF where WF.EmployeeId=PR.EmployeeId and WF.WorkflowId like 'WFL_Purchasing_%' and WF.ReferenceId=PRL.PRLinesID and WF.ApproverStep = 1) as RMPMO
    	,(select ApprovalDate from TRX_Allworkflow WF where WF.EmployeeId=PR.EmployeeId and WF.WorkflowId like 'WFL_Purchasing_%' and WF.ReferenceId=PRL.PRLinesID and WF.ApproverStep = 2 and WF.ApproverId=1315) as MIS
    	,(select ApprovalDate from TRX_Allworkflow WF where WF.EmployeeId=PR.EmployeeId and WF.WorkflowId like 'WFL_Purchasing_%' and WF.ReferenceId=PRL.PRLinesID and ((WF.ApproverStep = 2 and WF.ApproverId<>1315) or (WF.approverStep=3))) as DivisionHead	
    	,POH.PONumber
    	,POH.PODate
    	,POH.POStatusCode
    	,POL.Quantity
    	,L1.LookupName as UOM
    	,L10.LookupName as CurrencyPO
    	,POL.Price as PricePO
    	,POL.Total
    	,POH.Remark
    	,V.name as VendorName
    	,POH.ShippingDate
    	,POH.PaymentTermTypeCode
    	,L2.LookupName as PaymentTerm
    	,L3.LookupName as POStatus
    from TRX_PurchasingPOLines POL 
    inner join TRX_PurchasingPRLines PRL on PRL.PRLinesID=POL.PRLinesId and PRL.DeleteFlag=0 and POL.DeleteFlag=0
    inner join TRX_PurchasingPOHeader POH on POH.POHeaderId=POL.POHeaderId and POH.DeleteFlag=0
    inner join TRX_PurchasingPRHeader PR on PR.PRHeaderId=PRL.PRHeaderId
    inner join MST_Employee E on PR.EmployeeId=E.EmployeeId
    inner join MST_ItemVendor IV on IV.ItemId=PRL.ItemId
    inner join MST_CostCategory CC on CC.CostCategoryId=PRL.CostCategoryId
    inner join MST_Vendor V on V.VendorId=POH.VendorId
    
    inner join MST_Lookup L1 on L1.LookupCode=POL.UomCode
    inner join MST_Lookup L2 on L2.LookupCode=POH.PaymentTermTypeCode
    inner join MST_Lookup L3 on L3.LookupCode=POH.POStatusCode
    inner join MST_Lookup L4 on L4.LookupCode=E.LevelCode
    inner join MST_Lookup L5 on L5.LookupCode=E.DivisionCode
    inner join MST_Lookup L6 on L6.LookupCode=E.CompanyCode
    inner join MST_Lookup L7 on L7.LookupCode=PRL.UomCode
    inner join MST_Lookup L8 on L8.LookupCode=PRL.CurrencyCode
    inner join MST_Lookup L9 on L9.LookupCode=IV.ItemTypeCode
    inner join MST_Lookup L10 on L10.LookupCode=POH.CurrencyCode

    thank you very much

  2. #2
    Join Date
    Oct 2010
    Posts
    6
    and please don't mind about the copyright

  3. #3
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Did you check if any of the following returned more than one value?

    ,(select ApprovalDate from TRX_Allworkflow WF where WF.EmployeeId=PR.EmployeeId and WF.WorkflowId like 'WFL_Purchasing_%' and WF.ReferenceId=PRL.PRLinesID and WF.ApproverStep = 1) as RMPMO
    ,(select ApprovalDate from TRX_Allworkflow WF where WF.EmployeeId=PR.EmployeeId and WF.WorkflowId like 'WFL_Purchasing_%' and WF.ReferenceId=PRL.PRLinesID and WF.ApproverStep = 2 and WF.ApproverId=1315) as MIS
    ,(select ApprovalDate from TRX_Allworkflow WF where WF.EmployeeId=PR.EmployeeId and WF.WorkflowId like 'WFL_Purchasing_%' and WF.ReferenceId=PRL.PRLinesID and ((WF.ApproverStep = 2 and WF.ApproverId<>1315) or (WF.approverStep=3))) as DivisionHead

  4. #4
    Join Date
    Oct 2010
    Posts
    6
    yes. those sub queries are returning more than 1 value, and what I have to do is to fix those queries.

    the code is given by my predecessor who built the system. and right now I have to fix the errors (enhancement). the problem is I am still newbie related to the SQL programming

    thank you

  5. #5
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    yes that is the issue. It is a subquery. As the value of the subquery is used in a select query, only one value should have been returned.

    Check your query logic and actual functinality and rewrite the query.

    your query is something like as below,
    Example 1:
    Table 1
    id name
    1 A
    1 B

    2 C

    Table 2:
    Id map age
    1 wert 12
    2 dfg 13

    select b.Id,
    (select a.name from Table1 a where a.Id = b.Id),
    b.map,
    b.age
    from Table2

    In the above case select a.name from Table1 a where a.Id = b.Id returns 2 values for Id = 1. This is an issue. Instead the below example will work without any problem.

    Table 1
    id name
    1 A
    2 C

    Table 2:
    Id map age
    1 wert 12
    2 dfg 13

    select b.Id,
    (select a.name from Table1 a where a.Id = b.Id),
    b.map,
    b.age
    from Table2

    here select a.name from Table1 a where a.Id = b.Id returns only one value for Id = 1

    Check your code logic and modify accordingly.

  6. #6
    Join Date
    Oct 2010
    Posts
    6

    Red face

    here the explanation about the logic of this script:

    1. 1st step approver is always RMPMO
    2. 2nd step approver (if have to) could be the MIS or
    DivisionHead (depend on the cirumstances)
    3. 3rd step approver (if have to) is always the DivisionHead

    I try to alter this script using CASE WHEN syntax but I am still not yet made it. Anyone could help? because I am still newbie about DB. thank you very much


  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    None of us can tell you what needs to be done to fix this. You have to decide how to correct these subqueries to return a single row. It could be thru the use of a distinct/min/max/altering the SQL to ensure a single value /etc...
    Dave Nance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •