Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Unanswered: CASE does not give correct result

    How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)

    Scenario:
    -There are two types of business partners: Customers and Suppliers.
    -Customers have the value 'C' in the colomn CardType.
    -Suppliers have the value 'S' in the colomn CardType.
    -Customers have the following syntax 'C123456' in the colomn CardCode.
    -Suppliers have the following syntax 'S123456' in the colomn CardCode.
    -Existing CardCode-values in the DB for the Customers: C000001 - C100599.
    -Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.

    The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').


    What's been done so far:
    SELECT top 1
    (CASE
    WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
    FROM OCRD T0
    WHERE T0. CardCode like 'C%' AND T0. CardType='C'
    order BY T0.CardCode desc FOR BROWSE)
    WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
    FROM OCRD T0
    WHERE T0. CardCode like 'S%' AND T0. CardType='S'
    order BY T0.CardCode desc FOR BROWSE)
    END)
    FROM OCRD T0



    The current result:
    The result that it gives is 'C100600'.
    The problem however is that it always gives this result and does not take into account what has been selected in CardType.

    When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.

    So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.

    Does anyone know how I can make this work?

  2. #2
    Join Date
    Apr 2007
    Posts
    183

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    Code:
    You said you wanted iron clad?
    -- prepare test data
    declare @test table (t varchar(50))
    
    insert @test
    select 'ov1' union all
    select 'ov2' union all
    select 'ov4' union all
    select 'ov31' union all
    select 'ssss1' union all
    select 'ssss99'
    
    -- show data before inserting new values
    select * from @test
    
    -- do the work
    declare @prefix varchar(50)
    
    select @prefix = 'ov' -- Throw anything in!
    
    insert @test
    select	@prefix + convert(varchar, t.n + 1)
    from	(
    		select	max(convert(int, substring(t, 1 + len(@prefix), 50))) n
    		from	@test
    		where	t like @prefix + '%'
    			and not substring(t, 1 + len(@prefix), 50) like '%[^0-9]%'
    	) t
    where	not @prefix like '%[0-9]%'
    	and not t.n like '%[^0-9]%'
    
    -- show data after inserting new values
    select * from @test

  4. #4
    Join Date
    Aug 2007
    Posts
    2
    I honestly tried to fit/convert your solution to my problem but I simply do not understand what you mean with things like "@test" and "@prefix".

    This is mainly because I am merely a functional consultant in SAP Software and not a technical specialist like you. Is it therefore possible to explain your solution more in my context? :-)

  5. #5
    Join Date
    Apr 2007
    Posts
    183
    @test is the table used for this test.
    @prefix is the first couple of characters used for searching a client number. you set @prefix to the client number you want, in your case "c" or "s".

Posting Permissions

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