CODEWITHSUNDEEP
sqlsql-server
Kirk1993
Kirk1993

Reputation: 31

UNION, INTERSECT or EXCEPT operator must have an equal number of expressions error while executing query

This query returns the number of tickets which have been open older than 72 hours. Need to understand the reason for this error and a possible solution to this post.Could anyone please help?

I get the following error while running this query:

Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Below given is the code:

 SELECT temp1.*, max(cast(Round(temp2.Plan_Val,0) as int)) as Plan_Val,max(cast(Round(temp2.actual,0) as int)) as actual_val FROM(
        SELECT t.[Problem_Type_Name(Parent)] ,t.[Problem_Type_Name(Child)], REPORT_DATE, CLOSE_DATE,[Assigned Tech],NAME , Job_ticket_id FROM (
        SELECT '%Tickets Open Older than 72 Business Hours' as [Problem_Type_Name(Parent)],[Problem_Type_Name(Child)], REPORT_DATE, CLOSE_DATE,[Assigned Tech],NAME , Job_ticket_id
        FROM TEMP_TICKET_STATE

        UNION

        SELECT '%Tickets Open Older than 72 Business Hours' as [Problem_Type_Name(Parent)],[Problem_Type_Name(Child)], REPORT_DATE, CLOSE_DATE,[Assigned Tech],NAME , Job_ticket_id
        FROM TEMP_TICKET_STATE
        WHERE [greater than 72 hours] <= 4320) t

        group by t.[Problem_Type_Name(Parent)] , t.[Problem_Type_Name(Child)],REPORT_DATE, CLOSE_DATE,[Assigned Tech],NAME , Job_ticket_id) temp1

        INNER JOIN


        (SELECT t.[Problem_Type_Name(Parent)],t.[Problem_Type_Name(Child)],case when sum(t.Total_tickets) = 0 then null else cast(Round((cast(sum(t.Total_tickets) as decimal(38,2))- cast(sum(t.Alarm_Val) as decimal(38,2)))/cast(sum(t.Total_tickets) as decimal(38,2))*100,0) as int) end as Plan_val, 0 as actual  FROM (
        SELECT '%Tickets Open Older than 72 Business Hours' as [Problem_Type_Name(Parent)] ,[Problem_Type_Name(Child)],0 as Alarm_Val, count(distinct [Job_ticket_id])/29 as Total_tickets 
        FROM TEMP_TICKET_STATE
        group by [Problem_Type_Name(Parent)],[Problem_Type_Name(Child)] 

        UNION

        SELECT '%Tickets Open Older than 72 Business Hours' as [Problem_Type_Name(Parent)] ,[Problem_Type_Name(Child)],count(distinct [Job_ticket_id])/29 as Alarm_Val, 0 as Total_tickets 
        FROM TEMP_TICKET_STATE
        WHERE [greater than 72 hours] <= 4320
        group by [Problem_Type_Name(Parent)],[Problem_Type_Name(Child)] ) t
        group by t.[Problem_Type_Name(Parent)],t.[Problem_Type_Name(Child)] 

        UNION
        SELECT t.[Problem_Type_Name(Parent)],0 AS plan_val, cast(Round((cast(sum(t.Total_tickets) as decimal(38,2))- cast(sum(t.Alarm_Val) as decimal(38,2)))/cast(sum(t.Total_tickets) as decimal(38,2))*100,0) as int) as actual  FROM (
        SELECT '%Tickets Open Older than 72 Business Hours' as [Problem_Type_Name(Parent)] ,[Problem_Type_Name(Child)],0 as Alarm_Val, count(distinct [Job_ticket_id])/4 as Total_tickets 
        FROM TEMP_TICKET_STATE_month
        group by [Problem_Type_Name(Parent)],[Problem_Type_Name(Child)] 

        UNION

        SELECT '%Tickets Open Older than 72 Business Hours' as [Problem_Type_Name(Parent)] ,[Problem_Type_Name(Child)],count(distinct [Job_ticket_id])/4 as Alarm_Val, 0 as Total_tickets 
        FROM TEMP_TICKET_STATE_month
        WHERE [greater than 72 hours] <= 4320
        group by [Problem_Type_Name(Parent)],[Problem_Type_Name(Child)] ) t

        group by t.[Problem_Type_Name(Parent)],t.[Problem_Type_Name(Child)]) temp2

        ON temp1.[Problem_Type_Name(Parent)]=temp2.[Problem_Type_Name(Parent)]
        Group by temp1.[Assigned Tech],temp1.Close_Date,temp1.Job_ticket_id,temp1.NAME,temp1.[Problem_Type_Name(Parent)],temp1.Report_Date,temp1.[Problem_Type_Name(Child)]

