[RESOLVED] IF within select statement-VBForums
Results 1 to 7 of 7

Thread: [RESOLVED] IF within select statement

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    34

    Resolved [RESOLVED] IF within select statement

    I have a SQL select statement that calculates the value of one of the elements being returned by the select. Following is that SQL.
    , AVGManual =
    (select count(*)
    from CustomerFeedback as AV
    where av.SellingArea <> 'Find Merchandise'
    and av.Store = cf.Store
    )
    / (select count(distinct(tl.associatenumber))
    from CustomerFeedback as tl
    where tl.SellingArea <> 'Find Merchandise'
    and tl.Store = cf.Store
    )

    ----------------------------------------------------------------------------------
    The problem is that the divisor is a zero in some cases and gets an error.
    I tried writing an IF statement but it is giving me all kinds of errors. See
    below:
    , if (select count(distinct(tl.associatenumber))
    from CustomerFeedback as tl
    where tl.SellingArea <> 'Find Merchandise'
    and tl.Store = cf.Store) > 0
    BEGIN
    AVGManual =
    (select count(*)
    from CustomerFeedback as AV
    where av.SellingArea <> 'F M'
    and av.Store = cf.Store
    )
    / (select count(distinct(tl.associatenumber))
    from CustomerFeedback as tl
    where tl.SellingArea <> 'F M'
    and tl.Store = cf.Store
    )
    END
    ELSE
    AVGManual = 0

    , PCTTTL = 22

    ------------------------------------------------------------------------------------
    I want AVGManual to be 0 or a calculated value.

  2. #2
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,208

    Re: IF within select statement

    What database are you using?
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    34

    Re: IF within select statement

    SQL Server 2008 R2

  4. #4
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,208

    Re: IF within select statement

    Could you please post the table structure and a few insert rows for your data?

    I think this doesn't require an IF condition at all.

    It is possible to use the AVG function in SQL Server and the COALESCE function to get you the results you need. That would also generate the results faster compared to the way you're currently going about it.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    3,488

    Re: IF within select statement

    I agree with abhijit that there are probably easier ways of doing this but there are a few obvious problems with what you've done so far. First of all you can't just assign a value to a variable like that in TSQL. You need to SET it:-
    Set @Var = (Select Value From Somewhere)

    or Select it:-
    Select @Var = Value From Somewhere

    Other than that you'd really have to post your full SQL (I can se you've started with a comma so I don't think we're seeing everything) and tell us what the errors actually are.
    When one of my minions says, "Hey, he's just one guy, what can he do?" I say "This"... and shoot them.

    The problem with putting your lair in a volcano is keeping your robot army from melting.

    I know that the human being and the fish can coexist peacefully - George Bush

  6. #6

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    34

    Re: IF within select statement

    Thanks guys. Someone from another team was able to help.

  7. #7
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,208

    Thumbs up Re: IF within select statement

    Quote Originally Posted by dlary9890 View Post
    Thanks guys. Someone from another team was able to help.
    In this case, you should post the solution that you used and mark this thread as resolved.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.