Oracle: Join Example

I have a table TEST in oracle which stores data for some users on monthly basis, I need to fetch the data in such a way that "all data for current month with the entry of their last month record for a particular column." Here is the table details and query:

CREATE TABLE DATETEST (ID NUMBER(4) NOT NULL, NAME VARCHAR2(10), BUDGET NUMBER(7), TDATE DATE);

INSERT INTO DATETEST VALUES (1, 'test1', 100, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 200, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 300, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (4, 'test4', 200, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (5, 'test5', 300, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (1, 'test1', 200, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 100, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 400, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 200, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 100, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (4, 'test4', 100, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));

select * from DATETEST;

# This Month's entry
select * from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0);

# Last Month's entry
select * from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1);

# Common users for current and last month and their budgets for current and last months
select A.ID, NAME, A.BUDGET, B.BUDGET from DATETEST A,
(select ID, BUDGET from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1)) B
where A.ID=B.ID and TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0) ;

# Query which displays data for all users with current month entry and thier last month record
select A.ID, NAME, A.BUDGET, NVL (B.BUDGET, 0)
from DATETEST A left join
(select ID, BUDGET from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1)) B
on A.ID=B.ID where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0) order by id asc;

sed: How to Escape Forward Slash

As you may know, sed performs a search and replace with this command:

sed s/seacrh pattern/replacement pattern/g file list

forward slash ("/") is used as part of the regular expression to separate the command options and search text. What if your search/replace pattern itself includes forward slash character.

I wanted to replace /usr/local/bin to /usr/local/dev, I escaped forward slashes with backward slash and my sed command looked like this:

sed -i -e 's/\/usr\/local\/bin/\/usr\/local\/dev/g' testfile.txt

Unfortunately, sed didn’t work and gave me this error:

sed: -e expression #1, char 27: unknown option to `s'

Then On googling, I discovered an exciting thing about sed. In the regular expression, it’s not necessary to delimit the find and replace texts and search options with the forward slash ‘/’ character. We can use any character to delimit the expression.

So I changed my command a bit, used @ character to delimit the expression and then it worked.

sed -i -e s@\/usr\/local\/bin@\/usr\/local\/dev@g testfile.txt

use variable refeences in awk

I need to compare 1st column of user.csv with value, it works well with following

value=2
awk -F':' '$1==$value {print}' user.csv

now I have a case where column number is also a variable for me, in that case, the solution would be:

value=2
column=1
awk -F':' ''\$$column==$value' {print}' filename

1. You need to put backslash (\) before first $
2. You need to put comparison in single quote

Retrieve web pages using perl

Here is the sample script to fetch web pages using perl.

#!/usr/local/bin/perl -w
use strict;
use LWP 5.64;
use URI;

my $browser = LWP::UserAgent->new;
$browser->cookie_jar({});

my @browserHeaders = (
'User-Agent' => 'Mozilla/4.0; (compatible; MSIE 6.0; Windows NT 5.1; en-US)',
'Accept-Language' => 'en-US',
);

my $response;
$response = $browser->get("http://vishnuagrawal.blogspot.com",@browserHeaders);

if ($response->is_success) {
print $response->content;
} else {
print $response->error_as_HTML, "\n";
print $response->status_line, "\n";
}

print $response->code(), "\n";
print $response->message() , "\n";
print $response->header('content-type'), "\n";

my $url = URI->new("http://www.softwareqa.com");
$response = $browser->get($url,@browserHeaders);

print $response->content;

Find the day diff between two days

Following is the shell script solution for getting number of days between two dates:

#/bin/sh
date1=`date +%s -d $1`
date2=`date +%s -d $2`
diff=`expr $date2 - $date1`
diff_days=`expr $diff / \( 60 '*' 60 '*' 24 \)`
echo $diff_days


[vishnu@ shell]# ./daydiff.sh 2010-08-02 2010-09-15
44
[vishnu@ shell]#

Here is the perl solution for the same:

#!/bin/perl
use strict;
use warnings;
use Time::Local;

my $date1 = $ARGV[0];
my $date2 = $ARGV[1];

my @formatted_date1 = split(/-/, $date1);
my @formatted_date2 = split(/-/, $date2);

my $local_date1 = timelocal(0, 0, 0, $formatted_date1[2], $formatted_date1[1], $formatted_date1[0]);
my $local_date2 = timelocal(0, 0, 0, $formatted_date2[2], $formatted_date2[1], $formatted_date2[0]);

my $diffSeconds = $local_date2- $local_date1;
my $diffDays = $diffSeconds / (60 * 60 * 24);
print "Day diff :: $diffDays\n";

[vishnu@ shell]# ./daydiff.pl 2010-08-02 2010-09-15
Day diff :: 43
[vishnu@ shell]#

Here is modified version of above shell script, if you want to find out time difference in seconds

#!/bin/bash

d1=`date +%s -d "$1"`
d2=`date +%s -d "$2"`
((diff_sec=d2-d1))
echo "Diff Seconds : $diff_sec"

[vishnu@ shell]#./date.sh "2011-07-19 22:44:34" "2011-07-20 02:04:14"
Diff Seconds : 11980
[vishnu@ shell]#

Dump cpu and memory usage of a process

Here is the automated shell script which can dump the memory and cpu usage of a process. Helpful in cases when you want to monitor a process for a long time.

#!/bin/bash

if [ "$#" -ne 1 ]; then
echo "Usage ... [$0 <pid>]"
exit
fi

pid=$1

# maxRunTime is used to control the script, so that it run only for specified number of seconds
maxRunTime=72000
# sleepTime is used to sleep for specified seconds, to take the memory dump again
sleepTime=120

logFile=${pid}_process.log
commandFile=${pid}_command.log

`ptree $pid >> $commandFile`;
`pargs $pid >> $commandFile`;

currentRunTime=0

while [ $currentRunTime -lt $maxRunTime ]
do
time=`date +%F::%X | tr -d '[\n]'`
line=`ps -eo pid,pcpu,vsz | grep $pid`;
echo "$time $line" >> $logFile
sleep $sleepTime
currentRunTime=`expr $currentRunTime + $sleepTime`
echo "Execution Time : ${currentRunTime} seconds [max ${maxRunTime}]"
done;

Flash Player debug version on New Google Chrome

If you install the latest version of Google Chrome (mine is 5.0.375.125), flash player debug version do not work in it; though the same debug player is working fine in Firefox3. Reason is that Chrome now comes pre-installed with the Flash player which get used by default, despite whatever other version you might have installed. It always use its original version. To change this, follow below steps:

- Open Google Chrome
- Type about:plugins in the url, - It will show the list of all the plugins
- Click on the "Disable" link of "Shockwave Flash" plugin

It will not disable flash, but just the built in version. Now the chrome will use the system-wide flash player version (probably the debug version).

Unix: Get a file's name, extension and directory name

file="/usr/local/bin/test.sh"

# get extension; everything after last '.'
ext=${file##*.}
# will return 'sh'

# everything after last '/'
basename=${file##*/}
# will return 'test.sh', similar to 'basename'

# everything before last '/'
basename=${file%/*}
# will return '/usr/local/bin', similar to 'dirname'

# back one directory
basename=${file%/*/*}
# will return '/usr/local'

Share files between local and remote computer using Remote Desktop Connection

One of the most common tasks you'll need to do when using an Remote Desktop Connection is transfer a file from your local machine to the remote machine that you're logged into. During a Remote Desktop session, you can gain easy access to your local disk drives on the remote computer so that you can transfer files between these two systems in the same way that you copy files from a network share.



1. Click Start ->All Programs->Accessories->Remote Desktop Connection.
2. Click Options, and then click the Local Resources tab.
3. Click Disk Drives and then click Connect.
4. When you get logged into the remote box, open My Computer and you'll see that all the local drives will show up as mapped disk drives.
5. Now you can transfer your file as you do in local system.

Remove a file whose name begins with "-"

Due to a bug in our product the log file was being created with the name "-server.log" (instead of "host-server.log"). Once the issue was fixed, i was trying to remove the file. Tried single quote/double quote/escape, but nothing worked. Finally after googling, found solution for it. Since the file name begins with the "-", all the unix commands treats the file name itself as a parameter to the command. To make it work put -- or ./ before the file name.

example:
rm -- -server.log
rm ./-server.log