Sponsored Content
Top Forums UNIX for Beginners Questions & Answers DB2 Query modification to remove duplicate values using LISTAGG function Post 303036826 by Corona688 on Friday 12th of July 2019 03:42:58 PM
Old 07-12-2019
You're mashing together at least two different sets of results grouped by employee, I think. Select employee and country, and you might get something like this:
Code:
1 MEXICO, UNITED STATES, INDIA, JAPAN, UNITED KINGDOM
2 MEXICO, UNITED STATES

If you just want all countries from all employees, just SELECT DISTINCT LISTAGG(COUNTRIES, ',') FROM LOCATION; may do.
 

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
Moose::Cookbook::Basics::Recipe4(3)			User Contributed Perl Documentation		       Moose::Cookbook::Basics::Recipe4(3)

NAME
Moose::Cookbook::Basics::Recipe4 - Subtypes, and modeling a simple Company class hierarchy VERSION
version 2.0205 SYNOPSIS
package Address; use Moose; use Moose::Util::TypeConstraints; use Locale::US; use Regexp::Common 'zip'; my $STATES = Locale::US->new; subtype 'USState' => as Str => where { ( exists $STATES->{code2state}{ uc($_) } || exists $STATES->{state2code}{ uc($_) } ); }; subtype 'USZipCode' => as Value => where { /^$RE{zip}{US}{-extended => 'allow'}$/; }; has 'street' => ( is => 'rw', isa => 'Str' ); has 'city' => ( is => 'rw', isa => 'Str' ); has 'state' => ( is => 'rw', isa => 'USState' ); has 'zip_code' => ( is => 'rw', isa => 'USZipCode' ); package Company; use Moose; use Moose::Util::TypeConstraints; has 'name' => ( is => 'rw', isa => 'Str', required => 1 ); has 'address' => ( is => 'rw', isa => 'Address' ); has 'employees' => ( is => 'rw', isa => 'ArrayRef[Employee]', default => sub { [] }, ); sub BUILD { my ( $self, $params ) = @_; foreach my $employee ( @{ $self->employees } ) { $employee->employer($self); } } after 'employees' => sub { my ( $self, $employees ) = @_; return unless $employees; foreach my $employee ( @$employees ) { $employee->employer($self); } }; package Person; use Moose; has 'first_name' => ( is => 'rw', isa => 'Str', required => 1 ); has 'last_name' => ( is => 'rw', isa => 'Str', required => 1 ); has 'middle_initial' => ( is => 'rw', isa => 'Str', predicate => 'has_middle_initial' ); has 'address' => ( is => 'rw', isa => 'Address' ); sub full_name { my $self = shift; return $self->first_name . ( $self->has_middle_initial ? ' ' . $self->middle_initial . '. ' : ' ' ) . $self->last_name; } package Employee; use Moose; extends 'Person'; has 'title' => ( is => 'rw', isa => 'Str', required => 1 ); has 'employer' => ( is => 'rw', isa => 'Company', weak_ref => 1 ); override 'full_name' => sub { my $self = shift; super() . ', ' . $self->title; }; DESCRIPTION
This recipe introduces the "subtype" sugar function from Moose::Util::TypeConstraints. The "subtype" function lets you declaratively create type constraints without building an entire class. In the recipe we also make use of Locale::US and Regexp::Common to build constraints, showing how constraints can make use of existing CPAN tools for data validation. Finally, we introduce the "required" attribute option. In the "Address" class we define two subtypes. The first uses the Locale::US module to check the validity of a state. It accepts either a state abbreviation of full name. A state will be passed in as a string, so we make our "USState" type a subtype of Moose's builtin "Str" type. This is done using the "as" sugar. The actual constraint is defined using "where". This function accepts a single subroutine reference. That subroutine will be called with the value to be checked in $_ (1). It is expected to return a true or false value indicating whether the value is valid for the type. We can now use the "USState" type just like Moose's builtin types: has 'state' => ( is => 'rw', isa => 'USState' ); When the "state" attribute is set, the value is checked against the "USState" constraint. If the value is not valid, an exception will be thrown. The next "subtype", "USZipCode", uses Regexp::Common. Regexp::Common includes a regex for validating US zip codes. We use this constraint for the "zip_code" attribute. subtype 'USZipCode' => as Value => where { /^$RE{zip}{US}{-extended => 'allow'}$/; }; Using a subtype instead of requiring a class for each type greatly simplifies the code. We don't really need a class for these types, as they're just strings, but we do want to ensure that they're valid. The type constraints we created are reusable. Type constraints are stored by name in a global registry, which means that we can refer to them in other classes. Because the registry is global, we do recommend that you use some sort of namespacing in real applications, like "MyApp::Type::USState" (just as you would do with class names). These two subtypes allow us to define a simple "Address" class. Then we define our "Company" class, which has an address. As we saw in earlier recipes, Moose automatically creates a type constraint for each our classes, so we can use that for the "Company" class's "address" attribute: has 'address' => ( is => 'rw', isa => 'Address' ); A company also needs a name: has 'name' => ( is => 'rw', isa => 'Str', required => 1 ); This introduces a new attribute option, "required". If an attribute is required, then it must be passed to the class's constructor, or an exception will be thrown. It's important to understand that a "required" attribute can still be false or "undef", if its type constraint allows that. The next attribute, "employees", uses a parameterized type constraint: has 'employees' => ( is => 'rw', isa => 'ArrayRef[Employee]' default => sub { [] }, ); This constraint says that "employees" must be an array reference where each element of the array is an "Employee" object. It's worth noting that an empty array reference also satisfies this constraint, such as the value given as the default here. Parameterizable type constraints (or "container types"), such as "ArrayRef[`a]", can be made more specific with a type parameter. In fact, we can arbitrarily nest these types, producing something like "HashRef[ArrayRef[Int]]". However, you can also just use the type by itself, so "ArrayRef" is legal. (2) If you jump down to the definition of the "Employee" class, you will see that it has an "employer" attribute. When we set the "employees" for a "Company" we want to make sure that each of these employee objects refers back to the right "Company" in its "employer" attribute. To do that, we need to hook into object construction. Moose lets us do this by writing a "BUILD" method in our class. When your class defines a "BUILD" method, it will be called by the constructor immediately after object construction, but before the object is returned to the caller. Note that all "BUILD" methods in your class hierarchy will be called automatically; there is no need to (and you should not) call the superclass "BUILD" method. The "Company" class uses the "BUILD" method to ensure that each employee of a company has the proper "Company" object in its "employer" attribute: sub BUILD { my ( $self, $params ) = @_; foreach my $employee ( @{ $self->employees } ) { $employee->employer($self); } } The "BUILD" method is executed after type constraints are checked, so it is safe to assume that if "$self->employees" has a value, it will be an array reference, and that the elements of that array reference will be "Employee" objects. We also want to make sure that whenever the "employees" attribute for a "Company" is changed, we also update the "employer" for each employee. To do this we can use an "after" modifier: after 'employees' => sub { my ( $self, $employees ) = @_; return unless $employees; foreach my $employee ( @$employees ) { $employee->employer($self); } }; Again, as with the "BUILD" method, we know that the type constraint check has already happened, so we know that if $employees is defined it will contain an array reference of "Employee" objects. Note that "employees" is a read/write accessor, so we must return early if it's called as a reader. The Person class does not really demonstrate anything new. It has several "required" attributes. It also has a "predicate" method, which we first used in recipe 3. The only new feature in the "Employee" class is the "override" method modifier: override 'full_name' => sub { my $self = shift; super() . ', ' . $self->title; }; This is just a sugary alternative to Perl's built in "SUPER::" feature. However, there is one difference. You cannot pass any arguments to "super". Instead, Moose simply passes the same parameters that were passed to the method. A more detailed example of usage can be found in t/recipes/moose_cookbook_basics_recipe4.t. CONCLUSION
This recipe was intentionally longer and more complex. It illustrates how Moose classes can be used together with type constraints, as well as the density of information that you can get out of a small amount of typing when using Moose. This recipe also introduced the "subtype" function, the "required" attribute, and the "override" method modifier. We will revisit type constraints in future recipes, and cover type coercion as well. FOOTNOTES
(1) The value being checked is also passed as the first argument to the "where" block, so it can be accessed as $_[0]. (2) Note that "ArrayRef[]" will not work. Moose will not parse this as a container type, and instead you will have a new type named "ArrayRef[]", which doesn't make any sense. AUTHOR
Stevan Little <stevan@iinteractive.com> COPYRIGHT AND LICENSE
This software is copyright (c) 2011 by Infinity Interactive, Inc.. This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself. perl v5.12.5 2011-09-06 Moose::Cookbook::Basics::Recipe4(3)
All times are GMT -4. The time now is 06:27 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy