Home » SQL & PL/SQL » SQL & PL/SQL » No rows selected but data exists (oracle 10g)
No rows selected but data exists [message #417693] Tue, 11 August 2009 03:20 Go to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
I have this query:

select lastname, firstname, birthday from table where lastname = 'ANG';

the results:
LASTNAME
ANG
ANG
ANG
ANG

FIRSTNAME
ANN
JOY
MARIE
CARL

BIRTHDAY
01-JAN-90
02-FEB-91
03-MAR-92
04-APR-93

and when i execute this query, the result is NO ROWS SELECTED.

select id from table where lastname = 'ANG' and firstname = 'ANN' and birthday = '01-JAN-90';

How come that it displays when i select all the lastname with 'ANG'? Do you think there is a space when someone encoded the name ANN? I also had a different query with a where clause of lastname and birthday only of ann ang. But still, the result is no rows selected.

Thanks!
Re: No rows selected but data exists [message #417695 is a reply to message #417693] Tue, 11 August 2009 03:30 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
 birthday = '01-JAN-90'

I bet the issue is here.

Show the results of
select id, to_char(birthday, 'dd-mon-yyyy hh:mi:ss') from table ;

[Updated on: Tue, 11 August 2009 04:24]

Report message to a moderator

Re: No rows selected but data exists [message #417698 is a reply to message #417693] Tue, 11 August 2009 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: No rows selected but data exists [message #417700 is a reply to message #417695] Tue, 11 August 2009 03:35 Go to previous messageGo to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
No, i dont think so.

I query the remaining 3 [joy, marie, carl] and i got the correct id for them.
Re: No rows selected but data exists [message #417701 is a reply to message #417693] Tue, 11 August 2009 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I do.
We're not really interested in the id here, we're interested in the birthday. Also what are the datatypes of the columns

[Updated on: Tue, 11 August 2009 03:43]

Report message to a moderator

Re: No rows selected but data exists [message #417709 is a reply to message #417700] Tue, 11 August 2009 04:08 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
shoowaruno wrote on Tue, 11 August 2009 09:35
No, i dont think so.

I query the remaining 3 [joy, marie, carl] and i got the correct id for them.

Suit yourself. You have come here looking for assistance because you are stuck (on a pretty basic query). I have asked you to supply some information. You have decided that you know better than me, and so do not supply the information that I have requested. I've got better things to do with my time than to try to eke out the required info from you. But I wish you good luck in getting the answer that you think you need. Genuinely.
Regards.
Re: No rows selected but data exists [message #417711 is a reply to message #417701] Tue, 11 August 2009 04:09 Go to previous messageGo to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
firstname VARCHAR(60)
lastname VARCHAR(60)

birthday DATE


Re: No rows selected but data exists [message #417713 is a reply to message #417711] Tue, 11 August 2009 04:13 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Why do you refuse to post results of query that pablolee suggested?

select id,to_char(birthday,'dd-Mon-yyyy hh24:mi:ss') from table

[Updated on: Tue, 11 August 2009 04:15]

Report message to a moderator

Re: No rows selected but data exists [message #417714 is a reply to message #417695] Tue, 11 August 2009 04:16 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
pablolee wrote on Tue, 11 August 2009 09:30

Show the results of
select id, to_char(birthday, 'dd-mon-yyyy hh:mi:ss') from table ;


Re: No rows selected but data exists [message #417716 is a reply to message #417711] Tue, 11 August 2009 04:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To enhance @Pablolee's request - could you post the output to the follwing queries:
select id, to_char(birthday, 'dd-mon-yyyy hh:mi:ss') from table ;

select id, to_char(birthday, 'dd-mon-yyyy hh:mi:ss') from table where lastname = 'ANG' and firstname = 'ANN';

select 'X'||lastname||'X' lastname, 'X'||firstname||'X' firstname
from table
where rtrim(ltrim(lastname)) != lastname
or rtrim(ltrim(firstname)) != firstname;


Between them, these should get to the root of the problem.
Re: No rows selected but data exists [message #417717 is a reply to message #417714] Tue, 11 August 2009 04:27 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
[whisper] pssst, thanks for correcting my "deliberate" mistake guys Wink[/whisper]
Re: No rows selected but data exists [message #417764 is a reply to message #417693] Tue, 11 August 2009 08:01 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
shoowaruno wrote on Tue, 11 August 2009 04:20

select lastname, firstname, birthday from table where lastname = 'ANG';

the results:
LASTNAME
ANG
ANG
ANG
ANG

FIRSTNAME
ANN
JOY
MARIE
CARL

BIRTHDAY
01-JAN-90
02-FEB-91
03-MAR-92
04-APR-93



Nice faking of your output. No way does Oracle give output that looks like that with that query. Why is it so hard to show what actually was output? Search Google for "cut and paste."
Re: No rows selected but data exists [message #417808 is a reply to message #417764] Tue, 11 August 2009 10:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
because we don't use sqlplus, and we do know how to copy data from <insert GUI tool here> column by column, but not the entire resultset?
Re: No rows selected but data exists [message #417821 is a reply to message #417711] Tue, 11 August 2009 11:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
@shoowaruno,
One Thumb rule for you:
Never use only '01-JAN-90' without to_date, while
1. inserting data into date columns
2. in where clause with date columns

Try use to_date('01-JAN-90','DD-MON-RR') where ever you can.

By
Vamsi
Re: No rows selected but data exists [message #417823 is a reply to message #417693] Tue, 11 August 2009 11:41 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
While RR has it's uses I really wouldn't use it for a birthday column - I know plenty of people old enough to break it.

SQL> SELECT to_char(to_date('01-JAN-90','DD-MON-RR'), 'DD-MON-YYYY') FROM dual;

TO_CHAR(TO_
-----------
01-JAN-1990

SQL> SELECT to_char(to_date('01-JAN-49','DD-MON-RR'), 'DD-MON-YYYY') FROM dual;

TO_CHAR(TO_
-----------
01-JAN-2049

SQL> 



For a DOB column you really need to use a 4 digit year.
Re: No rows selected but data exists [message #417825 is a reply to message #417823] Tue, 11 August 2009 12:09 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Agreed..
rephrasing...
Try use to_date('01-JAN-1990','DD-MON-YYYY') where ever you can.

By
Vamsi
Previous Topic: Is it possible to Access Partition Table Using DBLink
Next Topic: sql query
Goto Forum:
  


Current Time: Thu Dec 08 16:28:23 CST 2016

Total time taken to generate the page: 0.16372 seconds