Build Multi Part String Based on Multiple Variables 1

[Bonediggler1]

Hello

I am performing a pricing validation based on data in a table and based on this validation will update a comment field in the same table. The sample data might be:

Field1 Field2 Comment
$10 $5 "Field1 should be $8 - Field 2 should be $3"


To do this, I wanted to basically build a string based on the results of the pricing validation, and then update the Comment field with that string. Something like:

DECLARE @STRING VARCHAR(MAX)

UPDATE PRICING_TABLE

SET COMMENT =

CASE WHEN FIELD1 <> '10' THEN SET @STRING = 'Field1 should be $8' END
CASE WHEN FIELD2 <> '5' THEN SET @STRING = @STRING + '-Field2 Should be $3' END

-----------------------------

Unfortunately this method does not seem possible as I get a lot of incorrect syntax errors. Any suggestions?

 

[Qik3Coder]

You need to generate the entire string at the same time you are doing the update. You don't have access to variables, though this is not a problem.

You will want to save any existing comments, and restrict your update list to only the ones that may have potential problems.

Update Table
set comment = 
[tab]comment + 
[tab]Case when field1 <> 10 then ' Field3 != $8' else '' END + 
[tab]Case when field2 <> 5 then ' Field4 != $3' else '' END
[b]WHERE (field1 <> 10 or field2 <> 5) [!]AND status = 'OPEN'[/!][/b]

Lodaiden


You've got questions and source code. We want both!

 

[Bonediggler1]

Looks like this will do the trick. Thank for the help!

 

[Bonediggler1]

Let me take a step back - this is a move in the right direction, however when I start putting nested case statements in the logic, it stops working. I.e.:

Update Table
set comment =
comment +
Case when field1 <> 10 then ' Field3 != $8' else '' END +
Case when field2 <> 5 then ' Field4 != $3' else '' END +

CASE WHEN STATE = 'LA' AND FLAG4 = 'X' THEN
CASE WHEN FIELD5 <> 6 THEN 'Field5 <> 6' ELSE '' END +
CASE WHEN FIELD6 <> 11 THEN 'Field6 <> 11' ELSE '' END +
WHEN STATE <> 'LA' AND FLAG4 = 'X' THEN
CASE WHEN FIELD5 <> 7 THEN 'Field5 <> 7 ELSE '' END

ETC...

 

[Qik3Coder]

You should probably take a step back here.
You're manually validating a lot of fields.

If you have access to the application that is providing this data, i suggest adding those validations there, preventing the bad data from being entered.

With that said, if this comment field is only for you, then I suggest breaking this into a multi step process.

Clear the comment field on all possible records.
Update with comments for FieldX
Update with comments for FieldY


Lodlaiden

You've got questions and source code. We want both!

 

[Bonediggler1]

Yes - the entire process surrounding data for the client in question is an utter mess - and the fields and related validations are in the dozens. I cannot validate data at the source (Oracle) so am doing it via an extract into SQL Server.

Regardless, your suggestion to break the updates into seperate chunks is a good one.

Thanks again!

 

[Bonediggler1]

Sorry to keep this going, but the logic of

"SET COMMENT = COMMENT + XYZ"

is not working. I broke the logic into blocks as suggested, but each block of logic will overwrite the existing value in the COMMENT field.

Is there any way to update an existing value by concatenating it with another value?

 

[Qik3Coder]

Post one of the update blocks.

This works:

Declare @_testString varchar(20)
select @_testString = 'A'
Select @_testString = @_TestString + 'B'
Select @_testString

Lod

You've got questions and source code. We want both!

 

[Bonediggler1]

UPDATE PRICING
SET
COMMENT =
COMMENT +

CASE WHEN SUBSTRING(CL_RM_FLAG,2,1) = 'C' AND PHARM_FLAG IS NULL THEN

/*Non VA*/
CASE WHEN JURISDICTION_ST <> 'VA' THEN
CASE WHEN FEE_W <> '6.75' THEN 'FEE should be $6.75\' ELSE '' END +
CASE WHEN (P_FEE_W/NULLIF(P_REDUCTION,0)) NOT BETWEEN .18 AND .21 THEN 'P Fee W should be 19%\' ELSE '' END +
CASE WHEN (O_FEE_W/NULLIF(O_REDUCTION,0)) NOT BETWEEN .18 AND .21 THEN 'O Fee W should be 19%\' ELSE '' END

/*VA*/
WHEN JURISDICTION_ST = 'VA' THEN
CASE WHEN B_FEE_W <> '6.75' THEN 'Fee W should be $6.75\' ELSE '' END +
CASE WHEN (P_FEE_W/NULLIF(P_REDUCTION,0)) NOT BETWEEN .029 AND .031 THEN 'P Fee W should be 3%\' ELSE '' END
END

END
GO

 

[Qik3Coder]

UPDATE PRICINGSET    COMMENT =    COMMENT +    
 CASE WHEN SUBSTRING(CL_RM_FLAG,2,1) = 'C'  AND PHARM_FLAG IS NULL THEN
	/*Non VA*/
	CASE WHEN JURISDICTION_ST <> 'VA' THEN
		CASE WHEN FEE_W <> '6.75' THEN 'FEE should be $6.75\' ELSE '' END +
			CASE WHEN (P_FEE_W/NULLIF(P_REDUCTION,0)) NOT BETWEEN .18 AND .21 THEN 'P Fee W should be 19%\' ELSE '' END +
			CASE WHEN (O_FEE_W/NULLIF(O_REDUCTION,0)) NOT BETWEEN .18 AND .21 THEN 'O Fee W should be 19%\' ELSE '' END
                                                  
			/*VA*/            
	WHEN JURISDICTION_ST = 'VA' THEN
		CASE WHEN B_FEE_W <> '6.75' THEN 'Fee W should be $6.75\' ELSE '' END +
			CASE WHEN (P_FEE_W/NULLIF(P_REDUCTION,0)) NOT BETWEEN .029 AND .031 THEN 'P Fee W should be 3%\' ELSE '' END
	[!]--NO ELSE HERE; Impossible to get to, so moot point[/!]
		END
	[green]--NO ELSE HERE Resulting in a NULL Value when [!]CL_RM_Flag = 'C' and Pharm_Flag IS NOT NULL[/!]
[/green]	END GO

Lod

You've got questions and source code. We want both!

 

[Bonediggler1]

Correct again...thank you!