Solved

DISTINCT ROW IN GRIDVIEW

Posted on 2013-05-28
7
573 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
  • 4
  • 3
7 Comments
 
LVL 40

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 40

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 40

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Monitor input from a computer is usually nothing special.  In this instance it prevented anyone from using the computer.  This was a preconfiguration that didn't work.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 7, without downloading adware.

770 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