Nested Case Statement

[EagleTempest]

SQL Server 2000

I'm not sure if it's possible to have a nested case statement or if I should use a subquery instead. I have inconsistent data to deal with.

Here's my logic statement:

If LEN(ProjNumber) > 0 THEN
  IF ISNUMBER(SUBSTRING(ProjNumber,1,4) -1 THEN
    SUBSTRING(ProjNumber,8, LEN(ProjNumber) -7)
  ELSE
    ProjNumber
  END IF
ELSE
  ProjNumb = "--"
END IF

How can I achieve this logic statement in SQL?

 

[bborissov]

You can:

SELECT CASE WHEN LEN(ProjNumber) > 0
            THEN CASE ISNUMBER(SUBSTRING(ProjNumber,1,4) -1
                      THEN SUBSTRING(ProjNumber,8, LEN(ProjNumber) -7)
                      ELSE ProjNumber
                 END
            ELSE ProjNumb = "--" END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[EagleTempest]

You answered for me that it is possible to nest case statements. I couldn't find a definite answer.

You code led me to this which worked:

SELECT     ProjectNumber, ClientName, ProjectDesc, SubjobDesc, Region,
CASE LEN(ProjectManager)
  WHEN 0 THEN
    CASE ISNUMERIC(SUBSTRING(ProjectManager, 1, 4))
      WHEN 1 THEN SUBSTRING(ProjectManager, 8, LEN(ProjectManager) - 7)
      ELSE ProjectManager
    END
  ELSE ProjectManager
END AS Manager

 

[bborissov]

I have my doubts here Especially for this:

CASE LEN(ProjectManager)
  WHEN 0 THEN
    CASE ISNUMERIC(SUBSTRING(ProjectManager, 1, 4))

Hmmm HOW if the LEN() of ProjectManager is equal to 0 (zero) than SUBSTRING(ProjectManager, 1, 4) could be NUMERIC?


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[EagleTempest]

You're right. I tried now

SELECT ProjectNumber, ClientName, ProjectDesc, SubjobDesc, Region,
CASE WHEN LEN(ProjectManager) >0 THEN
  CASE ISNUMERIC(SUBSTRING(ProjectManager, 1, 4)) [blue]=[/blue]1
    THEN SUBSTRING(ProjectManager, 8, LEN(ProjectManager) - 7) 
    ELSE ProjectManager
  END
  ELSE ProjectManager
END AS Manager

But receive an error that WHEN is unrecognized. This SQL statement is actually for a VB .NET Tableadapter.

Is this supposed to be -1 or =1:

CASE ISNUMERIC(SUBSTRING(ProjectManager, 1, 4)) [blue]=[/blue]1

 

[bborissov]

SELECT ProjectNumber, ClientName, ProjectDesc, SubjobDesc, Region,
CASE WHEN LEN(ProjectManager) >0 THEN
  CASE [COLOR=red]WHEN[/color] ISNUMERIC(SUBSTRING(ProjectManager, 1, 4)) =1
    THEN SUBSTRING(ProjectManager, 8, LEN(ProjectManager) - 7)
    ELSE ProjectManager
  END
  ELSE ProjectManager
END AS Manager

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

You missed a 'when'.

SELECT ProjectNumber, ClientName, ProjectDesc, SubjobDesc, Region,
CASE WHEN LEN(ProjectManager) >0 THEN
  CASE [!]When[/!] ISNUMERIC(SUBSTRING(ProjectManager, 1, 4)) =1
    THEN SUBSTRING(ProjectManager, 8, LEN(ProjectManager) - 7)
    ELSE ProjectManager
  END
  ELSE ProjectManager
END AS Manager

If you use the above code, you'll see that it works (or at least it should ). Now that you see that, let's take a closer look at it. If I understand correctly, some ProjectManager's have numbers in the data. Furthermore, if the first 4 characters are numeric, then you want to remove the first 8. In this case, you decided on nested case statements, but you don't need to. Basically, what I am trying to say is that you don't need to have the outer case statement at all. In fact, the following code should produce the same results.

[COLOR=blue]SELECT[/color] ProjectNumber, 
       ClientName, 
       ProjectDesc, 
       SubjobDesc, 
       Region,
       [COLOR=blue]CASE[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]ISNUMERIC[/color]([COLOR=#FF00FF]SUBSTRING[/color]([COLOR=#FF00FF]IsNull[/color](ProjectManager, [COLOR=red]''[/color]), 1, 4)) =1
            [COLOR=blue]THEN[/color] [COLOR=#FF00FF]SUBSTRING[/color](ProjectManager, 8, LEN(ProjectManager) - 7)
            [COLOR=blue]ELSE[/color] ProjectManager
            [COLOR=blue]END[/color] [COLOR=blue]AS[/color] Manager

IsNumeric can only return 1 of 2 values. 1 or 0. 1 is returned when the parameter to the IsNumeric function can be converted to a number. 0 is returned when it cannot.

Ex: (copy/paste this to a query analyzer window)

[tt][blue]
Select IsNumeric(NULL)
Select IsNumeric('abc')
Select IsNumeric('24')
[/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[EagleTempest]

Won't SUBSTRING('',1,4) return an error being it's length is zero??

 

[EagleTempest]

I guess not.

 

[ESquared]

How about you try it in query analyzer and see if you get an error?