CODEWITHSUNDEEP
sqloracle-databaseplsql
Priyan RockZ
Priyan RockZ

Reputation: 1615

Oracle get numbers with range

Here is my serial table: 

+-----------------+-----------------+
|  START_SERIAL   |   END_SERIAL    |
+-----------------+-----------------+
| 120500216057150 | 120500216057155 |
| 120500216057157 | 120500216057157 |
| 120500216057161 | 120500216057164 |
+-----------------+-----------------+

Need to get result like this:

120500216057150 
120500216057151 
120500216057152 
120500216057153 
120500216057154 
120500216057155
120500216057157
120500216057161....

Please help me to sort out this issue. [seems need pl-sql with for loop needed.?]

EDIT dear TechDo, What about pl/sql usage here as follows.but its gives me a error :-)

DECLARE
  start_serial  NUMBER;
  end_serial  NUMBER;
  records NUMBER;
BEGIN
  select start_serial INTO start_serial
    from sales_details ;
  select (start_serial+no_of_cards)as end_serial into end_serial
    from sales_details;
  select count(*) as records into records 
    from sales_details;

  FOR i IN 1..records  LOOP
    INSERT INTO temp (serial_no)
    VALUES(i);
  END LOOP;
END;
/

Upvotes: 1

Views: 714

Answers (1)

TechDo
TechDo

Reputation: 18629

Please try:

select 
  distinct START_SERIAL+level-1 SERIAL
from 
  YourTable
connect by level-1<=END_SERIAL-START_SERIAL
order by 1;

Upvotes: 5

Related Questions