Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Non equi join
thiyagusham
post Apr 14 2012, 09:25 AM
Post #1


Advanced Member
***

Group: Members
Posts: 78
Joined: 16-March 12
From: Chennai from India
Member No.: 46,939





SQL> select * from x;

NAME MARKS

sam 91
chris 72
rose 78
sona 97
maya 55

-------------------


SQL> select * from y;

GRADE MIN MAX
A 96 100
B 91 95
C 76 90
D 60 75
E 40 59

----------

see the output reocord sona not disclosed. I tried several times couldn't find out.
what's the specific reason why oracle ignored specific record.???


SQL> select x.name,x.marks, y.grade
2 from x ,y where
3 x.marks between y.min and y.max;

NAME MARKS GRADE

sam 91 B
rose 78 C
chris 72 D
maya 55 E

-----------------------

SQL> desc x;

Name Null? Type

NAME VARCHAR2(15)
MARKS VARCHAR2(10)

SQL> desc y;

Name Null? Type

GRADE VARCHAR2(15)
MIN VARCHAR2(10)
MAX VARCHAR2(5)


All are varchar data type. Too much confusions.
IS there any specific reason to ignore any record ??? (Sona RECORD)
How oracle handles between number and varchar2 data type.
Clearly It indicates data type is problem
can any one explain this ???
Go to the top of the page
 
+Quote Post
HAL9000
post Apr 14 2012, 01:06 PM
Post #2


Advanced Member
***

Group: Members
Posts: 876
Joined: 25-September 07
Member No.: 12,336



"Clearly It indicates data type is problem"

So, why guess?

Redo your test, defining the data types as number, as they should be.

Why are you defining these varchar as different sizes?

CODE
MIN VARCHAR2(10)
MAX VARCHAR2(5)


Are you on drugs?
Go to the top of the page
 
+Quote Post
paulogervasio
post Apr 14 2012, 09:55 PM
Post #3


Advanced Member
***

Group: Members
Posts: 69
Joined: 5-August 11
From: Brazil
Member No.: 45,669



Try do it:

select max from y order by max DESC;

the result will be:

99
88
200
10000
100
10
1


As the column is VARCHAR, the sort will be done like a text sort ( 0,1,2,3..,9,A,B...Z)
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th April 2014 - 07:12 PM