asked on

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

Patrick Matthews

SELECT 'D' + CONVERT(varchar, MAX(CONVERT(numeric(14, 0), SUBSTRING(CardCode, 2, 14)) + 1)) As NewNum

Guy Hengel [angelIII / a3]

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

AGION

ASKER

>> 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.

Guy Hengel [angelIII / a3]

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

Patrick Matthews

AGION,

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

Regards,

Patrick

AGION

ASKER

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.

AGION

ASKER

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.

Guy Hengel [angelIII / a3]

>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?

AGION

ASKER

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.

AGION

ASKER

>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

Guy Hengel [angelIII / a3]

>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).

AGION

ASKER

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!

AGION

ASKER

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

AGION

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AGION

ASKER

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!