Sql Query to find increment of a number using select Statement

User_40B57

Summary:

I need a select query which should provide me a output with increment for the number (0001). Suggest a query which apt for the below expected output without using Sequence conditions.

Expected output:

TO_CHAR('0001')
0001
0002
0003

Query:

select to_char('0001') from dual;

Thanks,

Hema.

Solomon Yakobson

SQL> SELECT  TO_CHAR(LEVEL,'FM0000')

  2    FROM  DUAL

  3    CONNECT BY LEVEL <= 5 -- choose your limit

  4  /

TO_C

----

0001

0002

0003

0004

0005

SQL>

SY.

L. Fernigrini

This is a script we use on some cases (modified for your output requirements) , you can provide an initial value and the increment.

WITH vData AS (

SELECT 1 AS InitialValue, 1 AS StepIncrement, 5 AS DesiredRows FROM DUAL )

SELECT TO_CHAR(InitialValue + ( (Level - 1) * StepIncrement), '0000')

FROM vData

    CONNECT BY Level <= DesiredRows;

[Deleted User]

4141147 wrote:select to_char('0001') from dual;

Pay attention to your datatypes: You're giving to_char a string, while it expects a number or a date. So Oracle implicitly converst your string first to a number, in order to use the to_char function. Don't rely on implicit conversions, ALWAYS pay attention to your datatypes and deal with them properly. If you don't, I can guarantee you that it will come back and bite you at some point in the future.

User_40B57

Thanks for the information this is useful for me. Also i need solution , after 0005 it should continue with 0001.

For example :

0001

0002

0003

0004

0005

0001

0002

Thanks,

Hema

Paulzip

>select to_char(mod(level - 1, 5) + 1, 'FM0000') val

>from dual

>connect by level <= 15 -- choose your limit

>/

VAL

-----

0001

0002

0003

0004

0005

0001

0002

0003

0004

0005

0001

0002

0003

0004

0005

15 rows selected.

Solomon Yakobson

4141147 wrote:Thanks for the information this is useful for me. Also i need solution , after 0005 it should continue with 0001.

Generic solution:

WITH DRIVER AS (

                SELECT  1 LOWER_BAND,

                        5 UPPER_BAND,

                        3 REPEAT_NUMBER

                  FROM  DUAL

              )

SELECT  TO_CHAR(LOWER_BAND + MOD(LEVEL - 1,UPPER_BAND),'FM0000') N

  FROM  DRIVER

  CONNECT BY LEVEL <= UPPER_BAND * REPEAT_NUMBER

/

N

-----

0001

0002

0003

0004

0005

0001

0002

0003

0004

0005

0001

0002

0003

0004

0005

15 rows selected.

SQL>

SY.

Solomon Yakobson

Actually it isn't 100% generic since we have hardcoded FM0000. You could do something like:

WITH DRIVER AS (

                SELECT  1 LOWER_BAND,

                        5 UPPER_BAND,

                        3 REPEAT_NUMBER,

                        3 MIN_LEADING_ZEROES

                  FROM  DUAL

              )

SELECT  TO_CHAR(LOWER_BAND + MOD(LEVEL - 1,UPPER_BAND),'FM' || LPAD('0',MIN_LEADING_ZEROES + CEIL(UPPER_BAND / 10),'0')) N

  FROM  DRIVER

  CONNECT BY LEVEL <= UPPER_BAND * REPEAT_NUMBER

/

N

-----

0001

0002

0003

0004

0005

0001

0002

0003

0004

0005

0001

0002

0003

0004

0005

15 rows selected.

SQL>

WITH DRIVER AS (

                SELECT  1 LOWER_BAND,

                        10 UPPER_BAND,

                        2 REPEAT_NUMBER,

                        3 MIN_LEADING_ZEROES

                  FROM  DUAL

              )

SELECT  TO_CHAR(LOWER_BAND + MOD(LEVEL - 1,UPPER_BAND),'FM' || LPAD('0',MIN_LEADING_ZEROES + CEIL(UPPER_BAND / 10),'0')) N

  FROM  DRIVER

  CONNECT BY LEVEL <= UPPER_BAND * REPEAT_NUMBER

/

N

-----

0001

0002

0003

0004

0005

0006

0007

0008

0009

0010

0001

0002

0003

0004

0005

0006

0007

0008

0009

0010

20 rows selected.

SQL>

SY.