SQL 2000 - error with UNION in dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL 2000 - error with UNION in dynamic SQL

Author

Topic

cmallain
Starting Member

5 Posts

Posted - 2010-02-17 : 10:12:12

Hi there!

My current issue is something that I wrote that will sum various counts for 4 different categories/levels of totals. Each "level' requires an additional "group by" object.

When I use the PRINT command, it produces just fine. If I take that output and put it in another query and execute, it works fine; If I remove the UNION, it

works fine but groups everything where I need this to be put as one output, eventually to be stored into a table which is phase2 once I can get this to work.

HERE IS MY CODE:

--declare and set initial value of loop variable
DECLARE @loopvar1 int
SET @loopvar1 = 1

--stmt1 is the initial select statement
DECLARE @sqlSTMT1 NVARCHAR(4000)
SET @sqlSTMT1 = N'SELECT run_date
,end_date_requested
'

--alliance does NOT change so not bothering to add to loop and take up unnecessary runtime
DECLARE @fld_alliance NVARCHAR(4000) ----having blank will advance to next line to keep sql neat
SET @fld_alliance =N'
,cast(Alliance as varchar(20)) as Alliance'

DECLARE @fld_allianceNM NVARCHAR(4000)
SET @fld_allianceNM =N'
,AllianceNM'

--the rest that would go in this order are within the loop.
DECLARE @sqlSTMT2 NVARCHAR(4000)
SET @sqlSTMT2 = N'
--memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt

--webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg

--hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA

--EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75

--how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25

from ##meMemCnts1 d1 --dont forget to modify first program to put into actual table

group by run_date
,end_date_requested

--alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM
'

WHILE @loopvar1 < 5
BEGIN
DECLARE @sqlSELECT NVARCHAR(2000)
SET @sqlSELECT = case
when @loopvar1 = 1
then @sqlStmt1
else N' UNION '+ @sqlSTMT1
end

DECLARE @FLD_comment NVARCHAR(4000)
SET @FLD_comment = case
when @loopvar1 = 1
then N'
,''1-ALLIANCE TOTALS'' as Comment1 ' --keeping this like this helps to make the sql neater to look at for fixing/debug
when @loopvar1 = 2 --I guess it uses actual spacing in it's interpretation
then N'
,''2-MKTSEG TOTALS'' as Comment1 '
when @loopvar1 = 3
then N'
,''3-GROUP TOTALS'' as Comment1 '
when @loopvar1 = 4
then N'
,''4-BRANCH TOTALS'' as Comment1 '
else ' ' --null
end

--alliance does not change so didn't bother putting in loop
--starts mktseg here
DECLARE @FLD_mktseg NVARCHAR(4000)
SET @FLD_mktseg = case
when @loopvar1 = 1
then N'
,cast('' '' as varchar(20)) as MktSeg'
when @loopvar1 between 2 and 4
then N'
,cast(d1.MktSeg as varchar(20)) as MktSeg'
else ' '
end

DECLARE @FLD_mktsegNM NVARCHAR(4000)
SET @FLD_mktsegNM = case
when @loopvar1 = 1
then N'
,'' '' as MktSegNM'
when @loopvar1 between 2 and 4
then N'
,d1.MktSegNM as MktSegNM'
else ' '
end
--groupid here
DECLARE @FLD_group NVARCHAR(4000)
SET @FLD_group = case
when @loopvar1 <= 2
then N'
,cast('' '' as varchar(20)) as GrpID'
when @loopvar1 between 3 and 4
then N'
,cast(d1.grpID as varchar(20)) as GrpID'
else ' '
end

DECLARE @FLD_groupNM NVARCHAR(4000)
SET @FLD_groupNM = case
when @loopvar1 <= 2
then N'
,'' '' as GrpNM'
when @loopvar1 between 3 and 4
then N'
,d1.grpNM as GrpNM'
else ' '
end

--branch
DECLARE @FLD_branch NVARCHAR(4000)
SET @FLD_branch = case
when @loopvar1 <= 3
then N'
,cast('' '' as varchar(20)) as BRANCH'
when @loopvar1 = 4
then N'
,cast(d1.branch as varchar(20)) as BRANCH'
else ' '
end

DECLARE @FLD_branchNM NVARCHAR(4000)
SET @FLD_branchNM = case
when @loopvar1 <= 3
then N'
,'' '' as BranchNM'
when @loopvar1 = 4
then N'
,d1.branchNM '
else ' '
end

--GROUP BYs START HERE
--Alliance is always avail so here is MARKET
DECLARE @GROUPBY_mkt NVARCHAR(4000)
SET @GROUPBY_MKT = case
when @loopvar1 >= 2 --between 2 and 3
then N'
,d1.MktSeg'
else ' '
end
DECLARE @GROUPBY_mktNM NVARCHAR(4000)
SET @GROUPBY_MKTNM = case
when @loopvar1 >= 2 --between 2 and 3
then N'
,d1.MktSegNM'
else ' '
end
-- GROUP
DECLARE @GROUPBY_group NVARCHAR(4000)
SET @GROUPBY_GROUP = case
when @loopvar1 >= 3 --between 3 and 4
then N'
,d1.GrpID'
else ' '
end
DECLARE @GROUPBY_groupNM NVARCHAR(4000)
SET @GROUPBY_GROUPNM = case
when @loopvar1 >= 3 --between 3 and 4
then N'
,d1.GrpNM'
else ' '
end

