Grop by with multiple aggregate clauses

Multiple aggregate clauses in a group by..

select mstr_date
,sum((case when bs ='S' then -1 else 1 end )*quantity) as qty_sum
,sum((case when bs ='S' then -1 else 1 end)*orig_face) as orig_face_sum
,count(*)
from table_name
where column='xxxx'
group by mstr_date
order by mstr_date

I never tried this until now. Very interesting.

Closing a Swing Application

Just calling toplevel.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); is not good enough.
There could be other frames still alive, esp if you are using a third party library.
So your JVM is still running. Here is how you fix it..
Get all the frames that are currently running and dispose each of them

Frame[] frames = toplevel.getFrames();

for(int i=0;i
Frame frame = frames[i];
frame.dispose();
}
-Sonu

Excel logical if

Compare two columns and find out whats different.

Lets say you would like to compare col A1 and F1. Here is the logical if condition in excel you would use...

=IF(A1=F1,"","***")

The column would print *** when the values in A1 and F1 are different.

-Sunny

useful cvs commands

If you made local changes and if you like to diff the latest version in repository,
cvs -Q diff -c StormAccrual.pl

Unix file processing

This command is very useful to analyze data files

cut -d ' -f |

single col :
cut -d '|' -f1 uncl_sims_audit_clr_code_null.txt | sort|uniq

multiple cols
cut -d '|' -f1-2 uncl_sims_audit_clr_code_null.txt | sort|uniq


enjoy..!

Passing arrays to perl subroutines

Finally when we receive thes values in@_ variable then we can not recognize if we had passed one array or two value arraysbecause finally it is getting merged into one.
o

If you want to work with and identify the individual lists passed to Perl, then you need to use references:

(@listc, @listd) = simplesort(\@lista, \@listb);

The leading \ character tells Perl to supply a reference, or pointer, to the array. A reference is actually just a scalar, so we can identify each list by assigning the reference to each array within our subroutine. Now you can write your subroutineas follows:

sub simplesort
{
my ($listaref, $listbref ) = @_;

# De-reference the array list
my (@lista) = @$listaref;
my (@listb) = @$listbref;
# Now you can play with both arrays.
}

Syntax of inner join

select * from foo

inner join (

select 5 as bar

union all select 6

union all select 7

) as x on foo.bar = x.bar