| duplicate fields [message #289442] |
Fri, 21 December 2007 13:28  |
kand
Messages: 20
Registered: November 2007
Location: USA
|
Junior Member |
|
|
Hi
How do i remove duplicate fields in my report? pls see the example below
firm_name worktype_code worktype descr
ABC 5.1 ENVCODE
ABC 5.1 ENVCODE
ABC 5.1 ENVCODE
ABC 10.1 BLDGCODE
ABC 10.1 BLDGCODE
ABC 10.1 BLDGCODE
ABC 15.1 PAVCODE
ABC 15.1 SALTCODE
ABC 15.1 TEKCODE
from the example if you observe except for the worktype code 15.1 the rest all codes are repeating the same type of work descr. I want to get rid of the duplicates for all other worktype codes ecept for 15.1. Please any kind of help is appreciated.
Thanks
[EDITED by LF: added [code] tags to improve readability]
[Updated on: Fri, 21 December 2007 14:24] by Moderator Report message to a moderator |
|
|
|
| Re: duplicate fields [message #289449 is a reply to message #289442] |
Fri, 21 December 2007 14:33  |
 |
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Two simple options: use DISTINCT or GROUP BY:SQL> select DISTINCT firm_name, worktype_code, worktype_descr
2 from test
3 order by to_number(worktype_code);
FIRM_NAME WORKTYPE_CODE WORKTYPE_DESCR
-------------------- -------------------- --------------------
ABC 5.1 ENVCODE
ABC 10.1 BLDGCODE
ABC 15.1 SALTCODE
ABC 15.1 TEKCODE
ABC 15.1 PAVCODE
SQL> select firm_name, worktype_code, worktype_descr
2 from test
3 group by firm_name, worktype_code, worktype_descr
4 order by to_number(worktype_code);
FIRM_NAME WORKTYPE_CODE WORKTYPE_DESCR
-------------------- -------------------- --------------------
ABC 5.1 ENVCODE
ABC 10.1 BLDGCODE
ABC 15.1 SALTCODE
ABC 15.1 TEKCODE
ABC 15.1 PAVCODE
SQL>
Or, if you want to do it using Report Builder's capabilities, create a GROUP based on 'firm_name' and 'worktype_code'.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
