Getting the right Maximum value in a query

Hello experts,

I have a question about the max statement in SQL.
The query in the snippet below should give me the right maximum value and add '+1'
This query is being used to automaticly make a new code for a business partner (which should be unique).

As I use this query it doesn't give me the right maximum value: It returns as max value P9999 but I also have a value P10001. I know it has everything to do with the way SQL sorts data, but I don't know how to solve the query that it might select the RIGHT max value.

All help is welcome!
If OCRD.CardType='C'
 
Begin
 
SELECT     'D' + CONVERT(varchar, MAX(CONVERT(numeric(14, 0),
 
SUBSTRING(CardCode, 2, 14))) + 1) AS NewNum
 
FROM         OCRD
 
WHERE CardCode Like 'D%'
 
end
 
 
else
 
If OCRD.CardType='L'
 
Begin
 
SELECT     'P' + CONVERT(varchar, MAX(CONVERT(numeric(14, 0),
 
SUBSTRING(CardCode, 2, 14))) + 1) AS NewNum
 
FROM         OCRD
 
WHERE CardCode Like 'P%'
 
end
 
 
else
 
If OCRD.CardType ='S'
 
Begin
 
SELECT     'C' + CONVERT(varchar, MAX(CONVERT(numeric(14, 0),
 
SUBSTRING(CardCode, 2, 14))) + 1) AS NewNum
 
FROM         OCRD
 
WHERE CardCode Like 'C%'
 
end

Open in new window

AGIONAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
SELECT     'D' + CONVERT(varchar, MAX(CONVERT(numeric(14, 0), SUBSTRING(CardCode, 2, 14)) + 1)) As NewNum
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about simplifying your query into this:
DECLARE @requested_card_type VARCHAR(1)
SET @requested_card_type = 'C'  --- put 'C, L, S' 
DECLARE @prefix VARCHAR(1)
SET @prefix = CASE 
  WHEN @requested_card_type = 'C' THEN 'D' 
  WHEN @requested_card_type = 'L' THEN 'P' 
  WHEN @requested_card_type = 'S' THEN 'C' 
  ELSE 'X' END 
 
SELECT @prefix + CONVERT(varchar(14), MAX( CONVERT(numeric(14, 0), RTRIM(SUBSTRING(CardCode, 2, 14)) )) + 1) AS NewNum
FROM         OCRD
WHERE CardCode Like @prefix + '%'
 

Open in new window

0
AGIONAuthor Commented:
>> what about simplifying your query into this:
Angellll,
Thanks for your comment, it does work, but not the way I expected. Maybe I need to clarify that it should look at an other max when the cardtype changes. So by instance:
If the CardType = 'C' then the max should be 'D10025'
If the CardType = 'S' then the max should be 'C10043'
If the CardType = 'L' then the max should be 'P10258'
With your query it gives me allways 'D.....' and that is not right.
I hope you do understand what I mean.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, I understand what you mean, but you did not mention where the CardType comes from? is this in a trigger (so it would be from inserted.CardType, right?!), or in a procedure (so it would be a parameter...)

please clarify
0
Patrick MatthewsCommented:
AGION,

Did you try changing your expressions using the example I provided?

Regards,

Patrick
0
AGIONAuthor Commented:
Matthewspatrick,
I took a close look at your expression, but I can't see the difference between yours and mine, but I will take a give it a try in a minute.
 
Angellllll,
The procedure is like this: I add a new customer/supplier/lead, so I select it in a sort of dropdownbox, after doing that I will try to run the query. So the query can get the CardType from SQL. I am not familiar with trickers yet, so maybe that could be a solution too.
0
AGIONAuthor Commented:
By the way, my excuse for my late reaction to you guys, but yesterday I had an urgent job, so I didn't have the change to look at your replies anymore.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The procedure is like this: I add a new customer/supplier/lead, so I select it in a sort of dropdownbox, after doing that I will try to run the query.
means, the type is a parameter?!

>So the query can get the CardType from SQL.
how that? contradicts the above, no?

please clarify a bit more...
what is the programming language?
0
AGIONAuthor Commented:
matthewspatrick,
your change doesn't work eighter, I changed my query with your changes, but it does not work.
 
SQL gives this error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
0
AGIONAuthor Commented:
>means, the type is a parameter?!
Yes the CardType is a parameter, I asked a collegue and he confirmed that.

>how that? contradicts the above, no?
Well that sounds a bit strange to me as well, but my collegue is sure the query can get the right data from SQL.
>what is the programming language?
I am developing in SAP Business One, I don't know about its programming language
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Well that sounds a bit strange to me as well, but my collegue is sure the query can get the right data from SQL.

for that, that "parameter" needs to be stored somewhere already, if it's not a parameter of a stored procedure ....

stabbing in the dark, as I don't know SAP at all (apart from the name).

0
AGIONAuthor Commented:
My collegue made the query the first time and it does work at his company database but it has an internal lookup:
It will check the CardType in SAP and not in the SQL database.
So he mistaked explaining me how it would work. But now I exactly copied his query and it doesn't work for me.
So I still need to debug it somehow. I will take a look at it later on today and if I do have any questions I will post them here!
0
AGIONAuthor Commented:
The code with an internal lookup will be:
 

If $[OCRD.CardType] ='C'
Begin
SELECT     'D' + CONVERT(varchar, MAX(CONVERT(numeric(14, 0),
SUBSTRING(CardCode, 2, 14))) + 1) AS NewNum
FROM         OCRD
WHERE CardCode Like 'D%'
end
 
else
 
If $[OCRD.CardType] ='L'
Begin
SELECT     'P' + CONVERT(varchar, MAX(CONVERT(numeric(14, 0),
SUBSTRING(CardCode, 2, 14))) + 1) AS NewNum
FROM         OCRD
WHERE CardCode Like 'P%'
end 
 
else
 
If OCRD.CardType ='S'
Begin
SELECT     'C' + CONVERT(varchar, MAX(CONVERT(numeric(14, 0),
SUBSTRING(CardCode, 2, 14))) + 1) AS NewNum
FROM         OCRD
WHERE CardCode Like 'C%'
end

Open in new window

0
AGIONAuthor Commented:
Angel,
Could you do anything with
$[OCRD.CardCode] instead of OCRD.CardCode in the query you made yesterday?
The query above worked for a collegue of mine, but we both don't know why it doesn't work for me.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, what about:
DECLARE @requested_card_type VARCHAR(1)
SET @requested_card_type = '$[OCRD.CardCode]'
DECLARE @prefix VARCHAR(1)
SET @prefix = CASE 
  WHEN @requested_card_type = 'C' THEN 'D' 
  WHEN @requested_card_type = 'L' THEN 'P' 
  WHEN @requested_card_type = 'S' THEN 'C' 
  ELSE 'X' END 
 
SELECT @prefix + CONVERT(varchar(14), MAX( CONVERT(numeric(14, 0), RTRIM(SUBSTRING(CardCode, 2, 14)) )) + 1) AS NewNum
FROM         OCRD
WHERE CardCode Like @prefix + '%'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AGIONAuthor Commented:
you are the man!! it works now, I had to convert the $[OCRD.CardCode] again, but it works great!
The points are yours, besides I did learn a lot about your way of making queries!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.