CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
toing_toing
toing_toing

Reputation: 2442

UNION, INTERSECT or EXCEPT combined query giving error in SQL server

I have the following query. i am running it in SQL server 2008 r2.

DECLARE @PartNo Char(22)
DECLARE @PartIssue AS CHAR(4)
DECLARE @Level AS INT
SELECT @PartIssue = drawissno, @PartNo = partnum, @Level=1 FROM partmaster  where partnum = 'AE40-0287810'

WITH Hierarchyct(BOMLevel,bmchild, bmparent, bmqty, leadtime, childissue,bmethod,rmethod, requiredqty) AS

      (SELECT     @Level AS BOMLevel,
                        @PartNo AS bomchild, 
                        cast('' AS CHAR(22)) AS bomparent,  
                        cast(1.0 AS FLOAT) AS bomqty, 
                        (SELECT stleadtim FROM stock WHERE stocknum = @PartNo) AS leadt,
                        @PartIssue as childissue, 
                        eccbom AS bmethod, 
                        eccroute as rmethod,
                        cast(1.0 AS FLOAT) AS requiredqty 
      FROM eccissue 
      WHERE eccpart = @PartNo AND eccissueno = @PartIssue 

      UNION ALL

      SELECT            (hierarchyct.BOMLevel + 1) AS BOMLevel,
                        bom.bomchild, 
                        bom.bomparent, 
                        bom.bomqty, 
                         (CASE WHEN LTRIM(RTRIM(bom.bomissue)) = 'CURR' THEN (SELECT drawissno FROM PartMaster WHERE PartNum = bom.bomchild) ELSE bom.bomissue END) AS childissue,
                        bom.baltmethod AS bmethod,    
                        (SELECT eccroute FROM eccissue WHERE eccissue.eccissueno = (CASE WHEN LTRIM(RTRIM(bom.bomissue)) = 'CURR' THEN (SELECT drawissno FROM PartMaster WHERE PartNum = bom.bomchild) ELSE bom.bomissue END) AND eccissue.eccpart = bom.bomchild ) as rmethod,
                        (bom.bomqty * hierarchyct.requiredqty) AS requiredqty
      FROM         bom  INNER JOIN hierarchyct ON bom.bomparent = hierarchyct.bmchild  
      WHERE bom.baltmethod =(SELECT eccbom FROM eccissue WHERE eccissue.eccpart = hierarchyct.bmchild AND eccissue.eccissueno = hierarchyct.childissue )
      )
SELECT      hierarchyct.*
      FROM  hierarchyct

When I run it I get a message saying:

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

I have checked the syntax and it al seems fine, but this issue doesn't get solved. Any ideas on what this issue is?

Thanks in advance.

Upvotes: 1

Views: 807

Answers (2)

Ashish Kumar
Ashish Kumar

Reputation: 157

second part of your query missing [leadt] column.

Upvotes: 1

A Hocevar
A Hocevar

Reputation: 726

Your are missing the equivalent of

(SELECT stleadtim FROM stock WHERE stocknum = @PartNo) AS leadt,

in your second union member

Upvotes: 1

Related Questions