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

Re: RE: [SQLQueriesNoCode] CASE - ISNULL - What Am I Missing?

Expand Messages
  • Mark E
    Coalesce is ansi standard whereas isnull is t-sql and could go away in the future. Mark Sent from Yahoo! Mail on Android From: John Warner ; To: ; Subject: RE:
    Message 1 of 7 , May 12, 2012
      Coalesce is ansi standard whereas isnull is t-sql and could go away in the future.

      Mark

      Sent from Yahoo! Mail on Android

      From: John Warner ;
      To: ;
      Subject: RE: [SQLQueriesNoCode] CASE - ISNULL - What Am I Missing?
      Sent: Sat, May 12, 2012 9:43:35 AM

       

      I can't speak for all nor for the OP but null can cause procedural code to have an issue, null isn't a string or char array and thus will need conversion. Often it is easier and more expedient to have the 'conversion' made by the DB engine.
      I'm sure someone has already posted this suggestion but to repeat isn't COALESCE a standard SQL function these days?

      http://www.w3schools.com/sql/sql_isnull.asp

      John Warner

      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Mark E
      > Sent: Thursday, May 10, 2012 5:54 AM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: Re: [SQLQueriesNoCode] CASE - ISNULL - What Am I Missing?
      >
      > Arnie is correct about the subselect - it can only return a single value. That
      > may be an issue unless the code you removed changed it in such a way that it
      > actually works.
      >
      > Why are you checking for a null value on a name? Why not simply return the
      > value as-is?
      >
      >
      > Mark
      >
      >
      >
      > ________________________________
      > From: Arnie Rowland
      > To: SQLQueriesNoCode@yahoogroups.com
      > Sent: Wednesday, May 9, 2012 6:42 PM
      > Subject: RE: [SQLQueriesNoCode] CASE - ISNULL - What Am I Missing?
      >
      >
      >
      > The simple minded question that I pose, and I hope you will excuse my
      > simple-mindedness, are there rows where the p.[FirstName] column IS
      > NULL, or does it contain the string value 'NULL'?
      >
      > I don't see a CASE statement, and your use of ISNULL appears correct.
      >
      > However, more to the point, your sub-select is returning two values -and
      > that is not allowed in this usage. I'm going out on a limb here and
      > assuming that some of the code that you are protecting us from is
      > actually concatenating pro.[AccountManagerUserName] and ACount -which,
      > since pro.[AccountManagerUserName] IS NULL , will return a NULL.
      >
      > If that is not correct, then you are going to have to disclose more of
      > the code since this sniplet will only cause an error as two values are
      > returned for 'test'.
      >
      > Regards,
      >
      > Arnie Rowland | Westwood Consulting, LLC | MVP SQL Server
      >
      > "You cannot do a kindness too soon because you never know how soon
      > it will be too late."
      > -Ralph Waldo Emerson
      >
      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Andy Mills
      > Sent: Wednesday, May 09, 2012 3:08 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: [SQLQueriesNoCode] CASE - ISNULL - What Am I Missing?
      >
      > Hello Everyone:
      >
      > For the life of me, I can't get this case statement / ISNULL function to
      > replace the NULL values (please note: I took out a bunch of the SELECT
      > statement as well as the "FROM & WHERE" clause as I don't think it
      > matters.
      > But for the Sub-Select statements, no matter how I try to use an 'ISNULL
      > function' or a 'CASE statement', the value is always NULL. I was hoping
      > a second set of eyes could tell me what I am missing.
      >
      > SELECT
      > r.RoleName,
      > ISNULL(p.FirstName,'') as 'FirstName',
      > (select
      > *
      > FROM
      > (select
      > pro.AccountManagerUserName,
      > count(AccountManagerUserName) as
      > ACount
      > from
      > Profile pro
      > where
      > pro.RoleType = 'Educator'
      > and pro.AccountManagerUserName IS
      > NULL
      > and pro.UserId = p.userId
      > group by
      > pro.AccountManagerUserName
      > ) AS 'TEA3') AS 'test'
      > from dbo.AllMembers am
      >
      > [Non-text portions of this message have been removed]
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >





      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.