DECODE inside IN clause Oracle SQL

Requirement : Consider Emplyoee table emp which has Emplyee number and Deparment number columns.

Table : emp

empno  deptno
---------------------
1       10
2       20
3       20
4       10
5       30

Based on some input parameter var that isnot part of any table we wish to select employees of specific Departments.

If value of var = 1 then
select dept 10 employees
empno 1 and 4

If value of var = 2 then
select dept 20 and 30 employees.
empno 2, 3 and 5

var variable has no significance with respect to Employee data

Solution :

[sourcecode language="sql"]
SELECT empno,deptno
FROM emp
WHERE deptno IN (DECODE(:var,1,10),decode(:var,2,20),decode(:var,2,30))
[/sourcecode]

when var value is 1 then
both second and third decode returns null, and evaluates to false for all rows, and first decode returns 10. The where clause translates into something like
where deptno = 10 or deptno = null or deptno = null
So, it returns deptno 10 employees only.

Similarly, for var value of 2 the where clause translates into
where deptno = null or deptno = 20 or deptno = 30
and returns deptno 20 and 30 employees.

Oracles demo mail Package for sending mail

Oracles Demo mail package was available on OTN site previously, but this time when I searched I got 404 error. So, I thought of keeping a copy for future references.

Its a wrapper program to send mail using pl/sql. It hides the low level SMTP protocol details.

gist link of package

Unbiased Performance Evaluation

Padovan String Sequence Problem

Problem Find number of occurrences of input string in nth Padovan String P(n)
where P(0) = 'X', P(1)='Y' and P(2)='Z'
Input Parameters: needle string str to search for and number n
Conditions to handle:
if number n is >= 40 return -1
if string str has any other characters then X,Y and Z return -1

Solution

Solved this problem for preliminary round of one programming contest.

PadovanString.java

[code language="java"]
public class PadovanString {

public int stringOccurrences(int n, String str){
if (n >= 40)
return -1;
if (str.replaceAll("X|Y|Z", "").length() > 0)
return -1;
String res= pad(n);

/* print nth Padovan String
System.out.println(res);*/

/*replace search string with null and caculate number of occurences*/
return (res.length() - res.replaceAll(str, "").length())/(str.length());
}

public String pad(int n){

if (n == 0) return "X";
if (n == 1) return "Y";
if (n == 2) return "Z";
else return pad(n-2) + pad(n-3);

}
}
[/code]

Test.java

[code language="java"]
public class Test {
public static void main(String []args)
{
PadovanString p = new PadovanString();
System.out.println(p.stringOccurrences(20,"YZ"));
}
}
[/code]



Also, Length of Nth padovan string = Nth Padovan number

Urban dictionary and Youtube search for cool slang's and their usage using Python

Urban dictionary is a user maintained dictionary of slang's. A thesaurus search for a word gives all the related slang's, related words could be antonym also.
Youtube, not long ago, started video comment search which is still in beta. Here, comments on all videos are searched for entered search term.

Requirement : To get related slang's from urban dictionary for a particular word, and then query youtube usage of those slang's.

[sourcecode language="python" wraplines="false"]
from BeautifulSoup import BeautifulSoup,NavigableString
import nltk
import os
import re
import urllib2
import webbrowser
import time

def get_soup(url):
#get soup object for the url
try:
page = urllib2.urlopen(url)
except urllib2.URLError, e:
print 'Failed to fetch ' + url
raise e

try:
soup = BeautifulSoup(page)
except HTMLParser.HTMLParseError, e:
print 'Failed to parse ' + url
raise e
return soup

def get_related(word):
word_list=[]
print 'Fetching related words for '+word+'........'
soup=get_soup('http://www.urbandictionary.com/thesaurus.php?term='+word)
for td in soup.findAll('td', {'class':'word'}): #each row
rel_word=td.find('a').contents[0].encode()
print rel_word
word_list.append(rel_word)
return word_list

def get_comment(word,npages):

print 'Fetching comments for '+word+'........'
comments =[]
for i in range(1,npages+1):
soup= get_soup('http://www.youtube.com/comment_search?q='+str(word)+'&ld=1&comment_only=1&hl=en&so=pagerank&page='+str(i))
for span in soup.findAll('span', {'class':'comment-result-comment'}): #each row
comment =''
for text in span.findAll(text=True):
comment = comment+ ' '+ text
comment=re.sub('[ ]+|\n|\r',' ',comment.strip())
comment=re.sub('^[0-9]+[ ]+','',comment)
comments.append(comment.encode('utf-8'))
time.sleep(6)
return comments

def main():
word='spooky'
npages=2
word_list=get_related(word)
#word_list = ['spooky']
file1=open('word_list','w')
for w in word_list:
for comment in get_comment(w,npages):
file1.write(comment+'\n')
file1.close()

if __name__ == '__main__':
main()
[/sourcecode]

Sentiment analysis using Naive Bayes Algorithm

Experimented with simple Naive Bayes for sentiment classification.

Naive Bayes code is available  here chatper6/docclass.py and training data is available here

Changed the getwords() function in docclass.py

- to remove special characters like single-quote, comma, full stop from text
- to split based on white spaces instead of non word character because it ignored emots with non word   character split and
- included nltk stopwords corpus check.

[sourcecode language="python"]

def getwords(doc):
doc=re.sub('\.+|,+|!+|\'','',doc)
splitter=re.compile('\\s+')
#print doc
# Split the words by non-alpha characters
words=[s.lower().strip() for s in splitter.split(doc)
if s.lower().strip() not in nltk.corpus.stopwords.words('english') ]
print words
# Return the unique set of words only
return dict([(w,1) for w in words])
[/sourcecode]

For training data, converted ';;' separated data file to '\t' separated file because csv.reader() function
was not accepting  two symbol delimiters.

Changed sampletrain function to train classifier on training data file "testdata.manual.2009.05.25".

[sourcecode language="python"]
def sampletrain(cl):
read = csv.reader(open('pos 1', 'rb'), delimiter='\t')
cnt = 1
for row in read:
if row[0] == 0:
sent = 'bad'
else:
sent = 'pos'
data = row[5]
cl.train(data,sent)
cnt = cnt+1
print cnt
[/sourcecode]

Socially adept programmers

The programmer stereotype as described in personality traits of great programmer

The stereotypical programmer is a shy young man, either scrawny or overweight, who works by himself in an 8’x8’ cubicle in a bigger room of dozens cubicles, each holding someone just like him. He intensely concentrates on writing cryptic instructions to coax a computer to do what is needed. He  devotes his evenings, weekends, and summers to work. He has no social life and any hobbies he may have resemble his work. In some companies he is regarded as an indispensable genius; in others he is tolerated as an eccentric artist. (McConnell, 1999)

Programmers who value social image present themselves so to conform to a perception of society's preferred type of personality. Some ways in which they manipulate  perception of society are

• When asked to stay little longer they decline by saying that they have some personal commitments, they need to spend time with family, when, in fact they will be working on some open source project or breaking into high profile gov network.

• They don't use Facebook or twitter often, so they develop a program that autonomously posts status and comment on others feed periodically(using some NLP and ML techniques) to show that they spend a lot of time on social networks and are social.

• They are aware that in social conversation what matters is not the correctness of the argument, but how much laughter it provokes and how much interesting it is.

• They don't use IT jargon in social conversations, even if they know everything about them and in fact, make fun of people who use them(making fun of others is most frequently used technique in social conversations).

• They identify themselves with hippie programmers instead of nerd programmers.

• They have a girlfriend or at least this is what they tell to others.

• They never tell programming as their hobby even if programming is at the top on the list.