Post reply

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  • November 21, 2012 at 1:32 am

    #285273

    Hi all, Am getting the above error in my procedure.

    this is my procedure

    ALTER procedure [dbo].[usp_Fixtures_BowlingAverages_Client]

    ( @Fixture_SeasonKey int,@Fixture_TeamKey int,@Fixture_FixtureType int)

    as

    begin

    select E.Member_FirstName as Player ,

    Count(A.FixturePlayer_MemberKey) As Matches ,

    SUM(D.Bowling_Overs) As Overs,

    SUM(D.Bowling_Maidens) as Maidens,

    SUM(D.Bowling_Runs) as Runs,

    SUM(D.Bowling_Wickets) as Wickets,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Overs),0),0) as Economy,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Wickets),0),0) as Average,

    (select (cast((D.Bowling_Wickets) as varchar)+'-'+cast((D.Bowling_Runs) as varchar)) from InningsBowlingDetails D

    where D.Bowling_Wickets in (select max(D.Bowling_Wickets) from InningsBowlingDetails D group by D.Bowling_MemberKey)

    and D.Bowling_Runs in (select MIN(D.Bowling_Runs) from InningsBowlingDetails D group by D.Bowling_MemberKey)

    group by D.Bowling_Wickets,D.Bowling_Runs,D.Bowling_MemberKey)as BestBowling

    from tbl_FixturePlayers A,tbl_Fixtures B,FixtureInnings C,InningsBowlingDetails D,tbl_ClubMembers E

    where B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END

    and E.Member_Key=D.Bowling_MemberKey

    and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END

    and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END

    and D.Bowling_MemberKey=A.FixturePlayer_MemberKey

    and A.FixturePlayer_FixtureKey=B.Fixture_Key

    and C.Innings_FixtureKey=B.Fixture_Key

    and D.Bowling_InningsKey=C.Innings_Key

    Group By A.FixturePlayer_MemberKey,E.Member_FirstName ,D.Bowling_Wickets,D.Bowling_Runs,D.Bowling_MemberKey

    end

  • November 21, 2012 at 1:38 am

    #1561123

    i want to display this format

    0-0

    2-2

    2-2

    0-3

    2-45

    5-49

  • November 21, 2012 at 1:47 am

    #1561126

    Hello and welcome to SSC,

    If you could knock up some sample data and DDL scripts, then your expected results based on the sample data provided that would be extremely useful in allowing people to help you.

    Have a read through this link --> http://qa.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, if you're unsure how best to lay this information out. Remember that if your sample data and DDL script is readily consumable, then the volunteers for this forum are much more likely to take time out from their work to give you a hand.

    Thanks.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 21, 2012 at 1:50 am

    #1561127

    Can you post the DDL (ideally temp tables/table vars) for the tables and some anonomised sample data that will give the results as this will enable us to help you.

    Being a bit of a cricket nut myself what is you are trying to do?

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • November 21, 2012 at 2:02 am

    #1561133

    If you could explain how you decide what are the best bowling figures for any one innings, this should be quite easy. For example, what order what you put these in, from best to worst?

    0-0

    2-2

    8-8

    0-3

    2-45

    5-49

    1-0

    1-90

    John

  • November 21, 2012 at 2:14 am

    #1561135

    John,

    Generally the Best bowling figures use the number of wickets first, then runs conceded, eg 5-7 is considered worse than 6-100, but 6-50 would be better than both of them.

    There are other stats that determing best bowling as well especially over a season such as Strike rate (balls/wicket), or Average (runs/wicket). It all depends on what you classify them as.

    something like

    Select i.Bowler, Max(w.wickets) wickets, Min(RunsPerWicket)

    From Innings i

    JOIN (Select Bowler, Max(Wickets) Wickets from Innings group by Bowler) w

    ON w.Bowler=i.Bowler

    AND w.Wickets=i.wickets

    group by i.Bowler

    Would probably give you the information on best bowling in an innings.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • November 21, 2012 at 2:31 am

    #1561143

    Yes, once we get DDL and sample data for InningsBowlingDetails we should be able to rewrite that subquery quite easily.

    John

  • November 21, 2012 at 2:38 am

    #1561145

    Looking forward to our friends in the USA helping out with a cricket question 🙂

    _____________________________________________________________________
    MCSA SQL Server 2012

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply