Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    5

    Unanswered: Problems with "WHERE" statement

    I am trying to create a statement that will combine two fields if they have the same accident number. So basically I have an accident report table that has a field for accident number and circumstance. There are two types of speeding circumstances ('04' and '05). If an accident report has both a '04' and a '05' speeding circumstance, I only want that circumstance counted once instead of twice even though two circumstances were reported. How do I tell my statement to do that? Here is the current statement I have. It counts all circumstances even if they have the same accident number.
    Code:
    SELECT  CNT AS ACC_CNT, (CASE SEVERITY WHEN '1' THEN 'Fatal' WHEN '2' THEN 'Personal Injury' WHEN '3' THEN 'Property Damage' END) AS SEV_ACC, 
                   (CASE ACCTYP WHEN '01' THEN 'Animal' WHEN '02' THEN 'Bicyclist' WHEN '03' THEN 'Fixed Object' WHEN '04' THEN 'Other Object' WHEN '05' THEN 'Pedestrian' WHEN '06' THEN 'Train'
                    WHEN '07' THEN 'Vehicle In Transport' WHEN '08' THEN 'Vehicle On Other Roadway' WHEN '09' THEN 'Parked Vehicle' WHEN '10' THEN 'Non-Collision Overturn' WHEN '11' THEN 'Non-Collision Other'
                    ELSE '0' END) AS ACC_TYP
    FROM     TABLE
                       (SELECT  COUNT(QO01ACCIDENT_NO) AS CNT, ACCTYP, SEVERITY, GROUPING(ACCTYP) AS ACCTYP_GROUP, GROUPING(SEVERITY) AS SEVERITY_GROUP
                        FROM     TABLE
                                           (SELECT  QO01ACCIDENT_NO, QO01ACCIDENT_SEVERITY AS SEVERITY, QO01ACCIDENT_TYPE AS ACCTYP
                                            FROM     XTECH.TR10TBACCIDENT a, XTECH.TR10TBVEHICLE_CIRCUMSTANCES vc
                                            WHERE  (QO01ACCIDENT_YEAR BETWEEN ? AND ?) AND (QO01SUBMIT_AGENCY_ORI = ? OR
                                                           QO01MUNICIPALITY = ?) AND (vc.FK_QO01ACCIDENT_NO = a.QO01ACCIDENT_NO) AND (QO05DR_VH_CONT_CIRC = '04' OR QO05DR_VH_CONT_CIRC = '05')) AS SEVACC
                        GROUP BY CUBE(ACCTYP, SEVERITY)
                        ORDER BY ACCTYP ASC, SEVERITY ASC) AS STAT
    WHERE  (ACCTYP_GROUP = 0 AND SEVERITY_GROUP = 0)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If I had to make a guess, I'd say that your SQL is for DB2 instead of Microsoft SQL Server. If it is, please let me know and I'll move this discussion thread to the DB2 forum where it will have a better chance of getting attention from people with DB2 expertise.

    If I'm correct in the DB2 assumption, then I'm 99.5% certain that all you really need is the addition of the DISTINCT keyword, like so:
    Code:
    SELECT  CNT AS ACC_CNT
    ,  (CASE SEVERITY
          WHEN '1' THEN 'Fatal' 
          WHEN '2' THEN 'Personal Injury' 
          WHEN '3' THEN 'Property Damage' END) AS SEV_ACC
    ,  (CASE ACCTYP 
          WHEN '01' THEN 'Animal' 
          WHEN '02' THEN 'Bicyclist' 
          WHEN '03' THEN 'Fixed Object' 
          WHEN '04' THEN 'Other Object' 
          WHEN '05' THEN 'Pedestrian' 
          WHEN '06' THEN 'Train'
          WHEN '07' THEN 'Vehicle In Transport' 
          WHEN '08' THEN 'Vehicle On Other Roadway' 
          WHEN '09' THEN 'Parked Vehicle' 
          WHEN '10' THEN 'Non-Collision Overturn' 
          WHEN '11' THEN 'Non-Collision Other'
          ELSE '0' END) AS ACC_TYP
       FROM TABLE (SELECT  COUNT(DISTINCT QO01ACCIDENT_NO) AS CNT
    ,     ACCTYP, SEVERITY
    ,     GROUPING(ACCTYP) AS ACCTYP_GROUP
    ,     GROUPING(SEVERITY) AS SEVERITY_GROUP
          FROM TABLE (SELECT  QO01ACCIDENT_NO
    ,        QO01ACCIDENT_SEVERITY AS SEVERITY
    ,        QO01ACCIDENT_TYPE AS ACCTYP
             FROM XTECH.TR10TBACCIDENT a
    ,           XTECH.TR10TBVEHICLE_CIRCUMSTANCES vc
             WHERE  (QO01ACCIDENT_YEAR BETWEEN ? AND ?)
                AND (QO01SUBMIT_AGENCY_ORI = ?
                   OR QO01MUNICIPALITY = ?) 
                AND (vc.FK_QO01ACCIDENT_NO = a.QO01ACCIDENT_NO) 
                AND (QO05DR_VH_CONT_CIRC = '04' 
                   OR QO05DR_VH_CONT_CIRC = '05')) AS SEVACC
             GROUP BY CUBE(ACCTYP, SEVERITY)
             ORDER BY ACCTYP ASC, SEVERITY ASC) AS STAT
       WHERE (ACCTYP_GROUP = 0
          AND SEVERITY_GROUP = 0)
    Please let me know if I'm correct in the DB2 assumption, and also if using DISTINCT solves your problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2011
    Posts
    5

    Thanks!!

    That did it! I feel like an idiot since it was that simple. Yes you are correct. I am creating the report in SSRS, but I am hitting a DB2 database. Thanks again for your help!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that no subquery nor CUBE were not necessary, like ...
    Code:
    SELECT COUNT(DISTINCT QO01ACCIDENT_NO) AS ACC_CNT
         , CASE QO01ACCIDENT_SEVERITY
           WHEN '1' THEN 'Fatal' 
           WHEN '2' THEN 'Personal Injury' 
           WHEN '3' THEN 'Property Damage'
           END AS SEV_ACC
         , CASE QO01ACCIDENT_TYPE
           WHEN '01' THEN 'Animal' 
           WHEN '02' THEN 'Bicyclist' 
           WHEN '03' THEN 'Fixed Object' 
           WHEN '04' THEN 'Other Object' 
           WHEN '05' THEN 'Pedestrian' 
           WHEN '06' THEN 'Train'
           WHEN '07' THEN 'Vehicle In Transport' 
           WHEN '08' THEN 'Vehicle On Other Roadway' 
           WHEN '09' THEN 'Parked Vehicle' 
           WHEN '10' THEN 'Non-Collision Overturn' 
           WHEN '11' THEN 'Non-Collision Other'
           ELSE           '0'
           END AS ACC_TYP
     FROM  XTECH.TR10TBACCIDENT              a
         , XTECH.TR10TBVEHICLE_CIRCUMSTANCES vc
     WHERE QO01ACCIDENT_YEAR BETWEEN ? AND ?
       AND
       (   QO01SUBMIT_AGENCY_ORI = ?
        OR
           QO01MUNICIPALITY      = ?
       )
       AND vc.FK_QO01ACCIDENT_NO = a.QO01ACCIDENT_NO
       AND QO05DR_VH_CONT_CIRC IN ('04' , '05')
     GROUP BY
           QO01ACCIDENT_SEVERITY
         , QO01ACCIDENT_TYPE
     ORDER BY
           QO01ACCIDENT_SEVERITY ASC
         , QO01ACCIDENT_TYPE     ASC
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •