Skip to Content
author's profile photo Former Member
Former Member

Automatic number generation

Hi All,

How can I generate automatic number's in Business patner code? I tried to execute one query for this , but error came while executing this query.Error message is: 'Error (8180) encountered'. Please help me out from this

thanks in advance bbn

Query:

-- This Query generates a Business Partner Code automatically

-- depending on the Card Type i.e. C100001, S1000001, L1000001

-- Prerequisites:

-- 1. The first customer, supplier and lead number has to be entered manually

-- 2. The number must start directly behind the cardtype, no leading zeros (NOT C000100)

-- Edda, March 27, 2003

SELECT $[OCRD.CardType] +

CONVERT(char(7),MAX(CONVERT(int, SUBSTRING(CardCode, 2, 6)))+1)

FROM OCRD

where OCRD.CardType = $[OCRD.CARDTYPE]

FOR BROWSE

SELECT

CASE OCRD.CardType

WHEN 'C' Then MAX(OCRD.CardCode)+1

When 'S' Then MAX(OCRD.CardCode)+1

Else ''

END

FROM OCRD WHERE OCRD.CardCode <> '20001' and OCRD.CardType = $[OCRD.CARDTYPE] Group by OCRD.CardType

FOR BROWSE

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 30, 2005 at 11:10 AM

    DECLARE @CardType varchar(1)

    SET @CardType = $[$40.0.0]

    SELECT

    CASE @CardType

    WHEN 'C' Then 'C' + CONVERT(char(7),MAX(CONVERT(int, SUBSTRING(T0.CardCode, 2, 6)))+1)

    When 'S' Then 'S' + CONVERT(char(7),MAX(CONVERT(int, SUBSTRING(T0.CardCode, 2, 6)))+1)

    When 'L' Then 'L' + CONVERT(char(7),MAX(CONVERT(int, SUBSTRING(CardCode, 2, 6)))+1)

    Else ''

    END

    FROM OCRD T0 WHERE T0.CardCode <> '20001' and T0.CardType = @CardType Group by T0.CardType

    FOR BROWSE

    <i>

    Message was edited by: Adele le Roux

    Was busy posting and testing when Alexey was. Was trying to base query on your query</i>

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 30, 2005 at 10:53 AM

    Hi!

    Hope it'll be not so hard to complete my query in accordance with your requirements:[code]DECLARE @NumLen Int

    SET @NumLen = 7

    IF (SELECT Count(*) FROM OCRD) = 0

    BEGIN

    IF (RTrim($[OCRD.CardType]) = 'S')

    SELECT 'V'SUBSTRING('0000000000',1,@NumLen-2)'1'

    ELSE

    SELECT RTrim($[OCRD.CardType])SUBSTRING('0000000000',1,@NumLen-2)'1'

    END

    ELSE

    IF (RTrim($[OCRD.CardType]) = 'S')

    SELECT

    'V'

    +

    SUBSTRING('0000000000',1,@NumLen-1-LEN(RTrim(Max(CAST(SUBSTRING(CardCode,2,99) AS INT))+1)))

    +

    RTrim(Max(CAST(SUBSTRING(CardCode,2,99) AS INT))+1)

    FROM

    OCRD

    ELSE

    SELECT

    RTrim($[OCRD.CardType])

    +

    SUBSTRING('0000000000',1,@NumLen-1-LEN(RTrim(Max(CAST(SUBSTRING(CardCode,2,99) AS INT))+1)))

    +

    RTrim(Max(CAST(SUBSTRING(CardCode,2,99) AS INT))+1)

    FROM

    OCRD[/code]HTH

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 03, 2005 at 12:06 PM

    Hi,

    Thanks for ur reply. The query is working fine.

    with regards bbn

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.