May 18, 2015 at 2:43 am
I have a table Alert_Types with these fields :
alert_id,
alert_source,
body,
Now whenever a new alert is registered record goes in table like
alert_id = 1,
alert_source= document_detail_view,
body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty
it was simple to parse simple variables using replace functions. eg. REPLACE(@str, '@customer_name', @customer_name). It worked like mail merge.
the converted string was then sent forward using a webservice.
now my requirement is to add conditional values in body field e.g:
body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty
IF isnull(@rate, 0) > 0 Rate: @rate
IF isnull(@rate, 0) > 0 Amount: @amount
how can i parse strings like this. I'm open to change format of values for body field.
May 18, 2015 at 3:11 am
Would you be able to provide sample DDL of how you currently do the insert?
May 18, 2015 at 3:21 am
its a simple insert into statement
insert into Alert_Types
values (1,'document_detail_view','Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty')
before forwarding to webservice i recall the body in a variable @STR
and use replace function to replace variable names with their corresponding values
May 18, 2015 at 3:26 am
raza.qadri (5/18/2015)
its a simple insert into statementinsert into Alert_Types
values (1,'document_detail_view','Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty')
So you have the variables available (@document_id etc.), INSERT a generic string and then do a UPDATE with replace?
May 18, 2015 at 3:34 am
Does something like this work? Build the 'Body' string before the insert? Although if you have all the values, why not store them all? What happens when you wish to make a change to one of the values?
DECLARE@document_id INT = 1,
@Customer_Name VARCHAR(25) = 'Chuffty McChuff',
@item_name VARCHAR(25) = 'Example Item',
@qty INT = 1000,
@Rate DECIMAL(5,4) = 1.5,
@Amount INT = 100,
@BodyText VARCHAR(255);
SET @BodyText = 'Document ID: ' + CAST(@document_id AS VARCHAR) +
+ ' Customer Name: ' + @customer_name +
+ ' Item name: ' + @item_name +
+ ' Quantity: ' + CAST(@qty AS VARCHAR)
+ CASE WHEN ISNULL(@RATE,0) > 0 THEN ' Rate: ' + CAST(@Rate AS VARCHAR) + ' Amount: ' + CAST(@Amount AS VARCHAR) ELSE '' END;
SELECT @BodyText;
INSERT INTO dbo.Alert_Types (alert_id,alert_source,body)
VALUES (1,'document_detail_view',@BodyText);
May 18, 2015 at 4:05 am
Thanks for your detailed response.
But let me add some info.
I have a table for alert registration (Alert_types). Then alerts are generated from document table and stored in alert_que table.
The process of alert generation is to read alert format(body) from alert_types and the replace variable names with variable values.
Then a service runs and sends the alerts from alert_que.
So i want to store alert format in alert_registration and then parse this format when generating alert for que and store complete body string with values.
May 18, 2015 at 4:27 am
So the table has the generic alert text that you wish to update? Are there other types of alerts with different messages?
2 examples below, one to conditionally append the rate + amount element to the string, but this would mean it wouldn't be held in the generic body text, second assumes it is included and deals with it conditionally
DECLARE@document_id INT = 1,
@Customer_Name VARCHAR(25) = 'Chuffty McChuff',
@item_name VARCHAR(25) = 'Example Item',
@qty INT = 1000,
@Rate DECIMAL(5,4) = 0,
@Amount INT = 100,
@BodyText VARCHAR(255) = 'Document ID: @document_id Customer Name: @customer_name Item name: @item_name Quantity: @qty';
SELECT@BodyText;
SET @BodyText = REPLACE(REPLACE(REPLACE(REPLACE(@BodyText,'@document_id',@document_id),'@customer_name',@Customer_Name),'@item_name',@item_name),'@qty',@qty)
+ CASE WHEN ISNULL(@RATE,0) > 0 THEN ' Rate: ' + CAST(@Rate AS VARCHAR) + ' Amount: ' + CAST(@Amount AS VARCHAR) ELSE '' END;
SELECT@BodyText;
SET @BodyText = 'Document ID: @document_id Customer Name: @customer_name Item name: @item_name Quantity: @qty Rate: @Rate Amount: @Amount';
SELECT@BodyText;
SET @BodyText = CASE
WHEN ISNULL(@RATE,0) > 0 THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@BodyText,'@document_id',@document_id),'@customer_name',@Customer_Name),'@item_name',@item_name),'@qty',@qty),'@Rate',@Rate),'@Amount',@Amount)
ELSE REPLACE(REPLACE(REPLACE(REPLACE(LEFT(@BodyText,LEN(@BodyText)-28),'@document_id',@document_id),'@customer_name',@Customer_Name),'@item_name',@item_name),'@qty',@qty)
END
SELECT@BodyText;
May 18, 2015 at 4:59 am
This is a good example but unfortunately there are many many alerts with different messages. Therefore i have to stick to input string in generic string in alert_types table.
May 18, 2015 at 5:07 am
I have attached variables list. User can select any variable from this list
May 18, 2015 at 5:13 am
So you take the body text from the alert_types table and body text can differ (different text, different variables) depending on the type of alert
How do you currently parse the body string before sending it through to your web service.
Nested replace for every possible variable in your attached diagram?
May 18, 2015 at 6:23 am
Yes, currently i have nested replaces for all possible variables.
And currently alert messages are defined in procedure.
That's why i want to make alerts dynamic because new alerts are defined on regular basis and procedure needs updating. I want alerts to be defined in db so that a single procedure can handle all alerts.
May 18, 2015 at 6:52 am
How many conditional scenarios are there and do they apply to all alerts?
i.e. would @Rate > 0 apply Rate + Amount be the same for both alert 1 and 2?
May 18, 2015 at 9:18 am
There are 5 conditional variables.
Not all apply to all alerts.
conditional variables are:
- @subledger_name
- @cost_centre
- @rate
- @amount
- @JobOrder
Only rate and amount go together all other are independent.
Alert is generated for 1 document and sent to multiple users. For taht specific document variable values are same.
May 19, 2015 at 2:33 pm
ive been thinking a lot.
Can someone help me out ?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply