• Dear SQL, How to convert If statement to Case ? I have already tried but does not work. Here my function : ALTER Function MaxId() Returns Char(4) As Begin
Message 1 of 7 , Dec 14, 2006
View Source
• 0 Attachment
Dear SQL,
How to convert If statement to Case ? I have already tried but does not work.
Here my function :

ALTER Function MaxId()
Returns Char(4)
As
Begin
Declare @MaxValue SmallInt, @Value Char(4)

Set @MaxValue = Cast((Select IsNull(Max(Right(HeaderId, 4)), 0)

If Len(Cast(@MaxValue As Varchar))= 1 Set @Value = '000' + Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 2 Set @Value = '00' + Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 3 Set @Value = '00' + Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 4 Set @Value = Cast(@MaxValue + 1 As Varchar)

Return @Value
End

I need become :

Case Len(Cast(@MaxValue As Varchar))
When '1' Then Set @Value = '000' + Cast(@MaxValue + 1 As Varchar)
.................................................................
End

Best regards,
Surya A. Saputra

http://aspspider.biz/saputra

--- On the road to glory ---
• Wouldn t it be much simpler to do something like this: DECLARE @MaxValue SmallInt SET @MaxValue = 2 SELECT right( 000 + cast(@MaxValue as varchar(4)), 4) -
Message 2 of 7 , Dec 14, 2006
View Source
• 0 Attachment
Wouldn't it be much simpler to do something like this:

DECLARE @MaxValue SmallInt
SET @MaxValue = 2

SELECT right( '000' + cast(@MaxValue as varchar(4)), 4)

- Arnie Rowland

"I am a great believer in luck, and I find that the harder I work, the more
I have of it." - Thomas Jefferson (1743-1826)

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Surya A. Saputra
Sent: Thursday, December 14, 2006 2:21 PM
To: Arnie
Subject: [SQLQueriesNoCode] Convert IF to Case

Dear SQL,
How to convert If statement to Case ? I have already tried but does not
work.
Here my function :

ALTER Function MaxId()
Returns Char(4)
As
Begin
Declare @MaxValue SmallInt, @Value Char(4)

Set @MaxValue = Cast((Select IsNull(Max(Right(HeaderId, 4)), 0)

If Len(Cast(@MaxValue As Varchar))= 1 Set @Value = '000' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 2 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 3 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 4 Set @Value = Cast(@MaxValue +
1 As Varchar)

Return @Value
End

I need become :

Case Len(Cast(@MaxValue As Varchar))
When '1' Then Set @Value = '000' + Cast(@MaxValue + 1 As Varchar)
.................................................................
End

Best regards,
Surya A. Saputra

http://aspspider.biz/saputra

--- On the road to glory ---

Disclaimer - December 14, 2006
This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

[Non-text portions of this message have been removed]
• I find on occasion the numeric comparison sometimes needs to be inside a pair of single quotes; example case when Len(Cast(ItemOfInterest As Varchar)) = 1
Message 3 of 7 , Dec 17, 2006
View Source
• 0 Attachment
I find on occasion the numeric comparison sometimes needs to be inside a pair of single quotes;
example

case when Len(Cast(ItemOfInterest As Varchar)) = '1' then '000' +
Cast(ItemOfInterest + '1' As Varchar)
end as 'FieldName'

AJ

Arnie Rowland <arnie@...> wrote: Wouldn't it be much simpler to do something like this:

DECLARE @MaxValue SmallInt
SET @MaxValue = 2

SELECT right( '000' + cast(@MaxValue as varchar(4)), 4)

- Arnie Rowland

"I am a great believer in luck, and I find that the harder I work, the more
I have of it." - Thomas Jefferson (1743-1826)

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Surya A. Saputra
Sent: Thursday, December 14, 2006 2:21 PM
To: Arnie
Subject: [SQLQueriesNoCode] Convert IF to Case

Dear SQL,
How to convert If statement to Case ? I have already tried but does not
work.
Here my function :

ALTER Function MaxId()
Returns Char(4)
As
Begin
Declare @MaxValue SmallInt, @Value Char(4)

Set @MaxValue = Cast((Select IsNull(Max(Right(HeaderId, 4)), 0)

If Len(Cast(@MaxValue As Varchar))= 1 Set @Value = '000' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 2 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 3 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 4 Set @Value = Cast(@MaxValue +
1 As Varchar)

Return @Value
End

I need become :

Case Len(Cast(@MaxValue As Varchar))
When '1' Then Set @Value = '000' + Cast(@MaxValue + 1 As Varchar)
.................................................................
End

Best regards,
Surya A. Saputra

http://aspspider.biz/saputra

--- On the road to glory ---

Disclaimer - December 14, 2006
This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

[Non-text portions of this message have been removed]

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]
• Then it is not a numeric comparison , is it? Len() returns a number, not a string. Putting a number in single quotes makes it a string. Comparing a number to
Message 4 of 7 , Dec 17, 2006
View Source
• 0 Attachment
Then it is not a 'numeric comparison', is it?

Len() returns a number, not a string. Putting a number in single quotes
makes it a string. Comparing a number to a string works because of implicit
conversion. When you compare len() = '1', you are depending upon an
'implicit' conversion -and that is not a good practice.

I fail to see how your response changes or adds to the response I previously
sent. In fact, if anything, you don't seem to be responding to the original
posting at all.

- Arnie Rowland

"I am a great believer in luck, and I find that the harder I work, the more
I have of it." - Thomas Jefferson (1743-1826)

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of A.J. Morales
Sent: Sunday, December 17, 2006 10:51 PM
To: Arnie
Subject: RE: [SQLQueriesNoCode] Convert IF to Case

I find on occasion the numeric comparison sometimes needs to be inside a
pair of single quotes;
example

case when Len(Cast(ItemOfInterest As Varchar)) = '1' then '000' +
Cast(ItemOfInterest + '1' As Varchar)
end as 'FieldName'

AJ

Arnie Rowland <arnie@...> wrote:
Wouldn't it be much simpler to do something like this:

DECLARE @MaxValue SmallInt
SET @MaxValue = 2

SELECT right( '000' + cast(@MaxValue as varchar(4)), 4)

- Arnie Rowland

"I am a great believer in luck, and I find that the harder I work, the more
I have of it." - Thomas Jefferson (1743-1826)

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Surya A. Saputra
Sent: Thursday, December 14, 2006 2:21 PM
To: Arnie
Subject: [SQLQueriesNoCode] Convert IF to Case

Dear SQL,
How to convert If statement to Case ? I have already tried but does not
work.
Here my function :

ALTER Function MaxId()
Returns Char(4)
As
Begin
Declare @MaxValue SmallInt, @Value Char(4)

Set @MaxValue = Cast((Select IsNull(Max(Right(HeaderId, 4)), 0)

If Len(Cast(@MaxValue As Varchar))= 1 Set @Value = '000' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 2 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 3 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 4 Set @Value = Cast(@MaxValue +
1 As Varchar)

Return @Value
End

I need become :

Case Len(Cast(@MaxValue As Varchar))
When '1' Then Set @Value = '000' + Cast(@MaxValue + 1 As Varchar)
.................................................................
End

Best regards,
Surya A. Saputra

http://aspspider.biz/saputra

--- On the road to glory ---

Disclaimer - December 14, 2006
This email and any files transmitted with it are confidential and intended
solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named
addressee you should not disseminate, distribute, copy or alter this email.
Any views or opinions presented in this email are solely those of the author
and might not represent those of Westwood Consulting, Inc. Warning: Although
Westwood Consulting, Inc has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

[Non-text portions of this message have been removed]

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]
• Hi, I think this will be take out both IF and CASE confusions. DECLARE @MaxValue SMALLINT, @Value Char(4) SET @MaxValue = 1 SELECT @Value = STUFF(@MaxValue, 1,
Message 5 of 7 , Dec 17, 2006
View Source
• 0 Attachment
Hi,

I think this will be take out both IF and CASE confusions.

DECLARE @MaxValue SMALLINT, @Value Char(4)
SET @MaxValue = 1

SELECT @Value =
STUFF(@MaxValue, 1, 0, REPLICATE('0', 4 - LEN(@MaxValue)))

Regards,
Elan.

--- In SQLQueriesNoCode@yahoogroups.com, "Surya A. Saputra"
<surya.saputra@...> wrote:
>
> Dear SQL,
> How to convert If statement to Case ? I have already tried but
does not work.
> Here my function :
>
> ALTER Function MaxId()
> Returns Char(4)
> As
> Begin
> Declare @MaxValue SmallInt, @Value Char(4)
>
> Set @MaxValue = Cast((Select IsNull(Max(Right(HeaderId,
4)), 0)
>
> If Len(Cast(@MaxValue As Varchar))= 1 Set @Value = '000' +
Cast(@MaxValue + 1 As Varchar)
> If Len(Cast(@MaxValue As Varchar))= 2 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
> If Len(Cast(@MaxValue As Varchar))= 3 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
> If Len(Cast(@MaxValue As Varchar))= 4 Set @Value = Cast
(@MaxValue + 1 As Varchar)
>
> Return @Value
> End
>
> I need become :
>
> Case Len(Cast(@MaxValue As Varchar))
> When '1' Then Set @Value = '000' + Cast(@MaxValue + 1 As
Varchar)
> ..............................................................
...
> End
>
> Best regards,
> Surya A. Saputra
>
> http://aspspider.biz/saputra
>
> --- On the road to glory ---
>
• Well, OK then, please disregard! Thanks AJ Arnie Rowland wrote: Then it is not a numeric comparison , is it?
Message 6 of 7 , Dec 18, 2006
View Source
• 0 Attachment
Thanks
AJ

Arnie Rowland <arnie@...> wrote: Then it is not a 'numeric comparison', is it?

Len() returns a number, not a string. Putting a number in single quotes
makes it a string. Comparing a number to a string works because of implicit
conversion. When you compare len() = '1', you are depending upon an
'implicit' conversion -and that is not a good practice.

I fail to see how your response changes or adds to the response I previously
sent. In fact, if anything, you don't seem to be responding to the original
posting at all.

- Arnie Rowland

"I am a great believer in luck, and I find that the harder I work, the more
I have of it." - Thomas Jefferson (1743-1826)

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of A.J. Morales
Sent: Sunday, December 17, 2006 10:51 PM
To: Arnie
Subject: RE: [SQLQueriesNoCode] Convert IF to Case

I find on occasion the numeric comparison sometimes needs to be inside a
pair of single quotes;
example

case when Len(Cast(ItemOfInterest As Varchar)) = '1' then '000' +
Cast(ItemOfInterest + '1' As Varchar)
end as 'FieldName'

AJ

Arnie Rowland <arnie@...> wrote:
Wouldn't it be much simpler to do something like this:

DECLARE @MaxValue SmallInt
SET @MaxValue = 2

SELECT right( '000' + cast(@MaxValue as varchar(4)), 4)

- Arnie Rowland

"I am a great believer in luck, and I find that the harder I work, the more
I have of it." - Thomas Jefferson (1743-1826)

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Surya A. Saputra
Sent: Thursday, December 14, 2006 2:21 PM
To: Arnie
Subject: [SQLQueriesNoCode] Convert IF to Case

Dear SQL,
How to convert If statement to Case ? I have already tried but does not
work.
Here my function :

ALTER Function MaxId()
Returns Char(4)
As
Begin
Declare @MaxValue SmallInt, @Value Char(4)

Set @MaxValue = Cast((Select IsNull(Max(Right(HeaderId, 4)), 0)

If Len(Cast(@MaxValue As Varchar))= 1 Set @Value = '000' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 2 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 3 Set @Value = '00' +
Cast(@MaxValue + 1 As Varchar)
If Len(Cast(@MaxValue As Varchar))= 4 Set @Value = Cast(@MaxValue +
1 As Varchar)

Return @Value
End

I need become :

Case Len(Cast(@MaxValue As Varchar))
When '1' Then Set @Value = '000' + Cast(@MaxValue + 1 As Varchar)
.................................................................
End

Best regards,
Surya A. Saputra

http://aspspider.biz/saputra

--- On the road to glory ---

Disclaimer - December 14, 2006
This email and any files transmitted with it are confidential and intended
solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named
addressee you should not disseminate, distribute, copy or alter this email.
Any views or opinions presented in this email are solely those of the author
and might not represent those of Westwood Consulting, Inc. Warning: Although
Westwood Consulting, Inc has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

[Non-text portions of this message have been removed]

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]
• Dear Arnie, ... Reply, Thanks Best regards, Surya A. Saputra http://aspspider.biz/saputra Choose me, train me, behave coherent and ossify to me and i promise i
Message 7 of 7 , Dec 21, 2006
View Source
• 0 Attachment
Dear Arnie,
Friday, December 15, 2006, 5:28:30 AM, you wrote:
> Wouldn't it be much simpler to do something like this:

> DECLARE @MaxValue SmallInt
> SET @MaxValue = 2

> SELECT right( '000' + cast(@MaxValue as varchar(4)), 4)

> - Arnie Rowland