SQL Server Performance

SQL 2000 - error with UNION in dynamic SQL - Need this one quickly PLEASE (it's 2/16/2010)

Discussion in 'Getting Started' started by CarolM, Feb 17, 2010.

  1. CarolM New Member

    Hi there!

    My 5yrs of SQL experience was mostly doing selects and joins in order to build datamarts for easier reporting. My specialty is with FOCUS and Dialogue Manager where it's so easy to write dynamic code so that one program can do the work of many.

    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 the expected output just fine. If I take that output and put it in another query and execute, it works fine;
    If I remove the UNION and run as EXEC stmt, 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

    */
  2. Adriaan New Member

    Not sure I understand your question - there's too much detail, and not enough outline. Anyways ...
    If I understand correctly, you're building dynamic SQL - either this way:
    SELECT blabla1
    UNION
    SELECT blabla2
    --- which will return a single combined resultset from the two SELECT statements ---
    or this way:
    SELECT blabla1
    SELECT blabla2
    --- which will return a single resultset from the second SELECT statement only ---
  3. CarolM New Member

    Sorry for the detail, I didn't know what to post since I really don't know where my error is coming from and really do not know how to explain any simpler.
    To answer your question, your first response seems to be what I need; the ability to build a dynamic SQL stmt:
    SELECT blabla1
    UNION
    SELECT blabla2
    --- which will return a single combined resultset from the two SELECT statements ---

Share This Page