Friday, August 5, 2011

Visual SQL Join

Nice SQL cases you'll need them in your real work and business need

Saturday, April 23, 2011

Are You Change Enabled?

I read a nice article on Oracle Magazine January-2011 Issue, the article title is "Are you Change Enabled?"

I will list some quotes I liked from the article below:-

In change-enabled organizations, Mistakes often produce revelations.

an executive sponser at OAUG said “If we had not made the original mistake, we never would have fully explored the available profile options for the feature in question. We would not have found a better fit to our desired operation. And we would not be where we are today.”

You have to be willing to assume a reasonable level of discomfort for progress to be made.

Thursday, April 21, 2011

Why is TO_CHAR function adding a space?

A user at my work requested to generate auto number concatenated with '/' and the current year, as follow:- 001/2011,002/2011 ...etc

I used the following to do that

SELECT TO_CHAR(1,'000')||'/'||to_char(sysdate,'RRRR') sn
FROM dual

SN
---------
001/2011


But I noticed that the output serial has an extra leading space, then I decided to add TRIM to the code as follow:-

SELECT TRIM(TO_CHAR(1,'000')||'/'||to_char(sysdate,'RRRR')) sn
FROM dual

SN
---------
001/2011


Till now the user requirement is done, but I didn't understand why TO_CHAR returned an extra leading space

I found the answer in this link

the reason for the extra leading space is for the potential minus sign as shown below

select '['||to_char(1, '000')||'/'||to_char(sysdate,'RRRR')||']' sn1, 
'['||to_char(-1, '000')||'/'||to_char(sysdate,'RRRR')||']' sn2, 
'['||to_char(1,'FM000')||'/'||to_char(sysdate,'RRRR')||']' sn3 
from dual
/

SN1         SN2         SN3
----------- ----------- -----------
[ 001/2011] [-001/2011] [001/2011]

Monday, April 11, 2011

TRANSLATE function, Something new I learned


-- To remove all spaces from a string

SELECT TRANSLATE('abc dda XbY a b c z','1 ','1') test

FROM dual;

TEST
-------------
abcddaXbYabcz

-- in the same manner to remove any abc from this string
SELECT TRANSLATE('abcddafghabcp','1abc ','1') test
FROM dual;

TEST
------
ddfghp

-- Also to replace any abc with xyz
SELECT TRANSLATE('abcddafghabcp','1abc ','1xyz') test
FROM dual;

TEST
-------------
xyzddxfghxyzp

Sunday, March 27, 2011