Results 1 to 4 of 4
  1. #1
    Join Date
    May 2014
    Posts
    16

    Unanswered: How to INCREMENT a value in a SELECT

    I have a select statement that I'm pulling data from, but I have a field i need to increment on each record that's returned by the SELECT.

    Below in my code I tried achieving this by selecting the max value for that field then adding 1 to it, but that just puts the same value for all my returned records for that field.

    I need the select statement to return a for vsc_plan_id increment base off 286006 +1

    Can anyone assist me?

    Code:
    Select Distinct 
           (max(vsc_plan_id)+1) as vsc_plans_base_id
           ,CASE When b.participation_tier = 1 then a.vsc_plan_code
                When b.participation_tier = 2 then a.vsc_plan_code||'T1'
           END as vsc_plan_code
         ,a.vsc_plan_make
         ,a.vsc_plan_class 
         ,a.vsc_plan_term
         ,a.vsc_plan_miles
         , 'f'    as vsc_plan_commercial
         ,a.vsc_plan_sku 
         ,b.vsc_plan_dealer_price
         , 0 as vsc_plan_internal_price
         ,Case When b.vsc_plan_odo_lower = 0 Then 1
           Else b.vsc_plan_odo_lower
          End as vsc_plan_odo_lower
         ,b.vsc_plan_odo_upper
         ,b.vsc_plan_deductible
         ,CASE When b.vsc_plan_deductible = 50 Then 0 
            ELSE 1
           END as vsc_plan_disappearing_deductible
         , a.vsc_plan_rgroup
         , NULL as vsc_plan_retail_rate
         , '2014-07-01' as vsc_plan_start_date
         , '1799-12-31' as vsc_plan_end_date
         , '2014-08-01' as vsc_plan_startb_date
         , '1799-12-31' as vsc_plan_endb_date
    
    From vsc_plans_base a
    Inner Join vsc_plans_base_excel  b
          On a.vsc_plan_code  = b.vsc_plan_code 
         and a.vsc_plan_class = b.vsc_plan_class
         and a.vsc_plan_term  = b.vsc_plan_term 
         and a.vsc_plan_miles = b.vsc_plan_miles
         and a.vsc_plan_make = a.vsc_plan_make
    
    Where a.vsc_plan_make = 'NA' Also
      and a.vsc_plan_rgroup =5
    Last edited by jm_green84; 08-13-14 at 17:44.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you can impose an order on the rows (sort them), you could use the Row_Number() Windowing Function to number the rows.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Do you need to Update (increment) the value in the table, or increment it within the query data only ?

    Is 286006 the current maximum for field vsc_plan_id?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Also, you appear to be tossing the max aggregate function into a non-aggregate query.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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