Monday, February 28, 2011

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;

Tuesday, February 22, 2011

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