-- BRANCH
DECLARE @GROUPBY_branch NVARCHAR(4000)
SET @GROUPBY_branch = case
when @loopvar1 = 4
then N'
,d1.branch'
else ' '
end

DECLARE @GROUPBY_branchNM NVARCHAR(4000)
-- union aLL --trying it here also resulted in the same error why?.
SET @GROUPBY_branchNM = case
when @loopvar1 = 4
then N'
,d1.branchNM
ORDER BY run_date
,end_date_requested
,comment1
,alliance
,mktseg
,grpID
,branch
'
else ' '
end

DECLARE @UNIONstmt NVARCHAR(4000)
SET @UNIONstmt = case
when @loopvar1 < 4
then N'
UNION ALL'
ELSE ' '
END

--now put it all together into another var
declare @sqlRunIt nvarchar(4000)
set @sqlRunIt =
--@sqlSELECT --this has union preceding select --> notes work either on "exec"
@sqlstmt1 --initial attempt which I swear worked last thurs when citrix was having issues at work....
+ @fld_comment
+ @fld_alliance
+ @fld_alliancenm
+ @fld_mktseg
+ @fld_mktsegNM
+ @fld_group
+ @fld_groupnm
+ @fld_branch
+ @fld_branchnm
+ @sqlstmt2
+ @groupby_mkt
+ @GROUPBY_MKTNM
+ @groupby_group
+ @groupby_groupNM
+ @groupby_branch
+ @groupby_branchNM
+ @UNIONstmt --if I comment, it works fine but as 4 queries. Keeping seems to process all but only displays branch with those errs

print @sqlRunIt
--exec sp_executesql @sqlRunIt

set @loopvar1 = @loopvar1 + 1
end --program and loop end here.

--final step, insert the output above into a table

/*
--here are my errors when EXEC runs with @unionstmt active:
Server: Msg 170, Level 15, State 1, Line 61
Line 61: Incorrect syntax near 'ALL'.
Server: Msg 170, Level 15, State 1, Line 63
Line 63: Incorrect syntax near 'ALL'.
Server: Msg 170, Level 15, State 1, Line 65
Line 65: Incorrect syntax near 'ALL'.

(221 row(s) affected)
*/

--here is the code after PRINT runs, which, if I run this, it runs fine/*
SELECT run_date
,end_date_requested

,'1-ALLIANCE TOTALS' as Comment1
,cast(Alliance as varchar(20)) as Alliance
,AllianceNM
,cast(' ' as varchar(20)) as MktSeg
,' ' as MktSegNM
,cast(' ' as varchar(20)) as GrpID
,' ' as GrpNM
,cast(' ' as varchar(20)) as BRANCH
,' ' as BranchNM
--memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt

--webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg

--hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA

--EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75

--how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25

from ##meMemCnts1 d1 --dont forget to modify first program to put into actual table

group by run_date
,end_date_requested

--alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM

UNION ALL
SELECT run_date
,end_date_requested

,'2-MKTSEG TOTALS' as Comment1
,cast(Alliance as varchar(20)) as Alliance
,AllianceNM
,cast(d1.MktSeg as varchar(20)) as MktSeg
,d1.MktSegNM as MktSegNM
,cast(' ' as varchar(20)) as GrpID
,' ' as GrpNM
,cast(' ' as varchar(20)) as BRANCH
,' ' as BranchNM
--memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt

--webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg

--hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA

--EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75

--how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25

from ##meMemCnts1 d1 --dont forget to modify first program to put into actual table

group by run_date
,end_date_requested

--alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM

,d1.MktSeg
,d1.MktSegNM
UNION ALL
SELECT run_date
,end_date_requested

,'3-GROUP TOTALS' as Comment1
,cast(Alliance as varchar(20)) as Alliance
,AllianceNM
,cast(d1.MktSeg as varchar(20)) as MktSeg
,d1.MktSegNM as MktSegNM
,cast(d1.grpID as varchar(20)) as GrpID
,d1.grpNM as GrpNM
,cast(' ' as varchar(20)) as BRANCH
,' ' as BranchNM
--memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt

--webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg

--hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA

--EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75

--how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25

from ##meMemCnts1 d1 --dont forget to modify first program to put into actual table

group by run_date
,end_date_requested

--alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM

,d1.MktSeg
,d1.MktSegNM
,d1.GrpID
,d1.GrpNM
UNION ALL
SELECT run_date
,end_date_requested

,'4-BRANCH TOTALS' as Comment1
,cast(Alliance as varchar(20)) as Alliance
,AllianceNM
,cast(d1.MktSeg as varchar(20)) as MktSeg
,d1.MktSegNM as MktSegNM
,cast(d1.grpID as varchar(20)) as GrpID
,d1.grpNM as GrpNM
,cast(d1.branch as varchar(20)) as BRANCH
,d1.branchNM
--memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt

--webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg

--hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA

--EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75

--how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25

from ##meMemCnts1 d1 --dont forget to modify first program to put into actual table

group by run_date
,end_date_requested

--alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM

,d1.MktSeg
,d1.MktSegNM
,d1.GrpID
,d1.GrpNM
,d1.branch
,d1.branchNM
ORDER BY run_date
,end_date_requested
,comment1
,alliance
,mktseg
,grpID
,branch

*/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 10:22:39

rather than posting entire query it would be better if you post some data and query which you think is not working and then explain what you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources