Error when modifying column of a View with calculation

Question

Error when modifying column of a View with calculation

Navigation

#1 by (1 votes)

1

I need to insert a calculation column in this view below, but the message

Msg 205, Level 16, State 1, Procedure ConsolidatedStudyBaseSifix, Line 4 [Batch Start Line 9] All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I know the error is in UNION ALL , but in case I did not want to insert more columns in the "percentage."

How can I resolve this issue?

Follow the code below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[ConsolidadoBaseSifis] AS

with contabilidade as ( 

SELECT iUnidade,
COUNT(iUnidade) AS iQuantidade,
COUNT(vSimNao)  AS iSimNaoRespondido,
COUNT(vResp)  AS iJustificRespondidas
FROM  [dbo].[BaseSifis]
GROUP BY iUnidade
),

percentual as(

SELECT
(((iSimNaoRespondido+iJustificRespondidas)/iUnidade)*100) as iPorc
from contabilidade

)

select * FROM contabilidade 

Union ALL

select * FROM percentual

In short, it looks like this:

AndIwouldlikeittolooklikethis:

The original script is this:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[ConsolidadoBaseSifis] AS
    with contabilidade as ( 

    SELECT iUnidade,
    COUNT(iUnidade) AS iQuantidade,
    COUNT(vSimNao)  AS iSimNaoRespondido,
    COUNT(vResp)  AS iJustificRespondidas
    FROM  [dbo].[EstudoBaseSifis]
    GROUP BY iUnidade
    )

    select * FROM contabilidade 
GO

sql sql-server view

asked by anonymous 06.09.2018 / 17:35

1 answer

Export password-protected csv from a DB Sum (SUM) of a column with irregular formatting

score 1 · Accepted Answer

Try it this way:

ALTER VIEW ConsolidadoBaseSifis
AS
    WITH contabilidade  AS 
    ( 
        SELECT      iUnidade
                ,   COUNT(iUnidade) AS iQuantidade
                ,   COUNT(vSimNao)  AS iSimNaoRespondido
                ,   COUNT(vResp)    AS iJustificRespondidas
        FROM        BaseSifis
        GROUP BY    iUnidade
    ),  percentual      AS
    (
        SELECT  iUnidade
            ,   iQuantidade
            ,   iSimNaoRespondido
            ,   iJustificRespondidas
            ,   (((iSimNaoRespondido + iJustificRespondidas) / iUnidade) * 100) AS iPorc
        FROM    contabilidade
    )
    SELECT  *
    FROM    percentual
GO

If you do not want to add more columns, in addition to iPorc and of course, iUnidade (otherwise you will not be able to connect the two tables), you can do this:

ALTER VIEW ConsolidadoBaseSifis
AS
    WITH contabilidade  AS 
    ( 
        SELECT      iUnidade
                ,   COUNT(iUnidade) AS iQuantidade
                ,   COUNT(vSimNao)  AS iSimNaoRespondido
                ,   COUNT(vResp)    AS iJustificRespondidas
        FROM        BaseSifis
        GROUP BY    iUnidade
    ),  percentual      AS
    (
        SELECT  iUnidade
            ,   (((iSimNaoRespondido + iJustificRespondidas) / iUnidade) * 100) AS iPorc
        FROM    contabilidade
    )
    SELECT      iUnidade
            ,   iQuantidade             = SUM(iQuantidade)
            ,   iSimNaoRespondido       = SUM(iSimNaoRespondido)
            ,   iJustificRespondidas    = SUM(iJustificRespondidas)
            ,   iPorc                   = SUM(iPorc)
    FROM        (
                    SELECT  iUnidade
                        ,   iQuantidade
                        ,   iSimNaoRespondido
                        ,   iJustificRespondidas
                        ,   iPorc = 0
                    FROM    contabilidade 
                    UNION ALL
                    SELECT  iUnidade
                        ,   iQuantidade             = 0
                        ,   iSimNaoRespondido       = 0
                        ,   iJustificRespondidas    = 0
                        ,   iPorc
                    FROM    percentual 
                ) X
    GROUP BY    iUnidade
GO

But it seems to me a bit too laborious and not very when what we want is basically the result of the percentual table.