Oracle Inner Join Syntax : 4 different ways.. Thanks to CarlosDLG ..

Oracle Inner Join Syntax : 4 different ways

Posted in sql & plsql | Leave a comment

Please check the below links you can get the common things related to Oracle Apex

Thanks to Enkitec…

https://www.enkitec.com/apex/f?p=15300:60400:0::NO:::

Posted in Apex | Leave a comment

sort given string alphabetic

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))
order by 1;

Posted in sql & plsql | Leave a comment

<<>>

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           3
vino        4
vinot       5
vinoth     6

Posted in sql & plsql | Leave a comment

Please check this below query to get the calender from 1-jan-15 to 31-dec-9999

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’)
)
group by to_char(rw,’month yyyy’),to_char(rw,’iw’)
order by month,week
)
order by to_date(month,’month yyyy’),to_number(week)

Posted in sql & plsql | Leave a comment

SQL Developer Quick Tip – Pin Query Result Sets and Plans

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

Posted in sql & plsql | Leave a comment

Hi,

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.!

Posted in sql & plsql | 1 Comment