Sponsored Content
Top Forums UNIX for Beginners Questions & Answers DB2 Query modification to remove duplicate values using LISTAGG function Post 303036823 by Perlbaby on Friday 12th of July 2019 12:29:54 PM
Old 07-12-2019
DB2 Query modification to remove duplicate values using LISTAGG function

I am using DB2 v9 and trying to get country values in comma seperated format using below query

Code:
SELECT  distinct LISTAGG(COUNTRIES, ',') WITHIN GROUP(ORDER BY EMPLOYEE)  
FROM LOCATION ;

Output Achieved 
MEXICO,UNITED STATES,INDIA,JAPAN,UNITED KINGDOM,MEXICO,UNITED STATES


The table "LOCATION" has multiple columns apart from Employee and Country and hence i cant group on countries to remove duplicate countries values .
Is there a way to remove dups to get only countries once using above query with modification .

Code:
Expected Output  ( Should exclude MEXICO,UNITED STATES as these two countries are duplicated) 
MEXICO,UNITED STATES,INDIA,JAPAN,UNITED KINGDOM

Any help appreciated.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Db2 query with script

Hi All, I want to connect two tables in DB2 using shell script and then compare the contents of two tables field by field.and i should return on the screen the un matched records .. Could any one please help me in connecting database tables using Unix and retriving data from the same. (1 Reply)
Discussion started by: kanakaraju
1 Replies

2. UNIX for Dummies Questions & Answers

[SOLVED] remove lines that have duplicate values in column two

