Results 1 to 11 of 11

Thread: Sql Case

  1. #1
    Join Date
    Jul 2008
    Posts
    16

    Unanswered: Sql Case

    Hi

    The code fragment below shows what I'm trying to attempt i.e. use a CASE statement to vary the WHERE conditions based on condition flags that can be 0, 1, 2 or 3. This is not working - can someone please suggest an alternative approach to accomplish this. So for example:
    if @IDESAC=0 then {where} DESAC > 0
    elseif @IDESAC=1 then {where} DESAC <= @ADESAC
    ...
    etc

    Hope this makes sense. TIA


    Code:
    CREATE PROCEDURE getNumberSections 
    @DISTRICT VARCHAR(25)='%',
    @COUNTY VARCHAR(25)='%',
    @MIX VARCHAR(25)='%',
    @GRADE VARCHAR(25)='%',
    @FAC VARCHAR(25)='%',
    @CLIMATE VARCHAR(25)='%',
    @ISPECYR VARCHAR(25)='%',
    @CONSTDATE VARCHAR(25)='%',
    @IDESAC INT=0,
    @ADESAC FLOAT=4.5,
    @BDESAC FLOAT=0.5,
    @IFLDDENSITY INT=0,
    @AFLDDENSITY FLOAT=93.,
    @BFLDDENSITY FLOAT=1.,
    @ILABGMB INT=0,
    @ALABGMB FLOAT=96.,
    @BLABGMB FLOAT=1.,
    @IVMA INT=0,
    @AVMA FLOAT=14.,
    @BVMA FLOAT=1,
    @IHT INT=0,
    @AHT FLOAT=4.,
    @BHT FLOAT=0.5
    AS
    SELECT COUNT(DISTINCT CONTID) AS SECS
    FROM DB_PRJS
    WHERE DISTRICT LIKE @DISTRICT AND 
    COUNTY LIKE @COUNTY AND 
    MIX LIKE @MIX AND 
    GRADE LIKE @GRADE AND 
    FAC LIKE @FAC AND 
    CLIMATE LIKE @CLIMATE AND 
    ISPECYR LIKE @ISPECYR AND 
    CONSTDATE LIKE @CONSTDATE AND DESAC
    CASE 
    WHEN @IDESAC=0 THEN > 0
    WHEN @IDESAC=1 THEN <= @ADESAC
    WHEN @IDESAC=2 THEN BETWEEN @ADESAC-@BDESAC AND @ADESAC+@BDESAC
    WHEN @IDESAC=3 THEN >= @ADESAC
    END
    AND FLDDENSITY
    CASE 
    WHEN @IFLDDENSITY=0 THEN > 0
    WHEN @IFLDDENSITY=1 THEN <= @AFLDDENSITY
    WHEN @IFLDDENSITY=2 THEN BETWEEN @AFLDDENSITY-@BFLDDENSITY AND @AFLDDENSITY+@BFLDDENSITY
    WHEN @IFLDDENSITY=3 THEN >= @AFLDDENSITY
    END
    AND LABGMB
    CASE 
    WHEN @ILABGMB=0 THEN > 0
    WHEN @ILABGMB=1 THEN <= @ALABGMB
    WHEN @ILABGMB=2 THEN BETWEEN @ALABGMB-@BLABGMB AND @ALABGMB+@BLABGMB
    WHEN @ILABGMB=3 THEN >= @ALABGMB
    END
    AND VMA
    CASE 
    WHEN @IVMA=0 THEN > 0
    WHEN @IVMA=1 THEN <= @AVMA
    WHEN @IVMA=2 THEN BETWEEN @AVMA-@BVMA AND @AVMA+@BVMA
    WHEN @IVMA=3 THEN >= @AVMA
    END
    AND HT
    CASE 
    WHEN @IHT=0 THEN > 0
    WHEN @IHT=1 THEN <= @AHT
    WHEN @IHT=2 THEN BETWEEN @AHT-@BHT AND @AHT+@BHT
    WHEN @IHT=3 THEN >= @AHT
    END
    adfs

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Mmmmm fairly certain you can't use CASE in this where clause because your changing the operator >,=,<>......

    some would jump straight into dynamic SQL to do this query

    probably a better idea would be to create a #temp table - pop it according to your incomming variable values - then join your final select to it.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2008
    Posts
    16
    Thanks for the reply. Not sure what you mean by dynamic SQL - is this when you build your SQL statement in a script (e.g. php, python) before passing to the server.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Check out sp_executesql in BoL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Nope it's

    DECLARE @Sql VarChar(500)
    SET @Sql ='SELECT'+' 1'
    PRINT @Sql
    EXEC(@sql)

    OR use sp_executesql as suggested
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Jul 2008
    Posts
    16
    aaah! - lightbulbs are a flashing. Thanks, that makes sense.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE PROCEDURE getNumberSections 
       @DISTRICT	VARCHAR(25) = '%'
    ,  @COUNTY	VARCHAR(25) = '%'
    ,  @MIX		VARCHAR(25) = '%'
    ,  @GRADE	VARCHAR(25) = '%'
    ,  @FAC		VARCHAR(25) = '%'
    ,  @CLIMATE	VARCHAR(25) = '%'
    ,  @ISPECYR	VARCHAR(25) = '%'
    ,  @CONSTDATE	VARCHAR(25) = '%'
    ,  @IDESAC	INT = 0
    ,  @ADESAC	FLOAT = 4.5
    ,  @BDESAC	FLOAT = 0.5
    ,  @IFLDDENSITY	INT = 0
    ,  @AFLDDENSITY	FLOAT = 93.
    ,  @BFLDDENSITY	FLOAT = 1.
    ,  @ILABGMB	INT = 0
    ,  @ALABGMB	FLOAT = 96.
    ,  @BLABGMB	FLOAT = 1.
    ,  @IVMA	INT = 0
    ,  @AVMA	FLOAT = 14.
    ,  @BVMA	FLOAT = 1
    ,  @IHT		INT = 0
    ,  @AHT		FLOAT = 4.
    ,  @BHT		FLOAT = 0.5
    AS
    
    SELECT COUNT(DISTINCT CONTID) AS SECS
       FROM DB_PRJS
       WHERE DISTRICT LIKE @DISTRICT
          AND COUNTY LIKE @COUNTY
          AND MIX LIKE @MIX
          AND GRADE LIKE @GRADE
          AND FAC LIKE @FAC
          AND CLIMATE LIKE @CLIMATE
          AND ISPECYR LIKE @ISPECYR
          AND CONSTDATE LIKE @CONSTDATE
          AND ( (@IDESAC=0 AND DESAC > 0)
             OR (@IDESAC=1 AND DESAC <= @ADESAC)
             OR (@IDESAC=2 AND DESAC BETWEEN @ADESAC - @BDESAC AND @ADESAC + @BDESAC)
             OR (@IDESAC=3 AND DESAC >= @ADESAC))
          AND ( (@IFLDDENSITY=0 AND FLDDENSITY > 0)
             OR (@IFLDDENSITY=1 AND FLDDENSITY <= @AFLDDENSITY)
             OR (@IFLDDENSITY=2 AND FLDDENSITY BETWEEN @AFLDDENSITY-@BFLDDENSITY AND @AFLDDENSITY+@BFLDDENSITY)
             OR (@IFLDDENSITY=3 AND FLDDENSITY >= @AFLDDENSITY))
          AND ( (@ILABGMB=0 AND LABGMB > 0)
             OR (@ILABGMB=1 AND LABGMB <= @ALABGMB)
             OR (@ILABGMB=2 AND LABGMB BETWEEN @ALABGMB-@BLABGMB AND @ALABGMB+@BLABGMB)
             OR (@ILABGMB=3 AND LABGMB >= @ALABGMB))
          AND ( (@IVMA=0 AND VMA > 0)
             OR (@IVMA=1 AND VMA <= @AVMA)
             OR (@IVMA=2 AND VMA BETWEEN @AVMA-@BVMA AND @AVMA+@BVMA)
             OR (@IVMA=3 AND VMA >= @AVMA))
          AND ( (@IHT=0 AND HT > 0)
             OR (@IHT=1 AND HT <= @AHT)
             OR (@IHT=2 AND HT BETWEEN @AHT-@BHT AND @AHT+@BHT)
             OR (@iHT=3 AND HT >= @AHT))
    
    RETURN
    GO
    -PatP

  9. #9
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I Still think the #Temp LEFT Join would be much faster
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  10. #10
    Join Date
    Jul 2008
    Posts
    16
    Problem solved - Thanks Pat & GWilliy!

    You've made my day. I posted here not expecting much and came away with three possible solutions. Pat's is elegantly logical

    Here's the dynamic SQL:

    Code:
    CREATE PROCEDURE getTestNumberSections 
    @DISTRICT	VARCHAR(25)='%',
    @COUNTY		VARCHAR(25)='%',
    @MIX		VARCHAR(25)='%',
    @GRADE		VARCHAR(25)='%',
    @FAC		VARCHAR(25)='%',
    @CLIMATE	VARCHAR(25)='%',
    @ISPECYR	VARCHAR(25)='%',
    @CONSTDATE	VARCHAR(25)='%',
    @IDESAC		INT=0,
    @ADESAC		FLOAT=4.5,
    @BDESAC		FLOAT=0.5,
    @IFLDDENSITY	INT=0,
    @AFLDDENSITY	FLOAT=93.,
    @BFLDDENSITY	FLOAT=1.,
    @ILABGMB	INT=0,
    @ALABGMB	FLOAT=96.,
    @BLABGMB	FLOAT=1.,
    @IVMA		INT=0,
    @AVMA		FLOAT=14.,
    @BVMA		FLOAT=1,
    @IHT		INT=0,
    @AHT		FLOAT=4.,
    @BHT		FLOAT=0.5
    AS
    DECLARE @SQL VARCHAR(500)
    SET @SQL ='SELECT COUNT(DISTINCT CONTID) AS SECS '+
    'FROM DB_PRJS '+
    'WHERE DISTRICT LIKE '''+@DISTRICT+''' AND '+
    'COUNTY LIKE '''+@COUNTY+''' AND '+
    'MIX LIKE '''+@MIX+''' AND '+
    'GRADE LIKE '''+@GRADE+''' AND '+
    'FAC LIKE '''+@FAC+''' AND '+
    'CLIMATE LIKE '''+@CLIMATE+''' AND '+
    'ISPECYR LIKE '''+@ISPECYR+''' AND '+
    'CONSTDATE LIKE '''+@CONSTDATE+''' AND '
    IF (@IDESAC=0)		SET @SQL=@SQL+'DESAC > 0 AND '
    IF (@IDESAC=1)		SET @SQL=@SQL+'DESAC <= '+@ADESAC+' AND '
    IF (@IDESAC=2)		SET @SQL=@SQL+'DESAC BETWEEN '+@ADESAC+'-'+@BDESAC+' AND '+@ADESAC+'+'+@BDESAC+' AND '
    IF (@IDESAC=3)		SET @SQL=@SQL+'DESAC >= '+@ADESAC+' AND '
    IF (@IFLDDENSITY=0)	SET @SQL=@SQL+'FLDDENSITY > 0 AND '
    IF (@IFLDDENSITY=1)	SET @SQL=@SQL+'FLDDENSITY <= '+@AFLDDENSITY+' AND '
    IF (@IFLDDENSITY=2)	SET @SQL=@SQL+'FLDDENSITY BETWEEN '+@AFLDDENSITY+'-'+@BFLDDENSITY+' AND '+@AFLDDENSITY+'+'+@BFLDDENSITY+' AND '
    IF (@IFLDDENSITY=3)	SET @SQL=@SQL+'FLDDENSITY >= '+@AFLDDENSITY+' AND '
    IF (@ILABGMB=0)		SET @SQL=@SQL+'LABGMB > 0 AND '
    IF (@ILABGMB=1)		SET @SQL=@SQL+'LABGMB <= '+@ALABGMB+' AND '
    IF (@ILABGMB=2)		SET @SQL=@SQL+'LABGMB BETWEEN '+@ALABGMB+'-'+@BLABGMB+' AND '+@ALABGMB+'+'+@BLABGMB+' AND '
    IF (@ILABGMB=3)		SET @SQL=@SQL+'LABGMB >= '+@ALABGMB+' AND '
    IF (@IVMA=0)		SET @SQL=@SQL+'VMA > 0 AND '
    IF (@IVMA=1)		SET @SQL=@SQL+'VMA <= '+@AVMA+' AND '
    IF (@IVMA=2)		SET @SQL=@SQL+'VMA BETWEEN '+@AVMA+'-'+@BVMA+' AND '+@AVMA+'+'+@BVMA+' AND '
    IF (@IVMA=3)		SET @SQL=@SQL+'VMA >= '+@AVMA+' AND '
    IF (@IHT=0)		SET @SQL=@SQL+'HT > 0'
    IF (@IHT=1)		SET @SQL=@SQL+'HT <= '+@AHT
    IF (@IHT=2)		SET @SQL=@SQL+'HT BETWEEN '+@AHT+'-'+@BHT+' AND '+@AHT+'+'+@BHT
    IF (@IHT=3)		SET @SQL=@SQL+'HT >= '+@AHT
    
    --PRINT @SQL
    EXEC(@SQL)
    
    GO
    Last edited by Andre Smit; 07-10-08 at 12:46.

  11. #11
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Pat's is elegantly logical
    That is because Pat is an elegant Guru
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •