[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 828
  • Last Modified:

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

0
AGION
Asked:
AGION
  • 9
  • 5
  • 2
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 9
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now