Home » SQL & PL/SQL » SQL & PL/SQL » "from-to" sequence from table data (Oracle 9i-9.2.0.1.0 [enterprise edition])
"from-to" sequence from table data [message #364830] Wed, 10 December 2008 03:39 Go to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends,

i have a table data stored like this,

sr tot_cartons
1 3
2 10
3 25
4 5

i want result like this,

sr from-to
1 1-3
2 4-13
3 14-38
4 39-44

will it be possible through sql statment.i've tried some of analytic functions like ( lag() and lead() ), but i'm not succeed
.so please provide me some help.either a document link or example.

Regards

Jimit
Re: "from-to" sequence from table data [message #364833 is a reply to message #364830] Wed, 10 December 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i've tried some of analytic functions like ( lag() and lead() ), but i'm not succeed

Post what you tried so far.

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) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

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

Regards
Michel
Re: "from-to" sequence from table data [message #364838 is a reply to message #364830] Wed, 10 December 2008 04:02 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

Herewith i'm sending all details with table data and script.

----Table Script
CREATE TABLE TEMP_NEW
(
  SRNO         NUMBER(1),
  TOT_CARTONS  NUMBER(4)
)
TABLESPACE WBTAB
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;

---Insert Statements
INSERT INTO TEMP_NEW ( SRNO, TOT_CARTONS ) VALUES ( 
1, 3); 
INSERT INTO TEMP_NEW ( SRNO, TOT_CARTONS ) VALUES ( 
2, 10); 
INSERT INTO TEMP_NEW ( SRNO, TOT_CARTONS ) VALUES ( 
3, 25); 
INSERT INTO TEMP_NEW ( SRNO, TOT_CARTONS ) VALUES ( 
4, 5); 
COMMIT;

---Here what i've tried so far
SELECT SRNO
,      DECODE(SRNO, 1, 1, (LAG(TOT_CARTONS) OVER (ORDER BY SRNO)) + 1)
     ||'-'
     ||DECODE(SRNO, 1, TOT_CARTONS, TOT_CARTONS + ((LAG(TOT_CARTONS) OVER (ORDER BY SRNO)) + 1)) "From-To" 
FROM   TEMP_NEW;
/

i got result nearly i wanted but on my live dbdata i'm not getting right result as i want.

Regards

Jimit


[Mod-Edit: Frank addded [code]-tags to improve readability]

[Updated on: Wed, 10 December 2008 04:07] by Moderator

Report message to a moderator

Re: "from-to" sequence from table data [message #364841 is a reply to message #364838] Wed, 10 December 2008 04:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As the starting point for a row, you want the sum of all previous rows + 1, not just the previous row + 1

(Oh, and please use [code] tags around your code)
Re: "from-to" sequence from table data [message #364844 is a reply to message #364841] Wed, 10 December 2008 04:18 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks,Moderators

i accept your suggestion and try that,it is working superb.what's bulls eye shot you have targeted.thanks.

here is my perfect working query.

SELECT SRNO,
DECODE(SRNO,1,1,(LAG(SUM(TOT_CARTONS)) OVER(ORDER BY SRNO))+1)||'-'||DECODE(SRNO,1,SUM(TOT_CARTONS),
SUM(TOT_CARTONS)+((LAG(SUM(TOT_CARTONS)) OVER(ORDER BY SRNO))+1)) "From-To" 
FROM TEMP_NEW 
GROUP BY SRNO


Regards

Jimit

[Updated on: Wed, 10 December 2008 04:44] by Moderator

Report message to a moderator

Re: "from-to" sequence from table data [message #364847 is a reply to message #364844] Wed, 10 December 2008 04:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another possibility is to make use of windowing clause:

SQL> SELECT SRNO
  2  ,      DECODE(SRNO, 1, 1, (sum(TOT_CARTONS) OVER
  3                      (ORDER BY SRNO rows between unbounded preceding and 1 preceding)) + 1)
  4       ||'-'
  5       ||DECODE(SRNO, 1, TOT_CARTONS, TOT_CARTONS + ((sum(TOT_CARTONS) OVER
  6                      (ORDER BY SRNO rows between unbounded preceding and 1 preceding)))) "From-To"
  7  FROM   TEMP_NEW
  8  order  by srno
  9  /

      SRNO From-To
---------- ---------------------------------------------------------------------------------
         1 1-3
         2 4-13
         3 14-38
         4 39-43
Previous Topic: SQL Max(cnt)
Next Topic: How to rewrite this stored procedure into a view ?
Goto Forum:
  


Current Time: Tue Dec 06 04:18:18 CST 2016

Total time taken to generate the page: 0.20741 seconds