On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,524 Users
  • 2,269,754 Discussions
  • 7,916,770 Comments

Discussions

Sql Query to find increment of a number using select Statement

User_40B57
User_40B57 Member Posts: 7 Green Ribbon
edited Sep 4, 2020 6:00AM in SQL & PL/SQL

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.

Tagged:
User_40B57BrunoVroman

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,212 Red Diamond
    edited Sep 3, 2020 12:09PM

    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.

    User_40B57User_40B57
  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
    edited Sep 3, 2020 1:11PM

    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]
    [Deleted User] Jack of all (Geo-) trades Hoorn, The NetherlandsPosts: 0 Silver Trophy
    edited Sep 4, 2020 2:10AM
    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.

    BrunoVroman
  • User_40B57
    User_40B57 Member Posts: 7 Green Ribbon
    edited Sep 4, 2020 3:21AM

    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
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Sep 4, 2020 4:17AM

    >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
    Solomon Yakobson Member Posts: 20,212 Red Diamond
    edited Sep 4, 2020 5:45AM
    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
    Solomon Yakobson Member Posts: 20,212 Red Diamond
    edited Sep 4, 2020 6:00AM

    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.