Error - UNION operator must have an equal number of expressions when using CTE for recursive selection

Question

At this moment I have a table tblLocation with columns ID  Location  PartOfID   The table is recursively connected to itself  PartOfID - gt  ID  My goal is to have a select output as followed    gt  France  gt  Paris  gt  AnyCity  gt    Explanation  AnyCity is located in Paris  Paris is located in France   My solution that I found until now was this     with q as   select ID Location PartOf LOC id from tblLocatie t where t ID   1 -- 1 represents an example union all select t Location     gt   from tblLocation t inner join q parent on parent ID   t LOC PartOf ID   select   from q   Unfortunately I get the following error      All queries combined using a UNION  INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists    If you have any idea how I could fix my output it would be great

User · Answer

The problem lays here   --This result set has 3 columns select LOC id LOC locatie LOC deelVan LOC id from tblLocatie t where t LOC id   1 -- 1 represents an example  union all  --This result set has 1 columns    select t LOC locatie     gt   from tblLocatie t inner join q parent on parent LOC id   t LOC deelVan LOC id   In order to use union or union all number of columns and their types should be identical cross all result sets   I guess you should just add the column LOC deelVan LOC id to your second result set

User · Answer

Then number of columns must match between both parts of the union   In order to build the full path  you need to  aggregate  all values of the Location column  You still need to select the id and other columns inside the CTE in order to be able to join properly  You get  rid  of them by simply not selecting them in the outer select   with q as       select ID  PartOf LOC id  Location     gt      Location as path    from tblLocation     where ID   1      union all     select child ID  child PartOf LOC id  Location  parent path      gt      child Location     from tblLocation child      join q parent on parent ID   t LOC PartOf ID   select path from q

User · Answer

The second result set have only one column but it should have 3 columns for it to be contented to the first result set    columns must match when you use UNION   Try to add ID as first column and PartOf LOC id to your result set  so you can do the UNION     WITH    q AS   SELECT   ID                       Location                       PartOf LOC id            FROM     tblLocation t            WHERE    t ID   1 -- 1 represents an example            UNION ALL            SELECT   t ID                       parent Location     gt     t Location                       t PartOf LOC id            FROM     tblLocation t                     INNER JOIN q parent ON parent ID   t LOC PartOf ID            SELECT    FROM    q

User · Answer

You could use a recursive scalar function -  set nocount on  create table location       id int      name varchar 50       parent int   insert into location values      1  france  null        2  paris  1        3  belleville  2        4  lyon  1        5  vaise  4        6  united kingdom  null        7  england  6        8  manchester  7        9  fallowfield  8        10  withington  8  go create function dbo breadcrumb  child int  returns varchar 1024  as begin     declare  returnValue varchar 1024         declare  parent int     select  returnValue     gt    name  parent parent     from location     where id  child     if  parent is not null         set  returnValue dbo breadcrumb  parent   returnValue     return  returnValue end go  declare  location int 1 while  location lt  10 begin     print dbo breadcrumb  location     gt       set  location  1 end   produces -    gt  france  gt    gt  france  gt  paris  gt    gt  france  gt  paris  gt  belleville  gt    gt  france  gt  lyon  gt    gt  france  gt  lyon  gt  vaise  gt    gt  united kingdom  gt    gt  united kingdom  gt  england  gt    gt  united kingdom  gt  england  gt  manchester  gt    gt  united kingdom  gt  england  gt  manchester  gt  fallowfield  gt    gt  united kingdom  gt  england  gt  manchester  gt  withington  gt

User · Answer

Although this an old post  I am sharing another working example   quot COLUMN COUNT AS WELL AS EACH COLUMN DATATYPE MUST MATCH WHEN  UNION  OR  UNION ALL  IS USED quot  Let us take an example  1  In SQL if we write - SELECT  column1    column2   NOTE  remember to specify names in quotes  In a result set  it will display empty columns with two headers - column1 and column2 2  I share one simple instance I came across  I had seven columns with few different datatypes in SQL  I e  uniqueidentifier  datetime  nvarchar My task was to retrieve comma separated result set with column header  So that when I export the data to CSV I have comma separated rows with first row as header and has respective column names  SELECT CONVERT NVARCHAR 36    Event ID              Last Name             First Name             Middle Name            CONVERT NVARCHAR 36    Document Type              Event Type            CONVERT VARCHAR 23    Last Updated   126   UNION ALL  SELECT CONVERT NVARCHAR 36   inspectionid                   individuallastname                  individualfirstname                  individualmiddlename                 CONVERT NVARCHAR 36   documenttype                    I                  CONVERT VARCHAR 23   modifiedon  126  FROM Inspection  Above  columns  inspectionid   amp   documenttype  has uniqueidentifer datatype and so applied CONVERT NVARCHAR 36    column  modifiedon  is datetime and so applied CONVERT NVARCHAR 23    modifiedon   126   Parallel to above 2nd SELECT query matched 1st SELECT query as per datatype of each column

[sql] Error - "UNION operator must have an equal number of expressions" when using CTE for recursive selection

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to recursive-query