Home » SQL & PL/SQL » SQL & PL/SQL » Between !! Urgent
Between !! Urgent [message #225741] Wed, 21 March 2007 04:02 Go to next message
mailtokkalyan
Messages: 65
Registered: December 2006
Location: Bangalore
Member

Dear All,
I have one Table INV_HDR. i want to select a data from that table like between one number to another number.

INV_HDR
--------
INV_NO Varchar2(10),
INV_DT date,
INV_QTY Number(10),
INV_AMT Number(10)

i wrote a query for selecting like that,

select * from inv_hdr where inv_no between '1' and '5'

but it shows only 1 and 5....
but i want from 1 to 5 records...
how can i get ? please tell me its urgent
Thanks
Re: Between !! [message #225742 is a reply to message #225741] Wed, 21 March 2007 04:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Give us some sample data to work with here. And you are trying to select from one NUMBER to another NUMBER, yet the column's data type is VARCHAR2?

EDIT: Avoid the "URGENT" keyword in topic titles. It can work against you Wink

MHE

[Updated on: Wed, 21 March 2007 04:08]

Report message to a moderator

Re: Between !! [message #225745 is a reply to message #225742] Wed, 21 March 2007 04:11 Go to previous messageGo to next message
mailtokkalyan
Messages: 65
Registered: December 2006
Location: Bangalore
Member

INV_HDR
--------
INV_NO Varchar2(10),
INV_DT date,
INV_QTY Number(10),
INV_AMT Number(10)

Select * from inv_hdr order by inv_no;

inv_no inv_dt inv_qty inv_amt
----------------------------------------
1 10/03/07 1000 2304
2 11/3/07 566 2002
3 12/03/07 1500 5000
4 13/03/07 2000 4500
5 15/03/07 1200 3000

now i want to select from 2 to 4th row

I want like this (output)

inv_no inv_dt inv_qty inv_amt
----------------------------------------
2 11/3/07 566 2002
3 12/03/07 1500 5000
4 13/03/07 2000 4500

Re: Between !! [message #225748 is a reply to message #225745] Wed, 21 March 2007 04:23 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
BETWEEN is inclusive, so the '1' and '5' are included in your result set. But I'd suggest you use a TO_NUMBER instead, otherwise you'll get something like this :

SQL> WITH yourtable AS
  2       (SELECT '1' inv_no
  3             , TO_DATE ('10/03/2007', 'dd/mm/yyyy') inv_dt
  4             , 1000 inv_qty
  5             , 2304 inv_amt
  6        FROM   DUAL
  7        UNION ALL
  8        SELECT '2' inv_no
  9             , TO_DATE ('11/03/2007', 'dd/mm/yyyy') inv_dt
 10             , 566 inv_qty
 11             , 2002 inv_amt
 12        FROM   DUAL
 13        UNION ALL
 14        SELECT '3' inv_no
 15             , TO_DATE ('12/03/2007', 'dd/mm/yyyy') inv_dt
 16             , 1500 inv_qty
 17             , 5000 inv_amt
 18        FROM   DUAL
 19        UNION ALL
 20        SELECT '4' inv_no
 21             , TO_DATE ('13/03/2007', 'dd/mm/yyyy') inv_dt
 22             , 2000 inv_qty
 23             , 4500 inv_amt
 24        FROM   DUAL
 25        UNION ALL
 26        SELECT '41' inv_no
 27             , TO_DATE ('13/03/2007', 'dd/mm/yyyy') inv_dt
 28             , 2000 inv_qty
 29             , 4500 inv_amt
 30        FROM   DUAL
 31        UNION ALL
 32        SELECT '5' inv_no
 33             , TO_DATE ('15/03/2007', 'dd/mm/yyyy') inv_dt
 34             , 1200 inv_qty
 35             , 3000 inv_amt
 36        FROM   DUAL)
 37  SELECT *
 38  FROM   yourtable
 39  WHERE  inv_no BETWEEN '1' AND '5'
 40  /

IN INV_DT       INV_QTY    INV_AMT
-- --------- ---------- ----------
1  10-MAR-07       1000       2304
2  11-MAR-07        566       2002
3  12-MAR-07       1500       5000
4  13-MAR-07       2000       4500
41 13-MAR-07       2000       4500
5  15-MAR-07       1200       3000

6 rows selected.

SQL>


The "WITH" part is just to create a temporary table, start reading from the SELECT.

Now, if I use a to number (and I want 2 to 4), I get:

SQL> ed
Wrote file afiedt.buf

  1  WITH yourtable AS
  2       (SELECT '1' inv_no
  3             , TO_DATE ('10/03/2007', 'dd/mm/yyyy') inv_dt
  4             , 1000 inv_qty
  5             , 2304 inv_amt
  6        FROM   DUAL
  7        UNION ALL
  8        SELECT '2' inv_no
  9             , TO_DATE ('11/03/2007', 'dd/mm/yyyy') inv_dt
 10             , 566 inv_qty
 11             , 2002 inv_amt
 12        FROM   DUAL
 13        UNION ALL
 14        SELECT '3' inv_no
 15             , TO_DATE ('12/03/2007', 'dd/mm/yyyy') inv_dt
 16             , 1500 inv_qty
 17             , 5000 inv_amt
 18        FROM   DUAL
 19        UNION ALL
 20        SELECT '4' inv_no
 21             , TO_DATE ('13/03/2007', 'dd/mm/yyyy') inv_dt
 22             , 2000 inv_qty
 23             , 4500 inv_amt
 24        FROM   DUAL
 25        UNION ALL
 26        SELECT '41' inv_no
 27             , TO_DATE ('13/03/2007', 'dd/mm/yyyy') inv_dt
 28             , 2000 inv_qty
 29             , 4500 inv_amt
 30        FROM   DUAL
 31        UNION ALL
 32        SELECT '5' inv_no
 33             , TO_DATE ('15/03/2007', 'dd/mm/yyyy') inv_dt
 34             , 1200 inv_qty
 35             , 3000 inv_amt
 36        FROM   DUAL)
 37  SELECT *
 38  FROM   yourtable
 39* WHERE  to_number(inv_no) BETWEEN 2 AND 4
SQL> /

IN INV_DT       INV_QTY    INV_AMT
-- --------- ---------- ----------
2  11-MAR-07        566       2002
3  12-MAR-07       1500       5000
4  13-MAR-07       2000       4500

SQL>


Is that what you wanted?

MHE
Previous Topic: is regulare expression the answer? insert escape characters into string
Next Topic: HOW TO CALL PROCEDURE FROM ANOTHER TO INSERT VALUES INTO A TABLE.....
Goto Forum:
  


Current Time: Sun Dec 04 04:26:12 CST 2016

Total time taken to generate the page: 0.22619 seconds