?
Solved

DISTINCT ROW IN GRIDVIEW

Posted on 2013-05-28
7
Medium Priority
?
587 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Computer running slow? Taking forever to open a folder, documents, or any programs that you didn't have an issue with before? Here are a few steps to help speed it up. The programs mentioned below ALL have free versions, you can buy them if you w…
You have seen this as an option on your internet browser before or it may be completely new to you.  But what does this mean and why would I use this?
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.
Suggested Courses
Course of the Month14 days, 18 hours left to enroll

839 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