Solved

DISTINCT ROW IN GRIDVIEW

Posted on 2013-05-28
7
580 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
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 

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

Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Employees depend heavily on their PCs, and new threats like ransomware make it even more critical to protect their important data.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

729 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