locked
Problem with SUM() with varbinary data RRS feed

  • Question

  • Hello everybody,

    I have a problem, my information in BD is encrypted. but i need to desencryte and convert to numeric and then get a SUM()

    I'm trying to do it, eg

    SELECT @TOT = SUM(cast(CAST(isnull(decryptbykey(TOT_TRAB_INCLUIDOS),0) as varchar(15)) as float))   from TABLE_XX

    or 

     SELECT @TOT_Trabajadores = SUM(cast(CAST(decryptbykey(TOT_TRAB_INCLUIDOS) as varchar(15)) as float))  from TABLE_XX

    This error show me: Operand data type varbinary(max) is invalid for sum operator.

    Someone may i help me, please :(

    Thks so much

    Friday, June 14, 2013 9:16 PM

Answers

  • I have had similar problems before.  Try this:

    SELECT 
    @TOT = SUM(imss_cuota_fija),
    @Excedente = sum(IMSS_Exced_3_SMGDF)
    FROM (
    SELECT  LLAVE, id_reg_pat,
           imss_cuota_fija = (cast(CAST(decryptbykey(imss_cuota_fija) as varchar(15)) as float) )
          ,IMSS_Exced_3_SMGDF = (cast(CAST(decryptbykey(IMSS_Exced_3_SMGDF) as varchar(15)) as float) )  
         from TABLE_1    
         where LLAVE = @LLAVE and id_reg_pat = @REG_PAT    
    ) a
         GROUP BY LLAVE, id_reg_pat  
    

    • Marked as answer by dirc_dirc Monday, June 17, 2013 3:34 PM
    Monday, June 17, 2013 3:07 PM

All replies

  • >This error show me: Operand data type varbinary(max) is invalid for sum operator.

    SUM requires numeric data.  Convert varbinary(max) to numeric first.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Friday, June 14, 2013 9:24 PM
  • I've tried to do this:

     SUM( cast( CAST( decryptbykey(Tot_Trab_Incluidos) as varchar(15)) as int) ) 

    But i get the same error.

    So, i have to convert my rows to numeric first in a temporary table, and then try to SUM(), is the only way?

    Thanks so much

    Friday, June 14, 2013 9:47 PM
  • Demo:

    DECLARE @vb varbinary(max) = newid();
    -- 0x098D5DFFFDFD32488EEAA020EBDA7F17
    SELECT @vb;
    SELECT convert(bigint, @vb);   -- -8148524512517128425
    SELECT SUM(convert(bigint, @vb));  -- -8148524512517128425


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Friday, June 14, 2013 10:00 PM
  • The error message comes from another piece of code than you are showing is. In this example SUM sees a float value. decryptbykey returs varbinary(8000) and not varbinary(MAX), so this clearly not the culprit.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 14, 2013 10:13 PM
  • My rows are encrypted with Symetrics Keys, in my stored procedure,

    1.- Open my simetric key,

    2. I have a function descrypted decryptbykey

    3. I think that i have to valited if my value is NULL, but i dont know how to do this if my column is varbinary.

    4. I've tried to convert first in varchar and then in numeric, so then try to sum()

    I dont know if its better to save my information in a temporary table with desencryted data, and then do a SUM with this information.

    Friday, June 14, 2013 10:26 PM
  • To help you we would need to see a full script that demonstrates the problem, and the full error message, including line number.

    It would also help to see the output from "SELECT @@version".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 15, 2013 8:50 AM
  • "SELECT @@version".

    Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)   Dec 10 2010 10:56:29   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

    Monday, June 17, 2013 2:02 PM
  • ALTER PROCEDURE sp_Carga    
     @LLAVE   varchar(11),    
     @REG_PAT  INT        
    AS    
    BEGIN    
     DECLARE 
     @TOT      numeric(15,2),
     @Excedente numeric(15,2) 

      OPEN SYMMETRIC KEY EncriptaData_Key         
      DECRYPTION   BY CERTIFICATE [cert_SIASS_Key2013]

       SELECT  
          @TOT = sum(cast(CAST(decryptbykey(imss_cuota_fija) as varchar(15)) as float) )
         ,@Excedente = sum(cast(CAST(decryptbykey(IMSS_Exced_3_SMGDF) as varchar(15)) as float) )  
        from TABLE_1    
        where LLAVE = @LLAVE and id_reg_pat = @REG_PAT    
        GROUP BY LLAVE, id_reg_pat  

      CLOSE SYMMETRIC KEY EncriptaData_Key  
    END  

    Show me error, in the lines when built SUM()

     
    Monday, June 17, 2013 2:04 PM
  • I have had similar problems before.  Try this:

    SELECT 
    @TOT = SUM(imss_cuota_fija),
    @Excedente = sum(IMSS_Exced_3_SMGDF)
    FROM (
    SELECT  LLAVE, id_reg_pat,
           imss_cuota_fija = (cast(CAST(decryptbykey(imss_cuota_fija) as varchar(15)) as float) )
          ,IMSS_Exced_3_SMGDF = (cast(CAST(decryptbykey(IMSS_Exced_3_SMGDF) as varchar(15)) as float) )  
         from TABLE_1    
         where LLAVE = @LLAVE and id_reg_pat = @REG_PAT    
    ) a
         GROUP BY LLAVE, id_reg_pat  
    

    • Marked as answer by dirc_dirc Monday, June 17, 2013 3:34 PM
    Monday, June 17, 2013 3:07 PM
  • Thank you so much!
    Monday, June 17, 2013 3:34 PM