HELP with case statement [Divide by zero error encountered.] !

I am getting the same result pandey, I might have left that of by accident.
I
have tried it many ways.
case when sum (dids.supplier_cost) = '0'
then '0'
when sum (dids.supplier_cost) IS NULL
then '0'
when sum (dihs.qty_total) = '0'
then '0'
when sum (dihs.qty_total) IS NULL
then '0'
WHEN SUM (dids.revenue) = '0'
THEN '0'
WHEN SUM (dids.revenue) IS NULL
THEN '0'
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end [dollar_turns],
"Abhishek Pandey" wrote:
> in your case statement you r not checking for dids.supplier_cost = 0
> and in your else clause you are divinding by
> ( (dihs.qty_total) * dids.supplier_cost )
> hope thishelps
> rgds
> abhishek
> "ITDUDE27" wrote:
>i am not sure what exactly are you trying to do here..
in your case statement you are checking "sum(dids.supplier_cost)" and other
things for 0 and handling it accordingly but in else clause you are divindin
g
by dids.supplier_cost.
say for example
dids.supplier_cost has three values
1
0
3
while sum(dids.supplier_cost) <> 0 but this doest garuntee that individual
values are also not zero.
so in this case when it goes to else clause sql server experience a divide
by zero error coz what it will try to do is this
sum( somethingvalue / somenonzerovalue + somevalue/ 0 + somevalue/somevalue)
i hope this is clear enough.
You gotta design your query in a better way. if you are having a hard time
try to post your FULL query and some1 would be able to help you out.
regards
Abhishek
"ITDUDE27" wrote:
> I am getting the same result pandey, I might have left that of by accident
. I
> have tried it many ways.
> case when sum (dids.supplier_cost) = '0'
> then '0'
> when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> THEN '0'
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end [dollar_turns],
> "Abhishek Pandey" wrote:
>

Help with backups

Hi, I have a SQL server running on a remote network. On my local machine I
have the SQL client tools in which I use to connect to the SQL server. How
can I perform a backup locally. When I try, it looks at the remote drives
rather then my local drives when I try to backup to my local folder.
Thanks
David
You might try this:
backup database DB1 to disk = '\\mymachine\c:\mssql\backups\DB1_date.bck'
You could run that via SQL Analyzer or put it into a script or job.
hth.
"David" <dross@.si.rr.com> wrote in message
news:um2RfUZdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi, I have a SQL server running on a remote network. On my local machine
I
> have the SQL client tools in which I use to connect to the SQL server.
How
> can I perform a backup locally. When I try, it looks at the remote drives
> rather then my local drives when I try to backup to my local folder.
> Thanks
> David
>
sql

Help with backups

Hi, I have a SQL server running on a remote network. On my local machine I
have the SQL client tools in which I use to connect to the SQL server. How
can I perform a backup locally. When I try, it looks at the remote drives
rather then my local drives when I try to backup to my local folder.
Thanks
DavidYou might try this:
backup database DB1 to disk = '\\mymachine\c:\mssql\backups\DB1_date.bck'
You could run that via SQL Analyzer or put it into a script or job.
hth.
"David" <dross@.si.rr.com> wrote in message
news:um2RfUZdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi, I have a SQL server running on a remote network. On my local machine
I
> have the SQL client tools in which I use to connect to the SQL server.
How
> can I perform a backup locally. When I try, it looks at the remote drives
> rather then my local drives when I try to backup to my local folder.
> Thanks
> David
>