Home » SQL & PL/SQL » SQL & PL/SQL » Problem in retrieving voucher no. pls. help ...
Problem in retrieving voucher no. pls. help ... [message #7890] Tue, 15 July 2003 09:50 Go to next message
Kalim Ahmad
Messages: 9
Registered: July 2003
Junior Member
dear

c ...

i m writing this query to display voucher no, voucherdate etc..

select voucherno,voucherdate, etc ... from vouchers
where to_number(voucherno) between to_number('1769994')
and to_number('1760005');

it gives the result

no rows selected

but when i will send this query

select voucherno,voucherdate etc from vouchers
where to_number(voucherno) between to_number('1769994')
and to_number('1769999');

it will show all

VOUCHERNO VOUCHERDATE
--------------------------
1769994 20-06-2003
1769995 20-06-2003
1769996 20-06-2003
1769997 21-06-2003
1769998 21-06-2003
1769999 21-06-2003

and same for this query too

select voucherno,voucherdate etc from vouchers
where to_number(voucherno) between to_number('1760001')
and to_number('1760005');

it will show all

VOUCHERNO VOUCHERDATE
--------------------------
1760001 24-06-2003
1760002 24-06-2003
1760003 24-06-2003
1760004 25-06-2003
1760005 25-06-2003

note our maxm limit for voucher is 1769999 after reaching this no. it starts from 1760001
off course i need . VOUCHERNO IS VARCHAR2.

VOUCHERNO VOUCHERDATE
--------------------------
1769994 20-06-2003
1769995 20-06-2003
1769996 20-06-2003
1769997 21-06-2003
1769998 21-06-2003
1769999 21-06-2003
1760001 24-06-2003
1760002 24-06-2003
1760003 24-06-2003
1760004 25-06-2003
1760005 25-06-2003

PLS. HELP ME ....

THANX
Re: Problem in retrieving voucher no. pls. help ... [message #7891 is a reply to message #7890] Tue, 15 July 2003 10:30 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
BETWEEN takes two arguments - a low and high value.

You cannot put a higher number on the left (before the 'and') and get any result other than an empty one.
Re: Problem in retrieving voucher no. pls. help ... [message #7896 is a reply to message #7891] Wed, 16 July 2003 00:36 Go to previous messageGo to next message
Kalim Ahmad
Messages: 9
Registered: July 2003
Junior Member
C... in this case, if i will put lower no. first then it will start from the beginig 1760001 upto 1769999 complete list.


actually i m using this query in Reports.
before reaching to 1769999 it was working perfactly but after reaching to 1769999. it will automatically start from 1760001 even if the date is same or later than 1769999 i am unable to print the report if i pass the parameter value 1769999 to 1760001 or 1760005 or any value less than 1769999.
but i will have to pass the paramete value in this way only becoz 1769999 has been entered first then only it start from 1760001.
u must consider the case upto 1769999 was working perfactly.

i have 2 parameter value one is

1 startvoucherno
2 endvoucherno

in this case startvoucherno should b 1769999
and endvoucherno 1760001
even if i will revrese this value it will give me the complete list starting from 1760001 upto 1769999 which i do not need i need only to print
1769999 25-jun-2003
1760001 25-jun-2003
hope u will get the problem now.

pls help ...

kalim
Re: Problem in retrieving voucher no. pls. help ... [message #7899 is a reply to message #7890] Wed, 16 July 2003 03:06 Go to previous messageGo to next message
sujit
Messages: 94
Registered: April 2002
Member
hi,
as you are using to_number fuction and your to number is smaller than from number you can't fetch the rows. you can write it as

select voucherno,voucherdate, etc ... from vouchers
where to_number(voucherno) (between to_number('1769994') and ('1769999') or between to_number('1760001') and to_number('1760005'));
Hope this will work
Sujit
Re: Problem in retrieving voucher no. pls. help ... [message #7977 is a reply to message #7896] Sun, 20 July 2003 10:28 Go to previous message
Kalim Ahmad
Messages: 9
Registered: July 2003
Junior Member
Thanx for Co operation.

I solved this problem using ROWID. It works perfactly.

Kalim
Previous Topic: INSERT INTO with subquery select from same table = ORA-00947 not enough values
Next Topic: sqlplus -s
Goto Forum:
  


Current Time: Thu Mar 28 15:44:01 CDT 2024