Visual SQL Join

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

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.

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]

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

TIMESTAMP datatype explanation

Good article about oracle dates data types