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
The message is very objective, you are reporting a different number of columns than
@RESULTADO
has. How did you do theinsert
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– Ricardo Pontual
Even following the @Ricardopunctual comment, ideally you set the fields on
INSERT
to ensure that adding fields will not break the code– Sorack
I’ve done it and it didn’t work
– Felipe Michael da Fonseca
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.
– Ricardo Pontual
Do this and edit the question, just like @Ricardopunctual commented we can see which columns might be left
– Sorack
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
– Ricardo Pontual
I already packed the code. It was some INSERTS that I was doing after those lines there, the value was not coming out right
– Felipe Michael da Fonseca