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
, orINTERSECT
, 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 typevarchar(12)
by default.'A' + 'B'
has the data typevarchar(2)
because that is the sum length of the two data types being summed (the actual value is not relevant).n+1
is still anint
- In a recursive CTE, the data type must match exactly, so
'1'
is avarchar(1)
. If you specifyvarchar
without a length in aCAST
then you getvarchar(30)
, sotxt + ', ' + CAST(n+1 AS varchar)
isvarchar(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
SQL Query to Join Two Tables Based Off Closest Timestamp
Should a Composite Primary Key Be Clustered in SQL Server
How to Use SQL Wildcards in Linq to Entity Framework
Error (Ora-00923: from Keyword Not Found Where Expected)
How to Add New Column in Existing View in SQL-Server 2014 Using Alter
SQL - Pivot Table and Group by Not Working
Table Valued Function Where Did My Query Plan Go
There Are No Primary or Candidate Keys in the Referenced Table
Does Except Execute Faster Than a Join When the Table Columns Are the Same
Postgresql Count Number of Times Substring Occurs in Text
Limiting Returned Record from SQL Query in Oracle
Distance Between Two Coordinates, How to Simplify This And/Or Use a Different Technique
Oracle SQL: the Insert Query with Regexp_Substr Expression Is Very Long ( Split String )
How Does Order by Clause Works If Two Values Are Equal
SQL Statements with Equals VS In
Preserve Parent-Child Relationships When Copying Hierarchical Data