Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Case statement


Case statement

Author
Message
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2445 Visits: 2763
Hi all. I have written a script but in this I am not getting value in a column which is populating through CASE statement. Its only returning NULL

Declare @vouchertype varchar(20) = 'Value Discount',
@denomination int,
@userid int,
@transactionID varchar(20),
@voucherstatus int,
@quantity int = 10,
@count int =1,
@max int,
@vouchertype1 varchar(20),
@amountvalue int
declare @retval varchar(20)
SET @vouchertype=RTRIM(LTRIM(@vouchertype));
SET @retval=LEFT(@vouchertype,1);
SET @amountvalue = 1000
WHILE CHARINDEX(' ',@vouchertype,1)>0
BEGIN

SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));
SET @retval+=LEFT(@vouchertype,1);
END

WHILE @count <= @quantity
BEGIN
SET @max = (select vouchervaluebased from gv_maxvouchervalue)
SET @transactionID = (select vouchertransactionID from gv_maxvouchervalue)
If EXIsts (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
print @retval
BEGIN
INSERT INTO GV_Voucher
VALUES
(
1,
@retval + Replace( Str( @max+1, 7), ' ', '0') ,
20,
DATEADD(MM,6,GETDATE()),
GETDATE(),
GETDATE(),
1,
1,
'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0'),
@quantity,
CASE WHEN @vouchertype = 'Value Based' THEN 0
WHEN @vouchertype = 'Value Discount' THEN @amountvalue
WHEN @vouchertype = 'Percentage Discount' THEN @amountvalue
END
)
SET @count = @count + 1
Update gv_maxvouchervalue
SET vouchervaluebased = @max + 1
END
print @amountvalue

END
UPDATE gv_maxvouchervalue
SET vouchertransactionID = @transactionID + 1

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 3229
I think you will find that this is the problem

SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));

As you seem to be taking Right N characters, in @vouchertype, and over Writing it so If @VoucherType='Value Discount' you are then changing @vouchertype to 'Discount' so @VoucherType != 'Value Discount'

You also have a Print Statement between the IF EXISTS and the BEGIN, I believe that this will then cause everything in the Next BEGIN/END batch to execute regardless of the outcome of the EXISTS.

Eg



IF EXISTS(Select 1)
PRINT 'TRUE'
BEGIN
Print 'FALSE'
END




Both true and False are printed. Is this intended behaviour?

_________________________________________________________________________
SSC Guide to Posting and Best Practices
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2445 Visits: 2763
Jason-299789 (3/19/2013)
I think you will find that this is the problem

SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));

As you seem to be taking Right N characters, in @vouchertype, and over Writing it so If @VoucherType='Value Discount' you are then changing @vouchertype to 'Discount' so @VoucherType != 'Value Discount'

You also have a Print Statement between the IF EXISTS and the BEGIN, I believe that this will then cause everything in the Next BEGIN/END batch to execute regardless of the outcome of the EXISTS.

Eg



IF EXISTS(Select 1)
PRINT 'TRUE'
BEGIN
Print 'FALSE'
END




Both true and False are printed. Is this intended behaviour?

I used that Print statement IF EXISTS and BEGIn to check the values of a variable but if I dont write print statement then this script keeps on executing... Unsure

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 3229
I probably got hold of the wrong end of the stick.

But I think you will find the problem is the SET @Vouchertype, if you add a Print @VoucherType after the set


Declare @vouchertype varchar(20) = 'Value Discount',
@denomination int,
@userid int,
@transactionID varchar(20),
@voucherstatus int,
@quantity int = 10,
@count int =1,
@max int,
@vouchertype1 varchar(20),
@amountvalue int
declare @retval varchar(20)
SET @vouchertype=RTRIM(LTRIM(@vouchertype));
SET @retval=LEFT(@vouchertype,1);
SET @amountvalue = 1000

SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));
print @vouchertype



this Returns 'Discount', which makes the case invalid as 'Discount' doenst equal any of the cases.

