Home » SQL & PL/SQL » SQL & PL/SQL » Strange query...
icon5.gif  Strange query... [message #188955] Tue, 22 August 2006 10:08 Go to next message
josh1977
Messages: 4
Registered: August 2006
Junior Member
Hi,

I'm newbie.
I use Oracle9i Enterprise Edition Release 9.2.0.6.0
I'm trying to run this query but It works in a strange way.

QUERY1 :

SELECT DISTINCT myvalue
FROM mytable
WHERE substr(col1, 1, 6) = 'AAA-00'

descr mytable :

myvalue number
col1 varchar2(2000)
col2 ...
col3 ...


If I run the QUERY1 from TOAD it works.
If I run the QUERY1 from Sql Plus it doesn't work and It returns any record.

Someone can help me ???

[Updated on: Tue, 22 August 2006 10:13]

Report message to a moderator

Re: Strange query... [message #188957 is a reply to message #188955] Tue, 22 August 2006 10:15 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Do you get any error message from SQL*Plus or just no records?
Re: Strange query... [message #189045 is a reply to message #188957] Wed, 23 August 2006 00:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
did you by any chance insert the records in the same TOAD-session and forgot to commit? That would explain why the records were not visible in sqlplus.
If not, please provide a test-script in which you replay your situation.
Re: Strange query... [message #189065 is a reply to message #188957] Wed, 23 August 2006 01:52 Go to previous messageGo to next message
josh1977
Messages: 4
Registered: August 2006
Junior Member
Hi !

I receive no records in SQL PLUS.
mytable is populated before I run the sql query... (I'm sure)

Can it depend by the name of the fields (col1 , col2) with a number ???

I've tried to remove from my query this :

WHERE substr(col1, 1, 6) = 'AAA-00'

and it works on SQLPlus and on Toad.
But if I do a "substr" instruction or "instr" instruction... It doesn't work on Sql Plus.

Thanks.
Re: Strange query... [message #189077 is a reply to message #189045] Wed, 23 August 2006 02:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Wed, 23 August 2006 07:13


If not, please provide a test-script in which you replay your situation.

Re: Strange query... [message #189078 is a reply to message #189065] Wed, 23 August 2006 02:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not that I don't believe you, but...

Can you cut and paste from SQL*Plus the following:

1) The results of the query
SELECT DISTINCT myvalue 
FROM mytable ;


2) The results of the query
SELECT DISTINCT myvalue 
FROM mytable 
WHERE substr(col1, 1, 6) = 'AAA-00';
Re: Strange query... [message #189080 is a reply to message #188955] Wed, 23 August 2006 02:32 Go to previous messageGo to next message
josh1977
Messages: 4
Registered: August 2006
Junior Member
FIRST QUERY :

SQL> SELECT DISTINCT myvalue
2 FROM mytable;

myvalue
-------------
1
2
3
4
5
6
7
8
9
10
11

myvalue
-------------
12
13
14
15
16
17
18
19
20
21
22

myvalue
-------------
23
24
25
26
27
28
29
30
31
32
33

myvalue
-------------
34
35
36
37


Selezionate 38 righe.

SQL> spool off
Re: Strange query... [message #189083 is a reply to message #188955] Wed, 23 August 2006 02:40 Go to previous messageGo to next message
josh1977
Messages: 4
Registered: August 2006
Junior Member
SECOND QUERY :

SQL> SELECT DISTINCT myvalue
2 FROM mytable
3 where substr(COL1,1,6)= 'AAA-00';

myvalue
-------------
2

SQL> spool off

Embarassed

Mad Mad Mad Mad Mad Mad Mad Mad

NOW IT WORKS !!!!

but...it is strange ... I haven't changed nothing...

...
Re: Strange query... [message #189111 is a reply to message #189083] Wed, 23 August 2006 04:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
josh1977 wrote on Wed, 23 August 2006 09:40

but...it is strange ... I haven't changed nothing...


Hate to say it, but you did. Oracle isn't some moody kind of database returning rows at will.
Somehow I still suspect you forgot a commit somewhere..
Re: Strange query... [message #189165 is a reply to message #189083] Wed, 23 August 2006 08:27 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
josh1977 wrote on Wed, 23 August 2006 03:40



but...it is strange ... I haven't changed nothing...

...


So you DO admit that you changed something. And this just confirms Frank's assertion that you did. Double-negative Wink
Previous Topic: Hide empty cells in query result
Next Topic: Trigger Prob.
Goto Forum:
  


Current Time: Thu Dec 08 18:35:41 CST 2016

Total time taken to generate the page: 0.07372 seconds