Upvotes: 1

Views: 9893

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

Here is why I suggest you might want to start over. You have the following code to produce "temp1"

SELECT t.[Problem_Type_Name(Parent)]
              , t.[Problem_Type_Name(Child)]
              , REPORT_DATE
              , CLOSE_DATE
              , [Assigned Tech]
              , NAME
              , Job_ticket_id
FROM   (SELECT '%Tickets Open Older than 72 Business Hours' AS [Problem_Type_Name(Parent)]
                      , [Problem_Type_Name(Child)]
                      , REPORT_DATE
                      , CLOSE_DATE
                      , [Assigned Tech]
                      , NAME
                      , Job_ticket_id
               FROM   TEMP_TICKET_STATE
               UNION
               SELECT '%Tickets Open Older than 72 Business Hours' AS [Problem_Type_Name(Parent)]
                      , [Problem_Type_Name(Child)]
                      , REPORT_DATE
                      , CLOSE_DATE
                      , [Assigned Tech]
                      , NAME
                      , Job_ticket_id
               FROM   TEMP_TICKET_STATE
               WHERE  [greater than 72 hours] <= 4320) t
       GROUP  BY t.[Problem_Type_Name(Parent)]
                 , t.[Problem_Type_Name(Child)]
                 , REPORT_DATE
                 , CLOSE_DATE
                 , [Assigned Tech]
                 , NAME
                 , Job_ticket_id) temp1

This entire thing can be reduced to a single, simple query like this.

SELECT '%Tickets Open Older than 72 Business Hours' AS [Problem_Type_Name(Parent)]
    , [Problem_Type_Name(Child)]
    , REPORT_DATE
    , CLOSE_DATE
    , [Assigned Tech]
    , NAME
    , Job_ticket_id
FROM   TEMP_TICKET_STATE temp1
GROUP BY temp1.[Problem_Type_Name(Child)]
    , temp1.REPORT_DATE
    , temp1.CLOSE_DATE
    , temp1.[Assigned Tech]
    , temp1.NAME
    , temp1.Job_ticket_id

You can do the same type of simplification on temp2. In general this is just way more complicated than it needs to be.

--EDIT--

Here is your second query with much of the noise removed so you can the skeleton.

SELECT t.[Problem_Type_Name(Parent)]
    , t.[Problem_Type_Name(Child)]
    , CASE WHEN Sum(t.Total_tickets) = 0 THEN NULL /*bunch of stuff here removed*/END AS Plan_val
    , 0 AS actual
FROM   
--[A bunch of stuff here]

UNION
SELECT t.[Problem_Type_Name(Parent)]
    --Where is [Problem_Type_Name(Child)]??
    , 0 AS plan_val
    , Cast(0/*bunch of stuff here removed*/ AS INT) AS actual
FROM 
(
---A bunch of stuff here]
) t
GROUP  BY t.[Problem_Type_Name(Parent)]
    , t.[Problem_Type_Name(Child)]) temp2

I would really recommend using sensible column names. Avoid using parenthesis, reserved words and other characters that are painful to work with. The column names are used for developers and if you want to provide more descriptive names or formatting that should be done in the application.

For example. Instead of "Problem_Type_Name(Parent)" how about something like ProblemTypeParent. It is still quite clear what that means but removes all the ugliness and the requirement to use [] around the name. Some people like underscores and others don't. I find I don't like the extra 2 keystrokes for little benefit. I do use them occasionally but not always. I tend to prefer Pascal case. But that stuff is all preference. Above all be consistent.

Upvotes: 3

Related Questions