title: SQL plus query returns duplicate rows
body: <p>My Oracle9i query is generating duplicate rows no matter what I do. It's also incorrectly assigning values and sample numbers to dates. I've tried to use joins unsuccessfully and have had no luck using distinct either. I've also tried to type in sd.SAMPLE_NUM=shv.SAMPLE_NUM in the where statement. This has not helped me though. When I do this I get zero rows returned. Thank you in advance for any help.</p>
  
  <pre><code>SELECT 
           DISTINCT 
           DATE_SAMPLE,
           SW_NAME,
           SAMPLE_NUM,
           BU_ID,
           sum(case when ANALYSIS_NAME = 'METHANE-MOL%' then ANALYSIS_RESULT else null end) C1,
           sum(case when ANALYSIS_NAME = 'ETHANE-MOL%' then ANALYSIS_RESULT else null end) C2,
           sum(case when ANALYSIS_NAME = 'PROPANE-MOL%' then ANALYSIS_RESULT else null end) C3
  FROM
         (
          SELECT 
                   DISTINCT 
                   sd.SW_NAME,
                   sd.SAMPLE_NUM, 
                   sd.ANALYSIS_RESULT,
                   sd.ANALYSIS_NAME,
                   sd.BU_ID,
                   shv.DATE_SAMPLE 
          FROM 
                   public_project.Sample_Data_Pbu_Lab_Vew sd,
                   public_project.Sample_Header_Vew shv
          WHERE 
                   (sd.SW_NAME LIKE '02-40%') AND                   
                   sd.BU_ID='PBU' AND
                   shv.DATE_SAMPLE &gt; TO_DATE ('2013-01-01', 'YYYY-MM-DD') 
          GROUP BY      
                   sd.SW_NAME,
                   sd.SAMPLE_NUM,
                   sd.ANALYSIS_RESULT,
                   sd.ANALYSIS_NAME,
                   sd.BU_ID,
                   shv.DATE_SAMPLE
         )
  GROUP BY 
           DATE_SAMPLE,
           SW_NAME,
           SAMPLE_NUM,
           BU_ID;
  </code></pre>
  
  <p>Sample of query output (there are several thousand lines similar to this):</p>
  
  <pre><code>DATE_SAMPL SW_NAME      SAMPLE_NUM    BU_ID      C1         C2         C3
  ---------- ------------ ------------- ---------- ---------- ---------- ----------
  2013-01-01 02-40        PC66207       PBU        79.981     5.192      1.681
  2013-01-01 02-40        PC43602       PBU        79.062     5.333      1.862
  2013-01-01 02-40        PC44278       PBU
  2013-01-01 02-40        PC61178       PBU
  2013-01-01 02-40        PC66207       PBU        79.981     5.192      1.681
  2013-01-01 02-40        PC43602       PBU        79.062     5.333      1.862
  2013-01-01 02-40        PC44278       PBU
  2013-01-01 02-40        PC61178       PBU
  2013-01-01 02-40        PC66207       PBU        79.981     5.192      1.681
  2013-01-01 02-40        PC43602       PBU        79.062     5.333      1.862
  </code></pre>
  
  <p>A describe on the tables yields:</p>
  
  <pre><code>$SQL&gt; DESCRIBE public_project.Sample_Data_Pbu_Lab_Vew
  
  Name                                      Null?    Type
  ----------------------------------------- -------- -----------------
  API                                                VARCHAR2(16)
  SW_NAME                                            VARCHAR2(20)
  BU_ID                                              VARCHAR2(30)
  PROJECT                                            VARCHAR2(30)
  SAMPLE_NUM                                NOT NULL VARCHAR2(15)
  ANALYSIS_NAME                             NOT NULL VARCHAR2(80)
  ANALYSIS_METHOD_NUM                       NOT NULL VARCHAR2(15)
  ANALYSIS_RESULT                                    NUMBER
  ANALYSIS_UNITS                                     VARCHAR2(20)
  ANALYSIS_RESULT_TEXT                               VARCHAR2(80)
  DATE_ANALYZED                                      DATE
  LAST_UPDATE_ID                                     VARCHAR2(30)
  LAST_UPDATE_DATE                                   DATE
  
  SQL&gt; DESCRIBE public_project.Sample_Header_Vew
  Name                                      Null?    Type
  ----------------------------------------- -------- -----------------
  API                                                VARCHAR2(16)
  SW_NAME                                            VARCHAR2(20)
  BU_ID                                              VARCHAR2(30)
  PROJECT                                            VARCHAR2(30)
  SAMPLE_NUM                                NOT NULL VARCHAR2(15)
  COMMENTS                                           VARCHAR2(500)
  DATE_SAMPLE                                        DATE
  DATE_RECEIVED                                      DATE
  METER_NUM                                          VARCHAR2(4)
  PWSID                                              NUMBER
  SAMPLE_CATEGORY                                    VARCHAR2(20)
  SAMPLE_DESC                                        VARCHAR2(80)
  SAMPLE_FACILITY                                    VARCHAR2(18)
  SAMPLE_LAB                                         VARCHAR2(15)
  SAMPLE_POINT                                       VARCHAR2(80)
  SAMPLE_TYPE                                        VARCHAR2(20)
  LAST_UPDATE_ID                                     VARCHAR2(30)
  LAST_UPDATE_DATE                                   DATE
  SAMPLE_DEPTH                                       NUMBER
  SAMPLE_DEPTH_REF                                   VARCHAR2(5)
  </code></pre>