cmallain
Starting Member
USA
5 Posts 
Posted  02/17/2010 : 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' ,''1ALLIANCE 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' ,''2MKTSEG TOTALS'' as Comment1 ' when @loopvar1 = 3 then N' ,''3GROUP TOTALS'' as Comment1 ' when @loopvar1 = 4 then N' ,''4BRANCH 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 ,'1ALLIANCE 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 ,'2MKTSEG 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 ,'3GROUP 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 ,'4BRANCH 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 */ 