I'm still not sure about the purpose of the IF EXISTS, I would have thought the original logic was that you want to check if the vouchertype exists, and if it does then add a new record and increment a sequence table.

but in its current form it will always add a row regardless of if the voucher type exists.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
AJ@SQL
AJ@SQL
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 309
I think SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1))); is actually changing the value for vouchertype.

You may want to include an ELSE as well within your CASE statement.
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2445 Visits: 2763
Jason-299789 (3/19/2013)
I probably got hold of the wrong end of the stick.

But I think you will find the problem is the SET @Vouchertype, if you add a Print @VoucherType after the set


Declare @vouchertype varchar(20) = 'Value Discount',
@denomination int,
@userid int,
@transactionID varchar(20),
@voucherstatus int,
@quantity int = 10,
@count int =1,
@max int,
@vouchertype1 varchar(20),
@amountvalue int
declare @retval varchar(20)
SET @vouchertype=RTRIM(LTRIM(@vouchertype));
SET @retval=LEFT(@vouchertype,1);
SET @amountvalue = 1000

SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));
print @vouchertype



this Returns 'Discount', which makes the case invalid as 'Discount' doenst equal any of the cases.

I'm still not sure about the purpose of the IF EXISTS, I would have thought the original logic was that you want to check if the vouchertype exists, and if it does then add a new record and increment a sequence table.

but in its current form it will always add a row regardless of if the voucher type exists.


Thanks Jason for pointing out the problem....I have changes the script and it working now..
Declare @vouchertype varchar(20) = 'Value Discount',
@denomination int,
@userid int,
@transactionID varchar(20),
@voucherstatus int,
@quantity int = 10,
@count int =1,
@max int,
@vouchertype1 varchar(20),
@amountvalue int
declare @retval varchar(20)
--Declare
SET @vouchertype1=RTRIM(LTRIM(@vouchertype));
SET @retval=LEFT(@vouchertype1,1);
SET @amountvalue = 5000
WHILE CHARINDEX(' ',@vouchertype1,1)>0
BEGIN

SET @vouchertype1 = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));
SET @retval+=LEFT(@vouchertype1,1);
END

WHILE @count <= @quantity
BEGIN
SET @max = (select vouchervaluebased from gv_maxvouchervalue)
SET @transactionID = (select vouchertransactionID from gv_maxvouchervalue)
If EXIsts (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
--print @retval
BEGIN
INSERT INTO GV_Voucher
VALUES
(
1,
@retval + Replace( Str( @max+1, 7), ' ', '0') ,
20,
DATEADD(MM,6,GETDATE()),
GETDATE(),
GETDATE(),
1,
1,
'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0'),
@quantity,
CASE WHEN @vouchertype = 'Value Based' THEN 0
WHEN @vouchertype = 'Value Discount' THEN @amountvalue
WHEN @vouchertype = 'Percentage Discount' THEN @amountvalue
END
)
SET @count = @count + 1
Update gv_maxvouchervalue
SET vouchervaluebased = @max + 1
END
--print @amountvalue

END
UPDATE gv_maxvouchervalue
SET vouchertransactionID = @transactionID + 1

But I have one more question I dont the static values in CASE statement. I only want to add amountvalue for voucher types other than 'value Based'

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 3229
The simplest way is to do this as you now have the @VoucherType1 set is to dot his



CASE @vouchertype1
WHEN 'Discount' THEN @amountvalue
ELSE 0
END




this way when you have a vouchertype that ends with 'Discount' you apply the amount otherwise you simply insert a 0.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2445 Visits: 2763
Jason-299789 (3/19/2013)
The simplest way is to do this as you now have the @VoucherType1 set is to dot his



CASE @vouchertype1
WHEN 'Discount' THEN @amountvalue
ELSE 0
END




this way when you have a vouchertype that ends with 'Discount' you apply the amount otherwise you simply insert a 0.

Thanks a lot Jason :-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search