Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Case statement Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 5:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1432574
Posted Tuesday, March 19, 2013 6:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1432585
Posted Tuesday, March 19, 2013 6:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
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...



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1432587
Posted Tuesday, March 19, 2013 6:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1432595
Posted Tuesday, March 19, 2013 6:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 10:20 PM
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.
Post #1432596
Posted Tuesday, March 19, 2013 6:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1432603
Posted Tuesday, March 19, 2013 6:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1432616
Posted Tuesday, March 19, 2013 7:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1432623
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse