Solved

DISTINCT ROW IN GRIDVIEW

Posted on 2013-05-28
7
576 Views
Last Modified: 2013-06-04
I want to retrieve rows of data showing for each employee one value for Amount based on the criteria set.
So as in the example, only one record for emp_id 12894 or 15950 should be shown.

However, when I run the query I obtain individual entries for the specific criteria but not one row that sums the unit charged or amount field.

I am not using any vb code. My sql statement in the querybuilder is not generating unique records by employee id and as such my gridview does not display what I need.

See attached

Any help will be appreciated.
SCREENSHOT.doc
0
Comment
Question by:ernie_shah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39202940
Then remove the unwanted columns from GROUP BY clause.
SELECT softech.GLTABLE_ARC.EMPL_ID, 
       softech.EMPLOYEE.FIRST_NAME, 
       softech.EMPLOYEE.LAST_NAME, 
       softech.GLTABLE_ARC.ACCOUNT_CODE, 
       SUM(softech.GLTABLE_ARC.AMOUNT)                AS AMOUNT, 
       softech.GLTABLE_ARC.SEGMENT1, 
       SUM(softech.GLTABLE_ARC.UNITS_CHARGED)         AS UNITS_CHARGED, 
       SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) AS LASTSEVENAC 
  FROM softech.EMPLOYEE, 
       softech.GLTABLE_ARC 
 WHERE softech.EMPLOYEE.EMPL_ID = softech.GLTABLE_ARC.EMPL_ID 
 GROUP BY --softech.GLTABLE_ARC.CYCLE_NO, 
          --softech.GLTABLE_ARC.PAY_YEAR, 
          --softech.GLTABLE_ARC.OOC_RUN, 
          softech.GLTABLE_ARC.EMPL_ID, 
          softech.EMPLOYEE.FIRST_NAME, 
          softech.EMPLOYEE.LAST_NAME, 
          --softech.GLTABLE_ARC.CODE_TYPE, 
          --softech.GLTABLE_ARC.CODE_FIELD, 
          softech.GLTABLE_ARC.ACCOUNT_CODE, 
          softech.GLTABLE_ARC.SEGMENT1, 
          SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) 
HAVING ( softech.GLTABLE_ARC.CYCLE_NO = 4 ) 
       AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 ) 
       AND ( softech.GLTABLE_ARC.OOC_RUN >= TO_DATE('4/16/2013', 'MM/DD/YYYY') ) 
       AND ( softech.GLTABLE_ARC.CODE_FIELD = '771' 
              OR softech.GLTABLE_ARC.CODE_FIELD = '773' 
              OR softech.GLTABLE_ARC.CODE_FIELD = '779' 
              OR softech.GLTABLE_ARC.CODE_FIELD = '780' ) 
       AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' ) 
        OR AND (softech.GLTABLE_ARC.CYCLE_NO = 4) 
           AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 ) 
           AND ( softech.GLTABLE_ARC.OOC_RUN IS NULL ) 
           AND ( softech.GLTABLE_ARC.CODE_FIELD = '771' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '773' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '779' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '780' ) 
           AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' ) 
           AND ( softech.GLTABLE_ARC.CYCLE_NO = 5 ) 
           AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 ) 
           AND ( softech.GLTABLE_ARC.OOC_RUN <= TO_DATE('5/15/2013', 'MM/DD/YYYY') ) 
           AND ( softech.GLTABLE_ARC.CODE_FIELD = '771' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '773' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '779' 
                  OR softech.GLTABLE_ARC.CODE_FIELD = '780' ) 
           AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' ) 
 ORDER BY softech.GLTABLE_ARC.EMPL_ID 

Open in new window

0
 

Author Comment

by:ernie_shah
ID: 39204572
This does not work. No results are generated using the AND rather than the OR at line 31 and line 39.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39204946
First, there are lot of syntactical errors in your query.

Second, I have not changed anything except commenting unwanted columns in GROUP BY clause.

If that does not work, you have issues in your original query.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ernie_shah
ID: 39205084
I don't understand. The query below works when I use OR at line 31 and 39.
The problem is that I am not achieving the desired result.
Instead of being able to view one record per employee that shows the sum of amount,
I am seeing individual transactions for the employee.

SELECT softech.GLTABLE_ARC.EMPL_ID,
       softech.EMPLOYEE.FIRST_NAME,
       softech.EMPLOYEE.LAST_NAME,
       softech.GLTABLE_ARC.ACCOUNT_CODE,
       SUM(softech.GLTABLE_ARC.AMOUNT)                AS AMOUNT,
       softech.GLTABLE_ARC.SEGMENT1,
       SUM(softech.GLTABLE_ARC.UNITS_CHARGED)         AS UNITS_CHARGED,
       SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) AS LASTSEVENAC
  FROM softech.EMPLOYEE,
       softech.GLTABLE_ARC
 WHERE softech.EMPLOYEE.EMPL_ID = softech.GLTABLE_ARC.EMPL_ID
 GROUP BY --softech.GLTABLE_ARC.CYCLE_NO,
          --softech.GLTABLE_ARC.PAY_YEAR,
          --softech.GLTABLE_ARC.OOC_RUN,
          softech.GLTABLE_ARC.EMPL_ID,
          softech.EMPLOYEE.FIRST_NAME,
          softech.EMPLOYEE.LAST_NAME,
          --softech.GLTABLE_ARC.CODE_TYPE,
          --softech.GLTABLE_ARC.CODE_FIELD,
          softech.GLTABLE_ARC.ACCOUNT_CODE,
          softech.GLTABLE_ARC.SEGMENT1,
          SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2)
