Skip to Main Content

Infrastructure Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle Linux vs. redhat 5.2 - Which one do I go with????

674757Dec 11 2008 — edited Dec 12 2008
Which one should it be? Can anyone shed some light on this decision?

Your thoughts...

-Rob

Comments

MaheshKaila
Try this one
WITH test AS (SELECT '1' id
              FROM DUAL
              UNION ALL
              SELECT '2' id
              FROM DUAL
              UNION ALL
              SELECT '4' id
              FROM DUAL
              UNION ALL
              SELECT '5' id
              FROM DUAL
              UNION ALL
              SELECT '6' id
              FROM DUAL)
SELECT TO_CHAR (l)
FROM (SELECT LEVEL l
      FROM DUAL
      CONNECT BY LEVEL <= (SELECT MAX (id)
                           FROM test))
MINUS
SELECT id
FROM test 
Regards,
Mahesh Kaila
ORA_AJ
Dear Mahesh


In my case Number of records is not fixed i mean to say its not neccesary that data is there for 1-6 only.It can be upto 100..


--Ajay Sharma
MaheshKaila
SELECT TO_CHAR (l)
FROM (SELECT LEVEL l
      FROM DUAL
      CONNECT BY LEVEL <= (SELECT MAX (id)
                           FROM <YOUR_TABLE>))
MINUS
SELECT id
FROM <YOUR_TABLE>
- Mahesh Kaila
Karthick2003
What if i delete 4,5 and 6 will your query find it?
Aketi Jyuuzou
I like recursive with clause B-)
Hehe I used PostgreSQL8.4 because I do not have Oracle11gR2 :D
WITH recursive t(Val) AS (
values(1),(2),(4),(5),(6),(12)),
rec(Val,LeadVal) as(
select Val+1,LeadVal
  from (select Val,Lead(Val) over(order by Val) as LeadVal
          from t) tmp
 where Val+1 < LeadVal
union all
select Val+1,LeadVal
  from rec
 where Val+1 < LeadVal)
select Val from rec;

 val
-----
   3
   7
   8
   9
  10
  11
MaheshKaila
WITH test AS (SELECT '1' id
              FROM DUAL
              UNION ALL
              SELECT '2' id
              FROM DUAL
              UNION ALL
              SELECT '4' id
              FROM DUAL
              UNION ALL
              SELECT '5' id
              FROM DUAL
              UNION ALL
              SELECT '6' id
              FROM DUAL)
SELECT TO_CHAR (l)
FROM (SELECT LEVEL l
      FROM DUAL
      WHERE LEVEL >= (SELECT MIN (id)
                 FROM test)
      CONNECT BY LEVEL <= (SELECT MAX (id)
                           FROM test))
MINUS
SELECT id
FROM test
ORA_AJ
Hi mahesh

I am using Oracle 8.1.5.0 and whenever i uses your query containing Connect by level I gets an errror saying Ora-01436 : CONNECT BY loop in user data..

Any comments about this.

--Ajay Sharma
JustinCave
CONNECT BY LEVEL was not available in 8.1.5. In 8.1.5, the simplest solution would be to select a ROWNUM from a table that has more rows than the TEST table. Assuming that DBA_OBJECTS is sufficiently large
SELECT rownum id
  FROM dba_objects
MINUS
SELECT id
  FROM test
As an FYI, if you're using an ancient version of Oracle like 8.1.5, it's helpful to mention that up front. There have been a lot of advances in the 8 major releases since then.

Justin
788759
Mahesh Kaila wrote:

Try this one



<PRE class=jive-pre><CODE class="jive-code jive-java">WITH test AS (SELECT <FONT color=navy>'1'</FONT> id

FROM DUAL

UNION ALL

SELECT <FONT color=navy>'2'</FONT> id

FROM DUAL

UNION ALL

SELECT <FONT color=navy>'4'</FONT> id

FROM DUAL

UNION ALL

SELECT <FONT color=navy>'5'</FONT> id

FROM DUAL

UNION ALL

SELECT <FONT color=navy>'6'</FONT> id

FROM DUAL)

SELECT TO_CHAR (l)

FROM (SELECT LEVEL l

FROM DUAL

CONNECT BY LEVEL &lt;= (SELECT MAX (id)

FROM test))

MINUS

SELECT id

FROM test

</CODE></PRE>



Regards,

Mahesh Kaila
Where can I find the related documentation? It has been solved, but why's that? Many thanks to your <A class=bodylinkwhite href="http://www.software-to-convert.com/myspace-video-conversion-software/myspace-video-to-iriver-lplayer-software.html"><FONT face=tahoma,verdana,sans-serif color=#000 size=1>explanation</FONT></A>!
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 9 2009
Added on Dec 11 2008
2 comments
942 views