Error - "Union Operator Must Have an Equal Number of Expressions" When Using Cte for Recursive Selection

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

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.

All queries combined using a UNION operator must have an equal number of expressions

Number of columns are 16 in below query Select TARGETFEESBILLED as CORPG, 0 as FUNDS, 0 as EUCOM, 0 as INSUR, 0 as IPIT, 0 as LITGE, 0 as FINR,
0 as CNSTR, 0 as PLENV, 0 as INSOL, 0 as EMPLO, 0 as HELSC, 0 as BANKG, 0 as CONST, 0 as COEN, 0 as CLIM
from ...

But other queries with unions are having 17 columns.

Error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists

There is one less column in the 'Acero' part (as Tony commented). Should be:

SELECT 
TD = 'Acero', '',
TD = CONVERT(int,SUM(totalprice)/b.xchgrate/1000),'',

However, when you fix this, you'll face another problem: all the columns in CTE must have a column name, and when you fix even that, you'll have the error that they are all called the same, and it's not allowed.

All queries combined using a UNION, INTERSECT or EXCEPT

You are missing a comma in the second part of the union statement, therefore the column size is different.

Try this:

select OpportunityOwner, RecordType, ForecastCategory, CaseSafeOppID, date, [Fiscal Qtr], CloseDate,
MasterLicenseAmt, TrueMCVEst, MasterLicenseAmt + TrueMCVEst "Total $"
from ds_adhoc_sops.Pipe.DailyLicMCV

union

select OpportunityOwner, RecordType, ForecastCategory, CaseSafeOppID, date, [Fiscal Qtr], CloseDate,
MasterLicenseAmt, TrueMCVEst, MasterLicenseAmt + TrueMCVEst "Total $"
from ds_adhoc_sops.Pipe.QTRLicMcvWL
;

TSQL CTE error ''Types don't match between the anchor and the recursive part

The info you want is all in the documentation:

When concatenating two char, varchar, binary, or varbinary expressions, the length of the resulting expression is the sum of the lengths of the two source expressions, up to 8,000 bytes.

snip ...

When comparing two expressions of the same data type but different lengths by using UNION, EXCEPT, or INTERSECT, the resulting length is the longer of the two expressions.

The precision and scale of the numeric data types besides decimal are fixed. When an arithmetic operator has two expressions of the same type, the result has the same data type with the precision and scale defined for that type.

However, a recursive CTE is not the same as a normal UNION ALL:

The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

So in answer to your questions:

  • 'Hello world!' has the data type varchar(12) by default.
  • 'A' + 'B' has the data type varchar(2) because that is the sum length of the two data types being summed (the actual value is not relevant).
  • n+1 is still an int
  • In a recursive CTE, the data type must match exactly, so '1' is a varchar(1). If you specify varchar without a length in a CAST then you get varchar(30), so txt + ', ' + CAST(n+1 AS varchar) is varchar(33).

When you cast the anchor part to varchar(max), that automatically means the recursive part will be varchar(max) also. You don't need to cast to max, you could also cast the recursive part directly to varchar(30) for example:

WITH CTE(n, txt) AS
(
--SELECT 1, '1' --This does not work.
SELECT 1, CAST('1' AS varchar(30)) --This does work.
--SELECT 1, CAST('1' AS varchar(1000)) --This does not work.
UNION ALL
SELECT
n+1,
CAST(CONCAT(txt, ', ', n+1) AS varchar(30))
FROM
CTE
WHERE
n < 10
)
SELECT *
FROM CTE

db<>fiddle

Formatting the output using select statement

So here's what's happening:

The error you're getting (All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.) is because there are more columns in one select than the other.

You could use the same blank columns in your first query as there are in [My Table] which would make your query look like:

SELECT 'MY OUTPUT' , '', '' ,'', '' --(no. of columns should match those in MY TABLE)
UNION
SELECT * FROM [MY TABLE]

I'm guessing you want an excel style cell merge which is not possible as the output of a select query unfortunately.

Why cte for returning months doesn't work

The second parameter of DATENAME is a date not an int, so create a date using the int value...

with cte_month(n, monthname)
as
(
select 1, datename(m,0)
union all
select n+1,datename(m,DATEFROMPARTS(2021,n+1,1))
from cte_month
where n < 12
)
select * from cte_month;

Using datename(m,0) works in the initial select because it is the equivalent of datename(m,cast(0 as datetime)) which will return "January" as the base datetime in SQL Server is 1900-01-01 (i.e. a January datetime).



Related Topics



Leave a reply



Submit