asked on

DISTINCT ROW IN GRIDVIEW

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

Sharath S

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

ernie_shah

ASKER

This does not work. No results are generated using the AND rather than the OR at line 31 and line 39.

Sharath S

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.

ernie_shah

ASKER

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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ernie_shah

ASKER

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.

ernie_shah

ASKER

Good Morning:

My issue has been resolved.

Thanks alot.