Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    30

    Unanswered: [Err] 42000 - [SQL Server] Column 'dbo_40.TZZ' is invalid...

    Hi all, I need your help.

    I try this query but i have this error, why?

    Can you help me?
    Thanks in advance.
    Code:
    [Err] 42000 - [SQL Server] Column 'dbo_40.TZZ' is invalid in the select list 
    because it is not contained in either an aggregate function or the GROUP BY clause.
    
    
    
    SELECT
    	(
    		CASE
    		WHEN (GROUPING([MAT]) = 1) THEN
    			'Tot'
    		ELSE
    			[MAT]
    		END
    	) AS MAT,
    	[myNUmber]
    	
    FROM
    	(
    		SELECT DISTINCT
    			CASE
    		WHEN LEFT (TZZ, 2) = '1D'
    		OR LEFT (TZZ, 2) = '1F' THEN
    			'MAO'
    		WHEN LEFT (TZZ, 2) = '1G'
    		OR LEFT (TZZ, 2) = '1H' THEN
    			'MAE'
    		WHEN LEFT (TZZ, 2) = '1I'
    		OR LEFT (TZZ, 2) = '1M'
    		OR LEFT (TZZ, 2) = '1S'
    		OR LEFT (TZZ, 2) = '1O' THEN
    			'MAC'
    		WHEN LEFT (TZZ, 2) = '1P'
    		OR LEFT (TZZ, 2) = '1Q'
    		OR LEFT (TZZ, 2) = '1R' THEN
    			'MAS'
    		END AS [MAT],
    		COUNT (*) AS [myNUmber]		
    	FROM
    		dbo_40
    	WHERE
    		1 = 1
    	AND LEFT (TZZ, 2) NOT LIKE 'LG%'
    	) AS SubQ
    WHERE
    	1 = 1
    GROUP BY
    	[MAT] WITH ROLLUP
    ORDER BY
    	CASE [MAT]
    WHEN 'MAO' THEN
    	1
    WHEN 'MAE' THEN
    	2
    WHEN 'MAC' THEN
    	3
    ELSE
    	4
    END;
    Version sql server:
    Code:
    9.00.1399.06	RTM	Standard Edition

  2. #2
    Join Date
    Aug 2012
    Posts
    45
    cms9651, although you are able to order by an aliased column, you cannot group by one. In your derived table you have your case statement aliased as 'mat'. If you want to group by this column, you will need to either put the entire case statement in the group by clause, or maybe make it a cte first. In any event, I believe this is the problem.

    Greg

    *** Edit ***
    Ok, I just took another look and realized what I suggested above is not wholly correct. You are lacking a group by clause in your derived table. Look at the error message. As I stated above, you will need to group by the entire case statement. If you posted sample data, it would be easier to test a working solution.
    Last edited by gsnidow; 08-28-12 at 09:51.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    What's with the . . .

    WHERE 1 = 1

    ?????

    Is this leftover from testing?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Aug 2012
    Posts
    30
    Quote Originally Posted by PracticalProgram View Post
    What's with the . . .

    WHERE 1 = 1

    ?????

    Is this leftover from testing?
    Yes, Sir.
    Thank you for reply

Posting Permissions

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