February 11, 2008 at 2:04 pm
Greeting folks. I have this function that I use to populate the body field of my email staging table as follows.
**************************************************
ALTER FUNCTION dbo.fnNewRTC (@region VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return ,'') + CO + ' ' + WO_ID + ' ' +
ISNULL(OSP_JOB_TYPE,'NULL') + ' ' + ISNULL(JOB_SCOPE,'NULL') + ' ' + RTC + CHAR(10)
FROM NewRTC
WHERE Region = @Region
RETURN @Return
END
****************************************************
There are basically 5 fields, CO, WO_ID, OSP_JOB_TYPE, JOB_SCOPE, and RTC, which are pulled from the table NewRTC. NewRTC is populated by an UPDATE trigger on another table for when the field 'RTC' is populated with a date. Everything works as it should, but I would like to try to make the email look better. If there is only one record updated in the source table, no problem. If there are say 20 records updated, then lines in the body of the email look messy, because the lengths of the data in the five fields varies, and the columns are not in alignment. How can I alter the function to make each of the five fields in the function be the legnth of the longest of the data for each field. Does that make sense? I want it to look like justified output.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 11, 2008 at 6:34 pm
What are the datatypes and, if appropriate, sizes for the 5 different columns you're pulling from, Greg?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 11, 2008 at 11:03 pm
Jeff Moden (2/11/2008)
What are the datatypes and, if appropriate, sizes for the 5 different columns you're pulling from, Greg?
Yep, I know did the cardinal sin by not including test data Jeff, but I could not figure out how to post test data that would work, since the end result is an email. I will try to be more specific when I get to work tomorrow. Thanks for the bite Jeff.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 12, 2008 at 9:47 am
Ok, this is yet another episode of 'How not to do things, by a non-programmer'. Anyhow, I think the problem was not clearly defined in the OP, so I have narrowed it down to the essential parts, and included test setup. I think the below is pretty easy to follow, and I will add that it does *exactly* what I want it to do. I don't know if you will have a way to see the result, because for me the end result is an email, but I would guess any email procedure would behave in a similar fashion, or you might know of a way to see the results in QA. The gist is that I want all columns of data in the email to be the same length as the longest record for each column, so they look like a nice spreadsheet. Before, the email looked pretty much like the test data, but without the quotes or SQL statements. If anything is still unclear as to what I want, please reply, and keep in mind that this does work for me, so I do not necessarily need a solution, but rather, I am looking for a more efficient way. Thank you
Greg
--Drop test table if it exists, and create it
IF OBJECT_ID('test','u') IS NOT NULL
DROP TABLE test
CREATE TABLE test
(
REGION VARCHAR(20),
CO VARCHAR(30),
WO_ID VARCHAR(25),
RTC SMALLDATETIME,
TYPE VARCHAR(100) NULL,
SCOPE VARCHAR(100) NULL
)
--Insert test data
INSERT INTO test
SELECT 'North','MyTown','MYTNVAMT-2A01001','2/12/2008','FEEDER',NULL UNION ALL
SELECT 'South','HisTown','HSTNVAHT-3A01001','2/12/2008','DISTRIBUTION','TERMINAL' UNION ALL
SELECT 'North','YourTown','YRTNVAYT-4A01001','2/12/2008','OTHER','CONDUIT' UNION ALL
SELECT 'South','HerTown','9SM-5501-2KM01AA','2/12/2008',NULL,NULL UNION ALL
SELECT 'North','TheirTown','9SD-5502-2KM01AA-01','2/12/2008',NULL,'DAMAGE'
--Create the function to populate body field of email table
ALTER FUNCTION dbo.fnTest (@region VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return ,'') + CO + ' '
+ WO_ID + ' ' +
+ CONVERT(CHAR(10),RTC,101) + ' ' + TYPE + ' '
+ SCOPE + CHAR(10)
FROM test
WHERE Region = @Region
RETURN @Return
END
--Create temp table to hold padding values for up to 20 padding spaces
IF OBJECT_ID('TempDB..#pad','u') IS NOT NULL
DROP TABLE #pad
CREATE TABLE #pad
(
[Len] TINYINT,
Pad CHAR(22)
)
--Populate the pad table with padding lengths of 1 to 20 spaces
DECLARE @Pad CHAR(20)
SELECT @Pad = ' '
INSERT INTO #pad
SELECT n,SUBSTRING(@pad,1,n)
FROM tally
WHERE n <= 20
--UPDATE CO column of test table to add pad to each CO whose
--LEN is less than MAX(LEN(co)). Table t2 will provide the amount
--of padding required.
UPDATE test
SET co = co + '' + SUBSTRING(pad,1,COPad)
FROM test t1,
(--Determine the amount of padding required for each CO
SELECT
WO_ID,
COPad = (SELECT MAX(LEN(co)) FROM test)-LEN(co)
FROM test
) t2,
#pad p
WHERE t1.wo_id = t2.wo_id
AND t2.copad = p.[len]
--UPDATE wo_id to add padding where required, as above
UPDATE test
SET wo_id = t1.wo_id + '' + SUBSTRING(pad,1,WO_IDPad)
FROM test t1,
(--Determine the padding required for each WO_ID
SELECT
WO_ID,
WO_IDPad = (SELECT MAX(LEN(wo_id)) FROM test)-LEN(wo_id)
FROM test
) t2,
#pad p
WHERE t1.wo_id = t2.wo_id
AND t2.WO_IDpad = p.[len]
--UPDATE NULL types to 'NULL', so I can add padding for one thing,
--but, since the data comes from another database feed, I want the
--null values displayed as 'NULL'
UPDATE test
SET type = 'NULL'
WHERE type IS NULL
--UPDATE type to add pad where necessarey.
UPDATE test
SET type = t1.type + '' + SUBSTRING(pad,1,TypePad)
FROM test t1,
(--Determing padding required for Type
SELECT
WO_ID,
TypePad = ISNULL((SELECT MAX(LEN(type)) FROM test)-LEN(type),0)
FROM test
) t2,
#pad p
WHERE t1.wo_id = t2.wo_id
AND t2.TypePad = p.[len]
--No need to add any padding to Scope, since it is last in line
--Send the test email
DECLARE @Body VARCHAR(4000)
SELECT @Body =(SELECT DISTINCT
dbo.fnTest(region)
FROM test
WHERE region = 'north') + CHAR(10) + CHAR(13) +
(SELECT DISTINCT
dbo.fnTest(region)
FROM test
WHERE region = 'south')
EXECUTE dbo.send_cdosysmail
@To = 'gregory.t.snidow@[mydomain]',
@From = 'FTTPVACMC',
@Subject = 'Test',
@Body = @Body
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 12, 2008 at 10:03 am
I do something kind of similar using the SPACE function to reserve a certain amount of space in a string; I used a default of 20 in this example, but i'd change it to the maximum size for the fields definition.
ALTER FUNCTION dbo.fnNewRTC (@region VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SELECT @Return = ISNULL(@Return ,'') + SPACE(20 - LEN(CO) ) + CO + ' '
+ SPACE(20 - LEN(WO_ID) ) + WO_ID + ' '
+ SPACE(20 - LEN(ISNULL(OSP_JOB_TYPE,'NULL')) ) + ISNULL(OSP_JOB_TYPE,'NULL') + ' '
+ SPACE(20 - LEN(ISNULL(JOB_SCOPE,'NULL')) ) + ISNULL(JOB_SCOPE,'NULL') + ' '
+ SPACE(20 - LEN(RTC) ) + RTC + CHAR(10)
FROM NewRTC
WHERE Region = @Region
RETURN @Return
END
Lowell
February 12, 2008 at 1:51 pm
Thanks Lowell. I have not tried it yet, but I should have time tomorrow. I searched past articles, and found the PAD function, that may or may not have been what I needed, but I could not find it in BOL. Maybe it is not in 2000? Anyhow, I figured there had to be an easier way. Thanks for the tip.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 12, 2008 at 4:53 pm
Greg,
Would you post one of your emails that has the correct format, please? Be sure to use a code window (Click on "IFCode" in the menu of the edit window of a new message to find it) so that leading spaces and embedded spaces are preserved. You may also want to use the correct font so we get a real live picture of what you're trying to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2008 at 8:21 am
Jeff Moden (2/12/2008)
Greg,Would you post one of your emails that has the correct format, please? Be sure to use a code window (Click on "IFCode" in the menu of the edit window of a new message to find it) so that leading spaces and embedded spaces are preserved. You may also want to use the correct font so we get a real live picture of what you're trying to do.
Ok, below is what it used to look like in my email. It is not terribly bad, but it can be confusing for people who do not understand what all the columns mean. Font is not really an issue, as I would have absolutely no idea how to change the font of an email. This is a pure cut and paste from my inbox.
The following jobs have been released, and need to be scheduled:
REGION: CO: WO_ID: RTC: TYPE: SCOPE:
North MyTown MYTNVAMT-2A01001 02/12/2008 FEEDER NULL
North YourTown YRTNVAYT-4A01001 02/12/2008 OTHER CONDUIT
North TheirTown 9SD-5502-2KM01AA-01 02/12/2008 NULL DAMAGE
North RidiculouslyLongTown RLTNVARL-4A05001 02/12/2008 VERY LONG JOB TYPE NULL
North RidiculouslyLongTown RLTNVARL-4A01002 02/12/2008 NULL VERY LONG JOB SCOPE
South South HisTown HSTNVAHT-3A01001 02/12/2008 DISTRIBUTION TERMINAL
South HerTown 9SM-5501-2KM01AA 02/12/2008 NULL NULL
South HisTown HSTNVAHT-2P03001-027 02/12/2008 NULL PATH CREATION
Now, I have managed to get it closer to what I would like it to look like, but I am pretty much at the limit of my skills. Ideally, I would like the column headers to be centered above the columns, rather than my just hard coding them into the body of the email. I can't get my head around that one yet, but I am still working on it. This is, again, a pure cut and paste from my inbox.
The following jobs have been released, and need to be scheduled:
REGION: CO: WO_ID: RTC: TYPE: SCOPE:
North MyTown MYTNVAMT-2A01001 02/12/2008 FEEDER NULL
North YourTown YRTNVAYT-4A01001 02/12/2008 OTHER CONDUIT
North TheirTown 9SD-5502-2KM01AA-01 02/12/2008 NULL DAMAGE
North RidiculouslyLongTown RLTNVARL-4A05001 02/12/2008 VERY LONG JOB TYPE NULL
North RidiculouslyLongTown RLTNVARL-4A01002 02/12/2008 NULL VERY LONG JOB SCOPE
South HisTown HSTNVAHT-3A01001 02/12/2008 DISTRIBUTION TERMINAL
South HerTown 9SM-5501-2KM01AA 02/12/2008 NULL NULL
South HisTown HSTNVAHT-2P03001-027 02/12/2008 NULL PATH CREATION
I managed to improve the appearance using the exact code I posted yesterday, so it works to align the columns, but I think it could still be better. I did beef up the mail procedure code I posted yesterday, so as to be more consistent with the real procedure. Below is the procedure basically as I use in production. I also added 'Region' to the function, which I do in production, but I just did not do it for the test data.
DECLARE @Body VARCHAR(4000)
SELECT @Body =
'The following jobs have been released, and need to be scheduled:' + CHAR(10) + CHAR(13) +
'REGION: CO: WO_ID: RTC: TYPE: SCOPE:' + CHAR(10) + CHAR(13) +
(SELECT DISTINCT
dbo.fnTest(region)
FROM test
WHERE region = 'north') + CHAR(10) + CHAR(13) +
(SELECT DISTINCT
dbo.fnTest(region)
FROM test
WHERE region = 'south')
EXECUTE dbo.send_cdosysmail
@To = 'gregory.t.snidow@verizon.com',
@From = 'FTTPVACMC',
@Subject = 'Test',
@Body = @Body
So I guess now, the main thing I would want to do it to center the headers. Thanks for offering to look at it.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 13, 2008 at 9:51 am
Greg -
does your e-mail system support HTML? If so - there is a trick with XML that might work. The cure may be worse thant what you're trying to fix though.
In short - outputting your query as xml MIGHT fool it into using that as HTML markup. The XML would essentially emulate the HTML syntax for a table (which makes columns and rows line up, etc...)
a VERY simplified example would look like this (interestingly enough - it's also a rather straightforward way to PIVOT data in a sort-of, kind of way).
select company as '@id',
(select ident as 'text()'
from matricxml mi
where mi.company=mo.company
FOR XML PATH('TD'),TYPE)
from (select distinct company from matricxml) mo
--order by company,ident
FOR XML PATH('TR'), root('TABLE')
Anyway - let me know and I will see if I can adapt something to your specifics (I would of course need some table specifics, etc...)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 13, 2008 at 10:32 am
Matt... is that going to work in 2k?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2008 at 10:44 am
dang it - missed what forum I was in.
ugh....No XML PATH in 2000.... So the syntax gets quite a bit longer.....
So - that particular example, no. Not entirely irretrievable, but does get quite a bit harder. we'd have to go EXPLICIT, with the unions that entails.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 13, 2008 at 11:25 am
About this time, I'm thinking we stop fighting city hall on formats and let something else do the formatting for us... and much nicer than we can make in raw T-SQL without writting a custom page formatter with all the HTML garbonzo's we'd need...
... Greg, ever heard of "sp_MakeWebTask"? Read up on it in Books Online and we'll talk some more 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2008 at 8:43 am
Another way of formatting variable-length strings:
(there must be at least a half-dozen more...)
[font="Courier New"]
DECLARE @ls_10_Blanks = ' '
DECLARE @ls_20_Blanks = @ls_10_Blanks + @ls_10_Blanks
SELECT
. LEFT(EmployeeName + @ls_20_Blanks, 20) AS Emp_Name,
. RIGHT(@ls_10_Blanks + EmployeeCode, 10) AS Emp_Code
FROM Employee
[/font]
[font="Arial"]
To Get
[/font]
[font="Courier New"]
Emp_Name Emp_Code
-------------------- ----------
Cosmo E10
Kramer E3
[/font]
February 14, 2008 at 1:08 pm
Jeff Moden (2/13/2008)
... Greg, ever heard of "sp_MakeWebTask"? Read up on it in Books Online and we'll talk some more 😉
Well, as a matter of fact I have indeed heard of it. One time a couple of months ago I came across it and used it with a test query. I typed in the address of the folder I named in IE and voila...up came a web page with my query in a nice little table, with the column headers in bold. Then I...well...forgot about it. So how could this help me?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 14, 2008 at 8:29 pm
The columns are self sizing... makes real pretty emails... you send an email with the file as an attachment.
ie... SUBJ: Your daily report on whatever is attached.
Now, that can also get expensive on the email server... you could also have a subject and body on the email that looks like this...
SUBJ: Your daily report is ready at the following URL:
Please double click on the following to see your report...
\\ReportServer\CorporateReports\Report-GOR-20080214.html
😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply