Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    99

    Unanswered: Case within Having ?

    Hi all,

    I tried the following code and obviously doesnt work, is there a way to simulate a case within having?


    Code:
    CREATE TABLE LTG (Name VARCHAR(10), B1 INT, B2 INT, B3 INT);
    
    INSERT INTO LTG VALUES
    ('Luis',1,0,0)
    INSERT INTO LTG VALUES
    ('Hector',0,1,0)
    INSERT INTO LTG VALUES
    ('Alejandro',0,0,1)
    
    
    DECLARE @S 	INT
    SET @S = 1
    
    SELECT	Name,
    	SUM (B1),
    	SUM (B2),
    	SUM (B3)
    FROM	LTG
    GROUP BY Name
    HAVING 
    	CASE @S
    		WHEN 1 THEN SUM (B1) != 0
            	WHEN 2 THEN SUM (B1) != 0
    		WHEN 3 THEN SUM (B1) != 0
                 END
    Thanks for your help

    Luis Torres

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    You don't have to simulate it. You can have it. Try This:

    Code:
    CREATE TABLE LTG (Name VARCHAR(10), B1 INT, B2 INT, B3 INT);
    
    INSERT INTO LTG VALUES
    ('Luis',1,0,0)
    INSERT INTO LTG VALUES
    ('Hector',0,1,0)
    INSERT INTO LTG VALUES
    ('Alejandro',0,0,1)
    
    
    DECLARE @S 	INT
    SET @S = 3
    
    SELECT	Name,
    	SUM (B1),
    	SUM (B2),
    	SUM (B3)
    FROM	LTG
    GROUP BY Name
    HAVING 1= CASE 
    		WHEN (@S= 1 AND SUM(B1) != 0) THEN 1 
            	WHEN (@S= 2 AND SUM(B2) != 0) THEN 1
    		WHEN (@S= 3 AND SUM(B3) != 0) THEN 1
    		ELSE 0
              END
    Last edited by cascred; 09-30-05 at 10:58.

  3. #3
    Join Date
    Aug 2004
    Posts
    99
    Thanks a lot !!!! , was toying with other ideas (which were messy), but I'm definetly using this one.

Posting Permissions

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