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

case statement not working SQL SERVER 2005

Expand Messages
  • pulverizers73
    Why is all_degrees null when degree2 is null? what am i missing? it only works if degree2 is not null I want DEGREE1, DEGREE2 (if not null) select
    Message 1 of 6 , Nov 5, 2009
    View Source
    • 0 Attachment
      Why is all_degrees null when degree2 is null? what am i missing? it only works if degree2 is not null

      I want DEGREE1, DEGREE2 (if not null)

      select degree1,degree2,
      (case degree2 when null then degree1 else degree1+', '+degree2 end) as all_degrees
      from fac_facultytbl
      group by degree1,degree2
      order by degree1,degree2

      degree1 degree2 all_degrees
      NULL NULL NULL
      D.O. NULL NULL
      Ed.D. NULL NULL
      J.D. NULL NULL
      LCSW NULL NULL
      M.D. NULL NULL
      M.D. D.V.M. M.D., D.V.M.
      M.D. J.D. M.D., J.D.
      M.D. M.H.S. M.D., M.H.S.
      M.D. M.P.H. M.D., M.P.H.
      M.D. M.S., C.P.E. M.D., M.S., C.P.E.
      M.D. MBBS M.D., MBBS
      M.D. Ph.D. M.D., Ph.D.
      M.P.H. NULL NULL
      M.S. NULL NULL
      MBBS NULL NULL
      MBBS D.Phil. MBBS, D.Phil.
      MBBS FAAP MBBS, FAAP
      MBBS M.P.H. MBBS, M.P.H.
      MSM LADAC MSM, LADAC
      MSW NULL NULL
      Ph.D. NULL NULL
      Ph.D. ABPP Ph.D., ABPP
      Ph.D. ATS Ph.D., ATS
      Ph.D. J.D. Ph.D., J.D.
      Ph.D. PT Ph.D., PT
      Ph.D. R.D. Ph.D., R.D.
      Pharm.D. NULL NULL
      PhD NULL NULL
      Psy.D. NULL NULL
      R.N. Ph.D. R.N., Ph.D.
    • dina_4ev3r
      I think you ll have to check Is NULL and not just NULL try this way select degree1,degree2, (case when degree2 IS NULL then degree1 else degree1+ , +degree2
      Message 2 of 6 , Nov 5, 2009
      View Source
      • 0 Attachment
        I think you'll have to check Is NULL and not just NULL

        try this way

        select degree1,degree2,
        (case when degree2 IS NULL then degree1 else degree1+', '+degree2 end) as all_degrees
        from fac_facultytbl
        group by degree1,degree2
        order by degree1,degree2

        HTH,
        Dina

        --- In SQLQueriesNoCode@yahoogroups.com, "pulverizers73" <pulverizers73@...> wrote:
        >
        > Why is all_degrees null when degree2 is null? what am i missing? it only works if degree2 is not null
        >
        > I want DEGREE1, DEGREE2 (if not null)
        >
        > select degree1,degree2,
        > (case degree2 when null then degree1 else degree1+', '+degree2 end) as all_degrees
        > from fac_facultytbl
        > group by degree1,degree2
        > order by degree1,degree2
        >
        > degree1 degree2 all_degrees
        > NULL NULL NULL
        > D.O. NULL NULL
        > Ed.D. NULL NULL
        > J.D. NULL NULL
        > LCSW NULL NULL
        > M.D. NULL NULL
        > M.D. D.V.M. M.D., D.V.M.
        > M.D. J.D. M.D., J.D.
        > M.D. M.H.S. M.D., M.H.S.
        > M.D. M.P.H. M.D., M.P.H.
        > M.D. M.S., C.P.E. M.D., M.S., C.P.E.
        > M.D. MBBS M.D., MBBS
        > M.D. Ph.D. M.D., Ph.D.
        > M.P.H. NULL NULL
        > M.S. NULL NULL
        > MBBS NULL NULL
        > MBBS D.Phil. MBBS, D.Phil.
        > MBBS FAAP MBBS, FAAP
        > MBBS M.P.H. MBBS, M.P.H.
        > MSM LADAC MSM, LADAC
        > MSW NULL NULL
        > Ph.D. NULL NULL
        > Ph.D. ABPP Ph.D., ABPP
        > Ph.D. ATS Ph.D., ATS
        > Ph.D. J.D. Ph.D., J.D.
        > Ph.D. PT Ph.D., PT
        > Ph.D. R.D. Ph.D., R.D.
        > Pharm.D. NULL NULL
        > PhD NULL NULL
        > Psy.D. NULL NULL
        > R.N. Ph.D. R.N., Ph.D.
        >
      • pulverizers73
        Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword is .
        Message 3 of 6 , Nov 5, 2009
        View Source
        • 0 Attachment
          Msg 156, Level 15, State 1, Line 1
          Incorrect syntax near the keyword 'is'.

          --- In SQLQueriesNoCode@yahoogroups.com, "dina_4ev3r" <chennai_dina@...> wrote:
          >
          > I think you'll have to check Is NULL and not just NULL
          >
          > try this way
          >
          > select degree1,degree2,
          > (case when degree2 IS NULL then degree1 else degree1+', '+degree2 end) as all_degrees
          > from fac_facultytbl
          > group by degree1,degree2
          > order by degree1,degree2
          >
          > HTH,
          > Dina
          >
          > --- In SQLQueriesNoCode@yahoogroups.com, "pulverizers73" <pulverizers73@> wrote:
          > >
          > > Why is all_degrees null when degree2 is null? what am i missing? it only works if degree2 is not null
          > >
          > > I want DEGREE1, DEGREE2 (if not null)
          > >
          > > select degree1,degree2,
          > > (case degree2 when null then degree1 else degree1+', '+degree2 end) as all_degrees
          > > from fac_facultytbl
          > > group by degree1,degree2
          > > order by degree1,degree2
          > >
          > > degree1 degree2 all_degrees
          > > NULL NULL NULL
          > > D.O. NULL NULL
          > > Ed.D. NULL NULL
          > > J.D. NULL NULL
          > > LCSW NULL NULL
          > > M.D. NULL NULL
          > > M.D. D.V.M. M.D., D.V.M.
          > > M.D. J.D. M.D., J.D.
          > > M.D. M.H.S. M.D., M.H.S.
          > > M.D. M.P.H. M.D., M.P.H.
          > > M.D. M.S., C.P.E. M.D., M.S., C.P.E.
          > > M.D. MBBS M.D., MBBS
          > > M.D. Ph.D. M.D., Ph.D.
          > > M.P.H. NULL NULL
          > > M.S. NULL NULL
          > > MBBS NULL NULL
          > > MBBS D.Phil. MBBS, D.Phil.
          > > MBBS FAAP MBBS, FAAP
          > > MBBS M.P.H. MBBS, M.P.H.
          > > MSM LADAC MSM, LADAC
          > > MSW NULL NULL
          > > Ph.D. NULL NULL
          > > Ph.D. ABPP Ph.D., ABPP
          > > Ph.D. ATS Ph.D., ATS
          > > Ph.D. J.D. Ph.D., J.D.
          > > Ph.D. PT Ph.D., PT
          > > Ph.D. R.D. Ph.D., R.D.
          > > Pharm.D. NULL NULL
          > > PhD NULL NULL
          > > Psy.D. NULL NULL
          > > R.N. Ph.D. R.N., Ph.D.
          > >
          >
        • John Warner
          SELECT degree1, degree2, (CASE WHEN degree2 IS NULL THEN degree1 ELSE degree1 + , + degree2 END) AS all_degrees FROM fac_facultytbl GROUP BY degree1,
          Message 4 of 6 , Nov 5, 2009
          View Source
          • 0 Attachment
            SELECT degree1, degree2,
            (CASE WHEN degree2 IS NULL THEN degree1 ELSE degree1 + ', ' +
            degree2 END)
            AS all_degrees
            FROM fac_facultytbl
            GROUP BY
            degree1,
            degree2
            ORDER BY
            degree1,
            degree2;


            This parses correctly according to SSMS for 2008. Naturally I can't submit
            the query.

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of pulverizers73
            > Sent: Thursday, November 05, 2009 2:13 PM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: [SQLQueriesNoCode] Re: case statement not working SQL SERVER
            > 2005
            >
            > Msg 156, Level 15, State 1, Line 1
            > Incorrect syntax near the keyword 'is'.
            >
            > --- In SQLQueriesNoCode@yahoogroups.com, "dina_4ev3r"
            > <chennai_dina@...> wrote:
            > >
            > > I think you'll have to check Is NULL and not just NULL
            > >
            > > try this way
            > >
            > > select degree1,degree2,
            > > (case when degree2 IS NULL then degree1 else degree1+', '+degree2 end)
            > as all_degrees
            > > from fac_facultytbl
            > > group by degree1,degree2
            > > order by degree1,degree2
            > >
            > > HTH,
            > > Dina
            > >
            > > --- In SQLQueriesNoCode@yahoogroups.com, "pulverizers73"
            > <pulverizers73@> wrote:
            > > >
            > > > Why is all_degrees null when degree2 is null? what am i missing? it
            only
            > works if degree2 is not null
            > > >
            > > > I want DEGREE1, DEGREE2 (if not null)
            > > >
            > > > select degree1,degree2,
            > > > (case degree2 when null then degree1 else degree1+', '+degree2 end)
            as
            > all_degrees
            > > > from fac_facultytbl
            > > > group by degree1,degree2
            > > > order by degree1,degree2
            > > >
            > > > degree1 degree2 all_degrees
            > > > NULL NULL NULL
            > > > D.O. NULL NULL
            > > > Ed.D. NULL NULL
            > > > J.D. NULL NULL
            > > > LCSW NULL NULL
            > > > M.D. NULL NULL
            > > > M.D. D.V.M. M.D., D.V.M.
            > > > M.D. J.D. M.D., J.D.
            > > > M.D. M.H.S. M.D., M.H.S.
            > > > M.D. M.P.H. M.D., M.P.H.
            > > > M.D. M.S., C.P.E. M.D., M.S., C.P.E.
            > > > M.D. MBBS M.D., MBBS
            > > > M.D. Ph.D. M.D., Ph.D.
            > > > M.P.H. NULL NULL
            > > > M.S. NULL NULL
            > > > MBBS NULL NULL
            > > > MBBS D.Phil. MBBS, D.Phil.
            > > > MBBS FAAP MBBS, FAAP
            > > > MBBS M.P.H. MBBS, M.P.H.
            > > > MSM LADAC MSM, LADAC
            > > > MSW NULL NULL
            > > > Ph.D. NULL NULL
            > > > Ph.D. ABPP Ph.D., ABPP
            > > > Ph.D. ATS Ph.D., ATS
            > > > Ph.D. J.D. Ph.D., J.D.
            > > > Ph.D. PT Ph.D., PT
            > > > Ph.D. R.D. Ph.D., R.D.
            > > > Pharm.D. NULL NULL
            > > > PhD NULL NULL
            > > > Psy.D. NULL NULL
            > > > R.N. Ph.D. R.N., Ph.D.
            > > >
            > >
            >
            >
            >
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
          • dina_4ev3r
            I don t have any instance of SQL server to check the query. But as far as I can recollect the syntax it should work.
            Message 5 of 6 , Nov 5, 2009
            View Source
            • 0 Attachment
              I don't have any instance of SQL server to check the query. But as far as I can recollect the syntax it should work.

              --- In SQLQueriesNoCode@yahoogroups.com, "pulverizers73" <pulverizers73@...> wrote:
              >
              > Msg 156, Level 15, State 1, Line 1
              > Incorrect syntax near the keyword 'is'.
              >
              > --- In SQLQueriesNoCode@yahoogroups.com, "dina_4ev3r" <chennai_dina@> wrote:
              > >
              > > I think you'll have to check Is NULL and not just NULL
              > >
              > > try this way
              > >
              > > select degree1,degree2,
              > > (case when degree2 IS NULL then degree1 else degree1+', '+degree2 end) as all_degrees
              > > from fac_facultytbl
              > > group by degree1,degree2
              > > order by degree1,degree2
              > >
              > > HTH,
              > > Dina
              > >
              > > --- In SQLQueriesNoCode@yahoogroups.com, "pulverizers73" <pulverizers73@> wrote:
              > > >
              > > > Why is all_degrees null when degree2 is null? what am i missing? it only works if degree2 is not null
              > > >
              > > > I want DEGREE1, DEGREE2 (if not null)
              > > >
              > > > select degree1,degree2,
              > > > (case degree2 when null then degree1 else degree1+', '+degree2 end) as all_degrees
              > > > from fac_facultytbl
              > > > group by degree1,degree2
              > > > order by degree1,degree2
              > > >
              > > > degree1 degree2 all_degrees
              > > > NULL NULL NULL
              > > > D.O. NULL NULL
              > > > Ed.D. NULL NULL
              > > > J.D. NULL NULL
              > > > LCSW NULL NULL
              > > > M.D. NULL NULL
              > > > M.D. D.V.M. M.D., D.V.M.
              > > > M.D. J.D. M.D., J.D.
              > > > M.D. M.H.S. M.D., M.H.S.
              > > > M.D. M.P.H. M.D., M.P.H.
              > > > M.D. M.S., C.P.E. M.D., M.S., C.P.E.
              > > > M.D. MBBS M.D., MBBS
              > > > M.D. Ph.D. M.D., Ph.D.
              > > > M.P.H. NULL NULL
              > > > M.S. NULL NULL
              > > > MBBS NULL NULL
              > > > MBBS D.Phil. MBBS, D.Phil.
              > > > MBBS FAAP MBBS, FAAP
              > > > MBBS M.P.H. MBBS, M.P.H.
              > > > MSM LADAC MSM, LADAC
              > > > MSW NULL NULL
              > > > Ph.D. NULL NULL
              > > > Ph.D. ABPP Ph.D., ABPP
              > > > Ph.D. ATS Ph.D., ATS
              > > > Ph.D. J.D. Ph.D., J.D.
              > > > Ph.D. PT Ph.D., PT
              > > > Ph.D. R.D. Ph.D., R.D.
              > > > Pharm.D. NULL NULL
              > > > PhD NULL NULL
              > > > Psy.D. NULL NULL
              > > > R.N. Ph.D. R.N., Ph.D.
              > > >
              > >
              >
            • J P
              Thanks, the suggestions triggered this solution.  Thanks for all responses, it pointed me in the right direction  
              Message 6 of 6 , Nov 5, 2009
              View Source
              • 0 Attachment
                Thanks, the suggestions triggered this solution.  Thanks for all responses, it pointed me in the right direction
                 
                selectcurrentstatus,degree1,degree2,(caseisnull(degree2,'0')when'0'thendegree1 elsedegree1+', '+degree2 end)asall_degreesfromfac_facultytblgroupbydegree1,degree2,currentstatusorderbydegree1,degree2




                ________________________________
                From: John Warner <john@...>
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Thu, November 5, 2009 1:55:50 PM
                Subject: RE: [SQLQueriesNoCode] Re: case statement not working SQL SERVER 2005

                 
                SELECT degree1, degree2,
                (CASE WHEN degree2 IS NULL THEN degree1 ELSE degree1 + ', ' +
                degree2 END)
                AS all_degrees
                FROM fac_facultytbl
                GROUP BY
                degree1,
                degree2
                ORDER BY
                degree1,
                degree2;

                This parses correctly according to SSMS for 2008. Naturally I can't submit
                the query.

                John Warner

                > -----Original Message-----
                > From: SQLQueriesNoCode@ yahoogroups. com
                > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of pulverizers73
                > Sent: Thursday, November 05, 2009 2:13 PM
                > To: SQLQueriesNoCode@ yahoogroups. com
                > Subject: [SQLQueriesNoCode] Re: case statement not working SQL SERVER
                > 2005
                >
                > Msg 156, Level 15, State 1, Line 1
                > Incorrect syntax near the keyword 'is'.
                >
                > --- In SQLQueriesNoCode@ yahoogroups. com, "dina_4ev3r"
                > <chennai_dina@ ...> wrote:
                > >
                > > I think you'll have to check Is NULL and not just NULL
                > >
                > > try this way
                > >
                > > select degree1,degree2,
                > > (case when degree2 IS NULL then degree1 else degree1+', '+degree2 end)
                > as all_degrees
                > > from fac_facultytbl
                > > group by degree1,degree2
                > > order by degree1,degree2
                > >
                > > HTH,
                > > Dina
                > >
                > > --- In SQLQueriesNoCode@ yahoogroups. com, "pulverizers73"
                > <pulverizers73@ > wrote:
                > > >
                > > > Why is all_degrees null when degree2 is null? what am i missing? it
                only
                > works if degree2 is not null
                > > >
                > > > I want DEGREE1, DEGREE2 (if not null)
                > > >
                > > > select degree1,degree2,
                > > > (case degree2 when null then degree1 else degree1+', '+degree2 end)
                as
                > all_degrees
                > > > from fac_facultytbl
                > > > group by degree1,degree2
                > > > order by degree1,degree2
                > > >
                > > > degree1 degree2 all_degrees
                > > > NULL NULL NULL
                > > > D.O. NULL NULL
                > > > Ed.D. NULL NULL
                > > > J.D. NULL NULL
                > > > LCSW NULL NULL
                > > > M.D. NULL NULL
                > > > M.D. D.V.M. M.D., D.V.M.
                > > > M.D. J.D. M.D., J.D.
                > > > M.D. M.H.S. M.D., M.H.S.
                > > > M.D. M.P.H. M.D., M.P.H.
                > > > M.D. M.S., C.P.E. M.D., M.S., C.P.E.
                > > > M.D. MBBS M.D., MBBS
                > > > M.D. Ph.D. M.D., Ph.D.
                > > > M.P.H. NULL NULL
                > > > M.S. NULL NULL
                > > > MBBS NULL NULL
                > > > MBBS D.Phil. MBBS, D.Phil.
                > > > MBBS FAAP MBBS, FAAP
                > > > MBBS M.P.H. MBBS, M.P.H.
                > > > MSM LADAC MSM, LADAC
                > > > MSW NULL NULL
                > > > Ph.D. NULL NULL
                > > > Ph.D. ABPP Ph.D., ABPP
                > > > Ph.D. ATS Ph.D., ATS
                > > > Ph.D. J.D. Ph.D., J.D.
                > > > Ph.D. PT Ph.D., PT
                > > > Ph.D. R.D. Ph.D., R.D.
                > > > Pharm.D. NULL NULL
                > > > PhD NULL NULL
                > > > Psy.D. NULL NULL
                > > > R.N. Ph.D. R.N., Ph.D.
                > > >
                > >
                >
                >
                >
                >
                > ------------ --------- --------- ------
                >
                > 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.