Error in Select Case

vba on error msgbox
vba on error exit sub
end on error resume next

Could somebody please tell me what they can see that is the problem in the code below?

--Query for DC Allocated Stock - Qty & Values
-------------------------------------------

    Declare @Brand varchar(50),
        @Div varchar(50),
        @Dept varchar(50),
        @Store varchar(50),
        @StyleOrSKU varchar(50)


    Set @Brand = 'TestProduct' --Test criteria
    set @Div = ''
    Set @Dept = ''
    set @Store = ''
    set @StyleOrSKU = ''

First query, table Qry_1_DCSOH - to return the stock quantity on hand

SELECT Brand, 
       CASE 
         WHEN Brand = @Brand THEN sum(AllocStockQty)
         WHEN Division = @Div THEN sum(AllocStockQty)
         WHEN Department = @Dept THEN sum(AllocStockQty)
         WHEN Store = @Store THEN sum(AllocStockQty)
         WHEN StyleOrSKU = @StyleOrSKU THEN sum(AllocStockQty)
         ELSE 'NoMatch' 
      END as [Allocated Stock Qty]
FROM Qry_2_DCAllocStock 
GROUP BY Brand, Division, Department, Store, StyleOrSKU

The error I get is Msg 8114, Level 16, State 5, Line 20 Error converting data type varchar to float.

But I am really not sure where the problem is - please assist.

You have varchar(50) types.. and it seems the from clause is returning some numeric values.

Cast the When clauses to var char or use the returned types on the variables.

Check the error number with Select Case statement : Resume , Check the error number with Select Case statement : Resume « Language Basics « VBA / Excel / Access / Word. Each Select Case construct must be terminated with an End Select statement. This error has the following cause and solution: You used a Select Case statement without a corresponding End Select statement. Check if there is an incorrectly matched Select CaseEnd Select structure inside an outer Select CaseEnd Select structure. If you have nested Select Case statements, each must have a matching End Select.

The mistake is that you are trying to force a no numeric value with the else option for a computed expression that has already float values. It can be corrected like this:

SELECT Brand, 
   CASE 
     WHEN Brand = @Brand THEN sum(AllocStockQty)
     WHEN Division = @Div THEN sum(AllocStockQty)
     WHEN Department = @Dept THEN sum(AllocStockQty)
     WHEN Store = @Store THEN sum(AllocStockQty)
     WHEN StyleOrSKU = @StyleOrSKU THEN sum(AllocStockQty)
     ELSE 0 
  END as [Allocated Stock Qty]
FROM Qry_2_DCAllocStock 
GROUP BY Brand, Division, Department, Store, StyleOrSKU

[RESOLVED] Select Case error-VBForums, Re: Select Case error​​ At the main Case statement you are comparing argv(sz). Depending on the match then you are doing a sub select case statement with the same variable. In the sub case statements, the only case that is going to be true is your case else clause. With that the sub selects are pointless. Check the error number with Select Case statement : Resume « Language Basics « VBA / Excel / Access / Word

You're mixing data types in your CASE.

The return type of CASE is:

... the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

The highest precedence type is float in your example.

Either change the type of your else result or convert the sum results to some char type.

type mismatch error for select case, type mismatch error for select case. Hi everyone,. I am stuck at this line of code. Select Case pnum_PQR Or pnum_WPS Or pnum_PQR2 Or  A Case statement must occur within a Select CaseEnd Select Block. This error has the following cause and solution: A Case statement can't be matched with a preceding Select Case statement. Check other control structures within the Select CaseCase structure and verify that they are correctly

Select Case error "Case without Select Case", For the following code, I'm getting the "Case without Select Case" error (On Case 3 to 5assuming more are wrong too, but debug can't get  CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING. Transact-SQL Syntax Conventions. Syntax

Raise an Error is sql Case Statement, Hi SQL Gurus I would want to be able to raise an exception in a SQL Server Transact Statement. I am using a case statement like SELECT CASE WHEN  If the code within a Case or Case Else statement block does not need to run any more of the statements in the block, it can exit the block by using the Exit Select statement. This transfers control immediately to the statement following End Select. Select Case constructions can be nested.

Select Case Error, I"m getting a Type Mismatch Error for the following VBA Select Case Sub SubCat(​) Dim SubCat As String, CrseID As String, CrseDescr As  The SQL CASE Statement. The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

Comments
  • What kind of language is that? Can you adjust the tagging of your question?
  • I'm guessing this is SQL. It would be helpful for you to specify which is line 20, but the error message is pretty sure that you're trying to get a varchar datatype to equate to a float somewhere.
  • Apologies folks - this is SQL
  • formatting, grammar
  • Thanks all for your help - sorted out!