Loading ...
Sorry, an error occurred while loading the content.

RE: [mugh-sqlcon] Re: APRIL PUZZLE OF SQLCON

Expand Messages
  • Subramanya Veera (Wipro Ltd.)
    Hi, It can t be said as a wrong calculation. It is doing the work as expected. It depends on our requirement and the way how we have given the parameters to
    Message 1 of 1 , Apr 7, 2004
    • 0 Attachment

      Hi,

       

      It can’t be said as a wrong calculation. It is doing the work as expected. It depends on our requirement and the way how we have given the parameters
      to coalesce function.

       

      It would have been better if you have given the requirement first and then given the query created for that purpose and then ask what is the correction required

       

      Subbu

       


      From: meetrag [mailto:raghava.naraharaseti@...]
      Sent: Wednesday, April 07, 2004 3:43 PM
      To: sqlcon@yahoogroups.com
      Subject: [mugh-sqlcon] Re: APRIL PUZZLE OF SQLCON

       

      To Avoid

      SELECT emp_id,CAST(COALESCE(salary, hourly_wage * 40 * 52 ,   
          commission * num_sales) AS money) AS 'Total Salary'


      --- In sqlcon@yahoogroups.com, "meetrag" <meetrag@y...> wrote:
      > The problem is that all four columns allow NULL values.
      > The COALESCE function returns the first nonnull expression.
      > The following COALESCE statement produces the first nonnull wage
      > item.
      >  So the wrong Salary for that employee
      >
      > SELECT emp_id,CAST(COALESCE(hourly_wage * 40 * 52 ,    salary,
      >    commission * num_sales) AS money) AS 'Total Salary'
      >
      > --- In sqlcon@yahoogroups.com, veer wangoo <vwangoo@y...> wrote:
      > > Ue the following script :-
      > >
      > > SET NOCOUNT ON
      > > GO
      > > USE master
      > > IF EXISTS (SELECT TABLE_NAME FROM
      > > INFORMATION_SCHEMA.TABLES
      > >       WHERE TABLE_NAME = 'wages')
      > >    DROP TABLE wages
      > > GO
      > > CREATE TABLE wages
      > > (
      > >    emp_id      tinyint    identity,
      > >    hourly_wage   decimal   NULL,
      > >    salary      decimal    NULL,
      > >    commission   decimal   NULL,
      > >    num_sales   tinyint   NULL
      > > )
      > > GO
      > > INSERT wages VALUES(10.00, NULL, NULL, NULL)
      > > INSERT wages VALUES(20.00, NULL, NULL, NULL)
      > > INSERT wages VALUES(30.00, NULL, NULL, NULL)
      > > INSERT wages VALUES(40.00, NULL, NULL, NULL)
      > > INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
      > > INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
      > > INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
      > > INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
      > > INSERT wages VALUES(NULL, NULL, 15000, 3)
      > > INSERT wages VALUES(NULL, NULL, 25000, 2)
      > > INSERT wages VALUES(NULL, NULL, 20000, 6)
      > > INSERT wages VALUES(NULL, NULL, 14000, 4)
      > > GO
      > > SET NOCOUNT OFF
      > > GO
      > >
      > > --So we create a table with values and we use
      > >
      > > GO
      > > SELECT emp_id,CAST(COALESCE(hourly_wage * 40 * 52 ,
      > >    salary,
      > >    commission * num_sales) AS money) AS 'Total Salary'
      > >
      > > FROM wages
      > > GO
      > >
      > >
      > > and we get the nice result as per the beghaviour of
      > > COALESCE
      > >
      > > Now if  we alter the 5th row in insert as
      > > Go
      > > INSERT wages VALUES(50.00, 10000.00, NULL, NULL)
      > > Go
      > >
      > > and again run Select query we get wrong Salary for
      > > that employee.
      > >
      > > PUZZLE is why and how to avaoid it?
      > >
      > >
      > >
      > >
      > >
      > > __________________________________
      > > Do you Yahoo!?
      > > Yahoo! Small Business $15K Web Design Giveaway
      > > http://promotions.yahoo.com/design_giveaway/




      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net

      http://www.mugh.net/sql




    Your message has been successfully submitted and would be delivered to recipients shortly.