HAVING ( softech.GLTABLE_ARC.CYCLE_NO = 4 )
       AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 )
       AND ( softech.GLTABLE_ARC.OOC_RUN >= TO_DATE('4/16/2013', 'MM/DD/YYYY') )
       AND ( softech.GLTABLE_ARC.CODE_FIELD = '771'
              OR softech.GLTABLE_ARC.CODE_FIELD = '773'
              OR softech.GLTABLE_ARC.CODE_FIELD = '779'
              OR softech.GLTABLE_ARC.CODE_FIELD = '780' )
       AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' )
        OR  (softech.GLTABLE_ARC.CYCLE_NO = 4)
           AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 )
           AND ( softech.GLTABLE_ARC.OOC_RUN IS NULL )
           AND ( softech.GLTABLE_ARC.CODE_FIELD = '771'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '773'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '779'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '780' )
           AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' )
           OR ( softech.GLTABLE_ARC.CYCLE_NO = 5 )
           AND ( softech.GLTABLE_ARC.PAY_YEAR = 2013 )
           AND ( softech.GLTABLE_ARC.OOC_RUN <= TO_DATE('5/15/2013', 'MM/DD/YYYY') )
           AND ( softech.GLTABLE_ARC.CODE_FIELD = '771'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '773'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '779'
                  OR softech.GLTABLE_ARC.CODE_FIELD = '780' )
           AND ( SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' )
 ORDER BY softech.GLTABLE_ARC.EMPL_ID  


Thanks
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39205332
Can you try this?
SELECT softech.GLTABLE_ARC.EMPL_ID, 
       softech.EMPLOYEE.FIRST_NAME, 
       softech.EMPLOYEE.LAST_NAME, 
       softech.GLTABLE_ARC.ACCOUNT_CODE, 
       SUM(softech.GLTABLE_ARC.AMOUNT)                AS AMOUNT, 
       softech.GLTABLE_ARC.SEGMENT1, 
       SUM(softech.GLTABLE_ARC.UNITS_CHARGED)         AS UNITS_CHARGED, 
       SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) AS LASTSEVENAC 
  FROM softech.EMPLOYEE, 
       softech.GLTABLE_ARC 
 WHERE softech.EMPLOYEE.EMPL_ID = softech.GLTABLE_ARC.EMPL_ID 
   AND softech.GLTABLE_ARC.CODE_FIELD IN( '771', '773', '779', '780' ) 
   AND SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) LIKE '70' 
   AND softech.GLTABLE_ARC.PAY_YEAR = 2013 
   AND ( ( softech.GLTABLE_ARC.CYCLE_NO = 4 
           AND softech.GLTABLE_ARC.OOC_RUN >= TO_DATE('4/16/2013', 'MM/DD/YYYY') ) 
          OR ( softech.GLTABLE_ARC.CYCLE_NO = 4 
               AND softech.GLTABLE_ARC.OOC_RUN IS NULL ) 
          OR ( softech.GLTABLE_ARC.CYCLE_NO = 5 
               AND softech.GLTABLE_ARC.OOC_RUN <= TO_DATE('5/15/2013', 'MM/DD/YYYY') ) ) 
 GROUP BY --softech.GLTABLE_ARC.CYCLE_NO,   
--softech.GLTABLE_ARC.PAY_YEAR,   
--softech.GLTABLE_ARC.OOC_RUN,   
softech.GLTABLE_ARC.EMPL_ID, 
softech.EMPLOYEE.FIRST_NAME, 
softech.EMPLOYEE.LAST_NAME, 
--softech.GLTABLE_ARC.CODE_TYPE,   
--softech.GLTABLE_ARC.CODE_FIELD,   
softech.GLTABLE_ARC.ACCOUNT_CODE, 
softech.GLTABLE_ARC.SEGMENT1, 
SUBSTR(softech.GLTABLE_ARC.ACCOUNT_CODE, 7, 2) 
 ORDER BY softech.GLTABLE_ARC.EMPL_ID 

Open in new window

0
 

Author Comment

by:ernie_shah
ID: 39206786
Ok, I will try this in my query builder and get back to you. Off work till Monday so you will hear from me then. Thanks so much for your time and assistance. Hear from me soon.
0
 

Author Comment

by:ernie_shah
ID: 39216157
Good Morning:

My issue has been resolved.

Thanks alot.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question