Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Unanswered: Increment in Select Statement

    Hi,

    Following is a query which gives me an output as mentioned below. Can anybody help me out, in giving increment numbers, e.g. 1,2,3,4... to the first column in the select statement.

    use account
    select 'PT','JE01','71','1','1', Vou_date=convert(varchar,vdt,101), cltcode, '994801', vamt, 'ZJE01','JOURNL', '0.00',
    narration, DrCr = (case when drcr = 'D' then 'D' else 'C' end),
    '','','71','1','P',',',',',','
    from ledger where cltcode = '5555'
    and vdt >= 'Apr 1 2007'
    and vtyp = '15'
    order by vdt

    OUTPUT

    PT JE01 71 1 1 20070419 A9986

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Check out the ROW_NUMBER() function as part of the OVER clause in Books On Line

  3. #3
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    you can use identity() function to generate the increament numbers. Your query should be like below

    use account
    select RowNum = identity(9),'PT','JE01','71','1','1', Vou_date=convert(varchar,vdt,101), cltcode, '994801', vamt, 'ZJE01','JOURNL', '0.00',
    narration, DrCr = (case when drcr = 'D' then 'D' else 'C' end),
    '','','71','1','P',',',',',','
    from ledger where cltcode = '5555'
    and vdt >= 'Apr 1 2007'
    and vtyp = '15'
    order by vdt

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by parangiri
    you can use identity() function to generate the increament numbers.
    are you sure?

    i just tested your solution and got an error "incorrect syntax near '9'"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    Thanks for checking.I am sorry identity will not work in this scenario. Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

  6. #6
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    After dumping the whole record in the temp table, you can have the rows increamented. Below piece of code might be helpful

    use account
    select RowNum = identity(int,1,1),'PT','JE01','71','1','1', Vou_date=convert(varchar,vdt,101), cltcode, '994801', vamt, 'ZJE01','JOURNL', '0.00',
    narration, DrCr = (case when drcr = 'D' then 'D' else 'C' end),
    '','','71','1','P',',',',',',' into #temp
    from ledger where cltcode = '5555'
    and vdt >= 'Apr 1 2007'
    and vtyp = '15'
    order by vdt

    select * from #temp

  7. #7
    Join Date
    Nov 2007
    Posts
    3

    Error displayed

    An error message 'No column was specified for column 1 of '#temp'

    this message is displayed for 23 columns

    Thanks and regards

  8. #8
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    instead of RowNum = identity(int,1,1), u try to specify as IDENTITY(int, 1,1) AS RowNum and try

  9. #9
    Join Date
    Nov 2007
    Posts
    3

    Error displayed

    Hi,

    its still not working

    Thanks and regards

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please avoid SELECT... INTO where ever possible. If you are using 2005 there is a much more efficient method. http://www.dbforums.com/showthread.php?t=1624901

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •