Blog ala Vidar

SQL, AppFrame and other cool technologies

Monthly Archives: January 2008

Pizza and Learning

On the 29th of January, we’re planning to have Pizza & Learning in Ølen. The topic is LINUX!
Everyone that’s interested should come to Ølen, and join us 🙂

Advertisements

SQL Query tips

Happy new year, guys!

I’ve been very busy lately. In Ølen we’re making a new framework, based on winforms. The first thing we did was DROP TABLE stbl_MSAccess%. So long, suckers! Now there’s no turning back! (PS!! We will still support access in AppFrameR2). So, what are we doing? Well, we’re making a framework, based on .NET 2.0 and controls from DevExpress. We have already started making some forms and it’s starting to look pretty nice! Today I did a quick test. Made a simple form with a couple of subforms. Took me not more than 15 minutes! I hope everyone’s looking forward to this new framework 🙂

I ran into a little problem when I was making a form today. In one of the subforms we need to list all mails from/to one address, with from, to, cc, subject and the body. The problem was that the users want’s CCs in ONE column, comma separated, and it’s stored as one record pr address in the table. How did I solve this? Of course, with a little help from Torgeir and some XML-queries

SELECT M.FromEMail, M.Subject, MC.TextHTML, M.PrimKey,
  (SELECT (SELECT [data()] = EMail + ','
		FROM stbl_Mail_MessagesRecipients
		WHERE MessageRef=M.PrimKey
		  AND Type='To'
		FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS ToEMail,
  (SELECT (SELECT [data()] = EMail + ','
		FROM stbl_Mail_MessagesRecipients
		WHERE MessageRef=M.PrimKey
		  AND Type='Cc'
		FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS CCEMail
	FROM stbl_Mail_Messages AS M
	INNER JOIN stbl_Mail_MessagesContent AS MC ON M.PrimKey = MC.MessageRef