Author Archives: vinothrmk

Oracle Inner Join Syntax : 4 different ways

select listagg(substr(‘AMERIKA’,level,1),”)within group (order by ‘AMERIKA’) from dual connect by level <= length(‘AMERIKA’) order by 1 select listagg(substr(txt,lvl,1),”) within group (order by txt) a from (select txt ,level lvl from (select ‘AMERIKA’ txt from dual) connect by level <= length(txt)) … Continue reading →

SELECT SUBSTR(:TEXT,1,LENGTH(:TEXT)-LEVEL+1) OUTPUT,LENGTH(:TEXT)-LEVEL+1 AS L FROM DUAL CONNECT BY LEVEL<=LENGTH(:TEXT) order by l; Result will be : v               1 vi             2 vin           … Continue reading →

select month,lag(sun,1) over(order by to_date(month,’month yyyy’),to_number(week)) “sun”,”mon”,”tue”,”wed”,”thu”,”fri”,”sat” from ( select to_char(rw,’month yyyy’) month, to_char(rw,’iw’) week, max(decode(to_char(rw,’d’),’1′,lpad(to_char(rw,’fmdd’),2))) “sun”, max(decode(to_char(rw,’d’),’2′,lpad(to_char(rw,’fmdd’),2))) “mon”, max(decode(to_char(rw,’d’),’3′,lpad(to_char(rw,’fmdd’),2))) “tue”, max(decode(to_char(rw,’d’),’4′,lpad(to_char(rw,’fmdd’),2))) “wed”, max(decode(to_char(rw,’d’),’5′,lpad(to_char(rw,’fmdd’),2))) “thu”, max(decode(to_char(rw,’d’),’6′,lpad(to_char(rw,’fmdd’),2))) “fri”, max(decode(to_char(rw,’d’),’7′,lpad(to_char(rw,’fmdd’),2))) “sat” from ( select (to_date(‘1-jan-0001′,’dd-mon-yyyy’)-1)+level rw from dual connect by level<=add_months((to_date(‘1-jan-0001′,’dd-mon-yyyy’)),12*9999)-to_date(‘1-jan-0001′,’dd-mon-yyyy’) ) … Continue reading →

Query Cost In SQL DEVELOPER Please check this link for find the query cost using Sql Developer.

This is Vinoth, just want to share my knowledge with friends and others, for that i have just created this blog in WordPress.. Just beginning.!