Hi, I've got a file that I'd like to uniquely sort based on column 2 (values in column 2 begin with "comp"). I tried sort -t -nuk2,3 file.txtBut got: sort: multi-character tab `-nuk2,3' "man sort" did not help me out Any pointers? Input: Output: (5 Replies)
Discussion started by: pathunkathunk
5 Replies

3. Shell Programming and Scripting

awk file to read values from Db2 table replacing hard coded values

Hi, I want to replace a chain of if-else statement in an old AWK file with values from Db2 table or CSV file. The part of code is below... if (start_new_rec=="true"){ exclude_user="false"; user=toupper($6); match(user, "XXXXX."); if (RSTART ==2 ) { ... (9 Replies)
Discussion started by: asandy1234
9 Replies

4. Shell Programming and Scripting

Remove duplicate values with condition

Hi Gents, Please can you help me to get the desired output . In the first column I have some duplicate records, The condition is that all need to reject the duplicate record keeping the last occurrence. But the condition is. If the last occurrence is equal to value 14 or 98 in column 3 and... (2 Replies)
Discussion started by: jiam912
2 Replies

5. Shell Programming and Scripting

Implementing Listagg like function in shell

Hi, Basically what I am trying to do is making multiple fields of the same type comma-separated. i.e. for a data like this: B00000 abc B00001 abc,def B00001 ghi B00001 jkl B00002 abc B00002 def B00003 xyz Output should be like: B00000 abc B00001 abc,def,ghi,jkl... (20 Replies)
Discussion started by: prohank
20 Replies

6. Shell Programming and Scripting

Filter file to remove duplicate values in first column

Hello, I have a script that is generating a tab delimited output file. num Name PCA_A1 PCA_A2 PCA_A3 0 compound_00 -3.5054 -1.1207 -2.4372 1 compound_01 -2.2641 0.4287 -1.6120 3 compound_03 -1.3053 1.8495 ... (3 Replies)
Discussion started by: LMHmedchem
3 Replies

7. Shell Programming and Scripting

Remove duplicate values in a column(not in the file)

Hi Gurus, I have a file(weblog) as below abc|xyz|123|agentcode=sample code abcdeeess,agentcode=sample code abcdeeess,agentcode=sample code abcdeeess|agentadd=abcd stereet 23343,agentadd=abcd stereet 23343 sss|wwq|999|agentcode=sample1 code wqwdeeess,gentcode=sample1 code... (4 Replies)
Discussion started by: ratheeshjulk
4 Replies

8. Shell Programming and Scripting

Find duplicate values in specific column and delete all the duplicate values

Dear folks I have a map file of around 54K lines and some of the values in the second column have the same value and I want to find them and delete all of the same values. I looked over duplicate commands but my case is not to keep one of the duplicate values. I want to remove all of the same... (4 Replies)
Discussion started by: sajmar
4 Replies

9. Programming

DB2 Query -Convert multi values from column to rows

Hi Team I am using DB2 artisan tool and struck to handle multi values present in columns that are comma(,) separated. I want to convert those column values in separate rows . For example : Column 1 Column2 Jan,Feb Hold,Sell,Buy Expected Result Column1 ... (3 Replies)
Discussion started by: Perlbaby
3 Replies

10. Programming

DB2 Query to pick hierarchy values

Dear Team I am using DB2 v9 . I have a condition to check roles based on hierarchies like below example. 1.Ramesh has Roles as "Manager" and "Interviewer" 2.KITS has Roles as "Interviewer" 3.ANAND has Roles as "Manager" and "Interviewer" select * FROM TESTING NAME ... (6 Replies)
Discussion started by: Perlbaby
6 Replies
Locale::Country(3pm)					 Perl Programmers Reference Guide				      Locale::Country(3pm)

NAME
Locale::Country - standard codes for country identification SYNOPSIS
use Locale::Country; $country = code2country('jp' [,CODESET]); # $country gets 'Japan' $code = country2code('Norway' [,CODESET]); # $code gets 'no' @codes = all_country_codes( [CODESET]); @names = all_country_names(); # semi-private routines Locale::Country::alias_code('uk' => 'gb'); Locale::Country::rename_country('gb' => 'Great Britain'); DESCRIPTION
The "Locale::Country" module provides access to several code sets that can be used for identifying countries, such as those defined in ISO 3166-1. Most of the routines take an optional additional argument which specifies the code set to use. If not specified, the default ISO 3166-1 two-letter codes will be used. SUPPORTED CODE SETS
There are several different code sets you can use for identifying countries. A code set may be specified using either a name, or a constant that is automatically exported by this module. For example, the two are equivalent: $country = code2country('jp','alpha-2'); $country = code2country('jp',LOCALE_CODE_ALPHA_2); The codesets currently supported are: alpha-2, LOCALE_CODE_ALPHA_2 This is the set of two-letter (lowercase) codes from ISO 3166-1, such as 'tv' for Tuvalu. This is the default code set. alpha-3, LOCALE_CODE_ALPHA_3 This is the set of three-letter (lowercase) codes from ISO 3166-1, such as 'brb' for Barbados. These codes are actually defined and maintained by the U.N. Statistics division. numeric, LOCALE_CODE_NUMERIC This is the set of three-digit numeric codes from ISO 3166-1, such as 064 for Bhutan. These codes are actually defined and maintained by the U.N. Statistics division. If a 2-digit code is entered, it is converted to 3 digits by prepending a 0. fips-10, LOCALE_CODE_FIPS The FIPS 10 data are two-letter (uppercase) codes assigned by the National Geospatial-Intelligence Agency. dom, LOCALE_CODE_DOM The IANA is responsible for delegating management of the top level country domains. The country domains are the two-letter (lowercase) codes from ISO 3166 with a few other additions. ROUTINES
code2country ( CODE [,CODESET] ) country2code ( NAME [,CODESET] ) country_code2code ( CODE ,CODESET ,CODESET2 ) all_country_codes ( [CODESET] ) all_country_names ( [CODESET] ) Locale::Country::rename_country ( CODE ,NEW_NAME [,CODESET] ) Locale::Country::add_country ( CODE ,NAME [,CODESET] ) Locale::Country::delete_country ( CODE [,CODESET] ) Locale::Country::add_country_alias ( NAME ,NEW_NAME ) Locale::Country::delete_country_alias ( NAME ) Locale::Country::rename_country_code ( CODE ,NEW_CODE [,CODESET] ) Locale::Country::add_country_code_alias ( CODE ,NEW_CODE [,CODESET] ) Locale::Country::delete_country_code_alias ( CODE [,CODESET] ) These routines are all documented in the Locale::Codes::API man page. alias_code ( ALIAS, CODE [,CODESET] ) Version 2.07 included 2 functions for modifying the internal data: rename_country and alias_code. Both of these could be used only to modify the internal data for country codes. As of 3.10, the internal data for all types of codes can be modified. The alias_code function is preserved for backwards compatibility, but the following two are identical: alias_code(ALIAS,CODE [,CODESET]); rename_country_code(CODE,ALIAS [,CODESET]); and the latter should be used for consistency. The alias_code function is deprecated and will be removed at some point in the future. Note: this function was previously called _alias_code, but the leading underscore has been dropped. The old name was supported for all 2.X releases, but has been dropped as of 3.00. SEE ALSO
Locale::Codes The Locale-Codes distribution. Locale::Codes::API The list of functions supported by this module. Locale::SubCountry ISO codes for country sub-divisions (states, counties, provinces, etc), as defined in ISO 3166-2. This module is not part of the Locale-Codes distribution, but is available from CPAN in CPAN/modules/by-module/Locale/ http://www.iso.org/iso/country_codes Official home page for the ISO 3166 maintenance agency. Unfortunately, they do not make the actual ISO available for free, so I cannot check the alpha-3 and numerical codes here. http://www.iso.org/iso/list-en1-semic-3.txt http://www.iso.org/iso/home/standards/country_codes/iso-3166-1_decoding_table.htm The source of ISO 3166-1 two-letter codes used by this module. http://unstats.un.org/unsd/methods/m49/m49alpha.htm The source of the official ISO 3166-1 three-letter codes and three-digit codes. For some reason, this table is incomplete! Several countries are missing from it, and I cannot find them anywhere on the UN site. I get as much of the data from here as I can. http://earth-info.nga.mil/gns/html/digraphs.htm The official list of the FIPS 10 codes. http://www.iana.org/domains/ Official source of the top-level domain names. https://www.cia.gov/library/publications/the-world-factbook/appendix/print_appendix-d.html The World Factbook maintained by the CIA is a potential source of the data. Unfortunately, it adds/preserves non-standard codes, so it is no longer used as a source of data. http://www.statoids.com/wab.html Another unofficial source of data. Currently, it is not used to get data, but the notes and explanatory material were very useful for understanding discrepancies between the sources. AUTHOR
See Locale::Codes for full author history. Currently maintained by Sullivan Beck (sbeck@cpan.org). COPYRIGHT
Copyright (c) 1997-2001 Canon Research Centre Europe (CRE). Copyright (c) 2001-2010 Neil Bowers Copyright (c) 2010-2013 Sullivan Beck This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. perl v5.18.2 2014-01-06 Locale::Country(3pm)
All times are GMT -4. The time now is 08:54 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy