-
Apr 17th, 2012, 12:53 PM
#1
Thread Starter
Member
[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.
-
Apr 25th, 2012, 12:30 PM
#2
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
-
Apr 25th, 2012, 12:34 PM
#3
Thread Starter
Member
Re: IF within select statement
-
Apr 25th, 2012, 12:41 PM
#4
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
-
Apr 25th, 2012, 12:55 PM
#5
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Apr 25th, 2012, 02:15 PM
#6
Thread Starter
Member
Re: IF within select statement
Thanks guys. Someone from another team was able to help.
-
Apr 26th, 2012, 02:58 AM
#7
Re: IF within select statement
Originally Posted by dlary9890
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|