locked
SELECT Statements Produces Incorrect Results RRS feed

  • Question

  • I discovered that all of the values in one calculated column (AVAIL) of the Production Query I am developing are incorrect, while all of the other columns are producing correct results and share a similar structure.  Running the exact same SELECT statement, with most of the other fields and all the joins removed in a Test Query produce the correct values.  Since my account hasn't been "verified" (even though I have entered the code I received through my email I cannot upload the nifty picture illustrating this issue.

    The queries are being run on a SQL Server 2008 system.  The Production Query uses Grouping Sets to calculate totals and the outermost query uses the row number to assign names to the NULL values produced by the Grouping Sets.  Note that the innermost query does not produce correct values when isolated from the outmost query:

    SELECT
    
    CASE WHEN itrs_unit_ind.uic IS NULL THEN 
    CASE 
    WHEN row_no = 1 THEN 'W70U' 
    WHEN row_no = 2 THEN 'BDEHQ'
    WHEN row_no = 3 THEN 'W71S'
    WHEN row_no = 4 THEN 'W74V'
    WHEN row_no = 5 THEN 'W75Z'
    WHEN row_no = 6 THEN 'W8CW'
    WHEN row_no = 7 THEN 'W8CX'
    WHEN row_no = 8 THEN 'W8DA'
    WHEN row_no = 9 THEN 'W71J' END
    ELSE itrs_unit_ind.uic END AS 'huic',
    itrs_unit_ind.*
    
    FROM (
     
    SELECT 
    
    SUM(CASE WHEN g18ext.overall_avail = 'N' THEN 1 ELSE 0 END) AS 'notavail',
    SUM(CASE WHEN g18ext.overall_avail = 'Y' THEN 1 ELSE 0 END) AS 'avail', 
    SUM(CASE WHEN ldr_book.due_eval = 'OD' AND LEFT(remarks.eval_rmk2, 1) <> '9' THEN 1 ELSE 0 END) AS 'evals_due',
    SUM(CASE WHEN ldr_book.mos_have <> 'N' THEN 1 ELSE 0 END) AS 'mos_clr_have',
    SUM(CASE WHEN ldr_book.mos_clr <> 'N' THEN 1 ELSE 0 END) AS 'mos_clr_req',
    SUM(CASE WHEN ldr_book.mpc = 'E' AND ldr_book.due_eval = 'OD' AND LEFT(remarks.eval_rmk2, 1) <> '9' THEN 1 ELSE 0 END) AS 'ncoers_due',
    SUM(CASE WHEN ldr_book.mpc = 'E' THEN 1 ELSE 0 END) AS 'ncos',
    SUM(CASE WHEN (ldr_book.mos_clr <> 'N' AND ldr_book.mos_have = 'N') OR (ldr_book.clr_req <> 'N' AND ldr_book.clr_have = 'N') THEN 1 ELSE 0 END) AS 'need_clr',
    SUM(CASE WHEN (ldr_book.depl <> 'YY' AND ldr_book.depl <> 'B1' AND ldr_book.depl <> 'B2' AND ldr_book.depl <> 'R1') THEN 1 ELSE 0 END) AS 'non_depl',
    SUM(CASE WHEN ldr_book.mpc <> 'E' AND ldr_book.due_eval = 'OD' AND LEFT(remarks.eval_rmk2, 1) <> '9' THEN 1 ELSE 0 END) AS 'oers_due',
    SUM(CASE WHEN ldr_book.mpc <> 'E' THEN 1 ELSE 0 END) AS 'officers', 
    SUM(CASE WHEN ldr_book.clr_have <> 'N' THEN 1 ELSE 0 END) AS 'posn_clr_have',
    SUM(CASE WHEN ldr_book.clr_req <> 'N' THEN 1 ELSE 0 END) AS 'posn_clr_req',
    SUM(CASE WHEN ldr_book.apft_ind = 'F' THEN 1 ELSE 0 END) AS 'apft_fail',
    SUM(CASE WHEN DATEADD(year, 1, CONVERT(datetime, ldr_book.apft_dt)) < GETDATE() OR ldr_book.apft_dt IS NULL THEN 1 ELSE 0 END) AS 'apft_over12',
    SUM(CASE WHEN ((cdrcrs.grade IN('MAJ', 'LTC', 'COL', 'CSM') AND cdrcrs. course_nbr = '921-933' AND cdrcrs.bdebncdr = 'G') OR (cdrcrs.grade IN ('2LT', '1LT', 'CPT', '1SG', 'MSG', 'SFC') AND cdrcrs. course_nbr = '921-932' AND cdrcrs. codetcdr = 'G')) THEN 1 ELSE 0 END) AS 'pccgrad', 
    SUM(CASE WHEN ((cdrcrs.grade IN('MAJ', 'LTC', 'COL', 'CSM') AND cdrcrs. course_nbr = '921-933' AND (cdrcrs.problem <> '' OR cdrcrs.problem IS NOT NULL )) OR (cdrcrs.grade IN ('2LT', '1LT', 'CPT', '1SG', 'MSG', 'SFC') AND cdrcrs.course_nbr = '921-932' AND (cdrcrs.problem <> '' OR cdrcrs.problem IS NOT NULL ))) THEN 1 ELSE 0 END) AS 'pccreq', 
    SUM(CASE WHEN ((cdrcrs.grade IN('MAJ', 'LTC', 'COL', 'CSM') AND cdrcrs. course_nbr = '921-933' AND cdrcrs.bdebncdr = 'S') OR ((cdrcrs.grade IN ('2LT', '1LT', 'CPT', '1SG', 'MSG', 'SFC')) AND cdrcrs. course_nbr = '921-932' AND cdrcrs. codetcdr = 'S')) THEN 1 ELSE 0 END) AS 'pccsched', 
    SUM(CASE WHEN ldr_book.grade = '1LT' THEN 1 ELSE 0 END) AS 'asgn_1lt',
    SUM(CASE WHEN ldr_book.grade = '2LT' THEN 1 ELSE 0 END) AS 'asgn_2lt',
    SUM(CASE WHEN ldr_book.grade = 'CPT' THEN 1 ELSE 0 END) AS 'asgn_cpt',
    SUM(CASE WHEN ldr_book.grade = 'MAJ' THEN 1 ELSE 0 END) AS 'asgn_maj',
    SUM(CASE WHEN ldr_book.grade = 'LTC' THEN 1 ELSE 0 END) AS 'asgn_ltc',
    SUM(CASE WHEN ldr_book.grade = 'COL' THEN 1 ELSE 0 END) AS 'asgn_col',
    SUM(CASE WHEN ldr_book.grade = 'WO1' THEN 1 ELSE 0 END) AS 'asgn_wo1',
    SUM(CASE WHEN ldr_book.grade = 'WO1' THEN 1 ELSE 0 END) AS 'asgn_wo2',
    SUM(CASE WHEN ldr_book.grade = 'WO1' THEN 1 ELSE 0 END) AS 'asgn_wo3',
    SUM(CASE WHEN ldr_book.grade = 'WO1' THEN 1 ELSE 0 END) AS 'asgn_wo4',
    SUM(CASE WHEN ldr_book.grade = 'WO1' THEN 1 ELSE 0 END) AS 'asgn_wo5',
    SUM(CASE WHEN ldr_book.grade = 'PV1' THEN 1 ELSE 0 END) AS 'asgn_pv1',
    SUM(CASE WHEN ldr_book.grade = 'PV2' THEN 1 ELSE 0 END) AS 'asgn_pv2',
    SUM(CASE WHEN ldr_book.grade = 'CPL' THEN 1 ELSE 0 END) AS 'asgn_cpl',
    SUM(CASE WHEN ldr_book.grade = 'PFC' THEN 1 ELSE 0 END) AS 'asgn_pfc',
    SUM(CASE WHEN ldr_book.grade = 'SPC' THEN 1 ELSE 0 END) AS 'asgn_spc',
    SUM(CASE WHEN ldr_book.grade = 'SGT' THEN 1 ELSE 0 END) AS 'asgn_sgt',
    SUM(CASE WHEN ldr_book.grade = 'SSG' THEN 1 ELSE 0 END) AS 'asgn_ssg',
    SUM(CASE WHEN ldr_book.grade = 'SFC' THEN 1 ELSE 0 END) AS 'asgn_sfc',
    SUM(CASE WHEN ldr_book.grade = 'MSG' THEN 1 ELSE 0 END) AS 'asgn_msg',
    SUM(CASE WHEN ldr_book.grade = '1SG' THEN 1 ELSE 0 END) AS 'asgn_1sg',
    SUM(CASE WHEN ldr_book.grade = 'SGM' THEN 1 ELSE 0 END) AS 'asgn_sgm',
    SUM(CASE WHEN ldr_book.grade = 'CSM' THEN 1 ELSE 0 END) AS 'asgn_csm',
    ROW_NUMBER() OVER (ORDER BY ldr_book.uic, LEFT(ldr_book.uic, 4)) AS 'row_no',
    ldr_book.uic
    
    FROM ldr_book
    
    LEFT OUTER JOIN cdrcrs ON cdrcrs.idunique = ldr_book.idunique
    LEFT OUTER JOIN g18ext ON g18ext.idunique = ldr_book.idunique
    LEFT OUTER JOIN remarks ON remarks.idunique = ldr_book.idunique
    
    /* Replace value within single quotes with brigade UIC */
    WHERE ldr_book.bdeuic = 'W71J'
    
    GROUP BY 
    
    GROUPING SETS (ldr_book.uic, LEFT(ldr_book.uic, 4), ())
     
    ) AS itrs_unit_ind
    

    The Test Query isolates the offending values and removes the associated JOINS and produces the correct values:

    SELECT
    
    SUM(CASE WHEN g18ext.overall_avail = 'N' THEN 1 ELSE 0 END) AS 'notavail',
    SUM(CASE WHEN g18ext.overall_avail = 'Y' THEN 1 ELSE 0 END) AS 'avail', 
    ROW_NUMBER() OVER (ORDER BY g18ext.uic, LEFT(g18ext.uic, 4)) AS 'row_no',
    g18ext.uic
    
    FROM g18ext
    
    /* Replace value within single quotes with brigade UIC */
    WHERE g18ext.bdeuic = 'W71J'
    
    GROUP BY 
    
    GROUPING SETS (g18ext.uic, LEFT(g18ext.uic, 4), ())

    Note that the design of the underlying database has been imposed on the user and cannot be changed, nor do I have administrator priviledges to the server involved. The tables involved in the Production Query are joined on a primary key.  What I find particularly bizarre is that AVAIL is the inverse of NONAVAIL (both Boolean) and the NONAVAIL column is correct in both queries.

    I'm at wits end with trying to determine the reason and appreciate any assistance the community might provide.  Thank you in advance!


    • Edited by spacetanker Sunday, July 20, 2014 8:30 PM Edited title
    • Moved by Kalman Toth Tuesday, July 22, 2014 3:23 PM Not database design
    Sunday, July 20, 2014 8:25 PM

Answers

All replies