Column number of supplied values does not Mach table Definition - SQL SERVER

Asked

Viewed 68 times

1

I have the following problem in sql server when I put the CASE clause at the end of this line of code:

DECLARE @RESULTADO TABLE (
Total INT,
GrupoTitulo VARCHAR(255),
GrupoChave VARCHAR(255),
GrupoNome VARCHAR(255),
GrupoOrdem VARCHAR(255),
ProdGrupo VARCHAR(255),
ProdGrupoOrdem INT,
ProdCodEstr VARCHAR(40),
ProdCodRed VARCHAR(7),
ProdNome VARCHAR(80),
ProdUnidMedCod VARCHAR(7),
ProdUnidMedPos SMALLINT,
ValTotVendasFinal NUMERIC(14,2),
ValTotVendasIPI NUMERIC(14,2),
ValTotVendas NUMERIC(14,2),
ValTotDevolucoes NUMERIC(14,2),
QtdVendas NUMERIC(24,9),
QtdDevolucoes NUMERIC(24,9),

------
ValTotVendasFinalFD NUMERIC(14,2),
ValTotVendasIPIFD NUMERIC(14,2),
ValTotVendasFD NUMERIC(14,2),
ValTotDevolucoesFD NUMERIC(14,2),
QtdVendasFD NUMERIC(24,9),
QtdDevolucoesFD NUMERIC(24,9)
)


 INSERT INTO @RESULTADO
 SELECT
0,
CAST (CASE
    WHEN '' = 'CIDADE' THEN 'Cidade'
    WHEN '' = 'ESTADO' THEN 'Estado'
    WHEN '' = 'CLIENTE' THEN 'Cliente'
    WHEN '' = 'GRUPO' THEN 'Grupo de Produtos'
    WHEN '' = 'VENDEDOR' THEN 'Vendedor'
    WHEN '' = 'CATEGORIA' THEN 'Categoria'
END AS VARCHAR(255)) GrupoTitulo,
ISNULL(V.GrupoChave,D.GrupoChave) GrupoChave,
ISNULL(V.GrupoNome,D.GrupoNome) GrupoNome,
ISNULL(V.GrupoOrdem, D.GrupoOrdem),
CAST(CASE
    WHEN P.ProdCodEstr LIKE '07%'    THEN 'Fralda'
    WHEN P.ProdCodEstr LIKE '30.03%' THEN 'Bobina'
    WHEN P.ProdCodEstr LIKE '30%'    THEN 'Tissue'
    ELSE 'Outros'
END AS VARCHAR(255)) ProdGrupo,
    CASE
    WHEN P.ProdCodEstr LIKE '07%'    THEN 2
    WHEN P.ProdCodEstr LIKE '30.03%' THEN 3
    WHEN P.ProdCodEstr LIKE '30%'    THEN 1
    ELSE 4
END ProdGrupoOrdem,
P.ProdCodEstr, P.ProdCodRed, P.ProdNome,
UPPER(ISNULL(V.ProdUnidMedCod, D.ProdUnidMedCod)) ProdUnidMedCod,
ISNULL(V.ProdUnidMedPos, D.ProdUnidMedPos) ProdUnidMedPos,

V.ItNFValTotFinal ValTotVendasFinal,
V.ItNFValIpi ValTotVendasIPI,
(V.ItNFValTotFinal - V.ItNFValIpi) ValTotVendas,

D.ItNFValTotFinal ValTotDevolucoes,
V.ItNFQtd QtdVendas, D.ItNFQtd QtdDevolucoes,

-- O ERRO DÁ A PARTIR DAQUI - QUANDO EU INSIRO ESTAS CONDIÇÕES:

Column name or number of supplied values does not match table Definition.

  CASE WHEN UPPER(ISNULL(V.ProdUnidMedCod, D.ProdUnidMedCod)) = 'FD' THEN 
  V.ItNFValTotFinal ELSE 0 END ValTotVendasFinalFD,
  CASE WHEN UPPER(ISNULL(V.ProdUnidMedCod, D.ProdUnidMedCod)) = 'FD' THEN 
  V.ItNFValIpi ELSE 0 END ValTotVendasIPIFD,
  CASE WHEN UPPER(ISNULL(V.ProdUnidMedCod, D.ProdUnidMedCod)) = 'FD' THEN 
  (V.ItNFValTotFinal - V.ItNFValIpi) ELSE 0 END ValTotVendasFD,

  CASE WHEN UPPER(ISNULL(V.ProdUnidMedCod, D.ProdUnidMedCod)) = 'FD' THEN 
  D.ItNFValTotFinal ELSE 0 END ValTotDevolucoesFD,
  CASE WHEN UPPER(ISNULL(V.ProdUnidMedCod, D.ProdUnidMedCod)) = 'FD' THEN 
  V.ItNFQtd ELSE 0 END QtdVendasFD,
  CASE WHEN UPPER(ISNULL(V.ProdUnidMedCod, D.ProdUnidMedCod)) = 'FD' THEN 
  D.ItNFQtd ELSE 0 END QtdDevolucoesFD
  • 1

    The message is very objective, you are reporting a different number of columns than @RESULTADO has. How did you do the insert without setting fields, you need to pass values for all fields, in the order they are in the table, check the command to see what is missing or more

  • 1

    Even following the @Ricardopunctual comment, ideally you set the fields on INSERT to ensure that adding fields will not break the code

  • I’ve done it and it didn’t work

  • 1

    What @Sorack commented is very important, besides ensuring that you will not break the command if the table changes, would already help you telling if there are more or less columns in the command.

  • Do this and edit the question, just like @Ricardopunctual commented we can see which columns might be left

  • 1

    Good is just count the table fields and see if the values are correct. It can also be a comma problem or quotes that have in some field, this has happened to me and ended up breaking the value of a field in 2

  • I already packed the code. It was some INSERTS that I was doing after those lines there, the value was not coming out right

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.