Hello,

I'm having the time of my life trying to figure out this query!!!

For instance, In the udt there is a column called "Created Date" which is actually stored under UserDefinedFields

Let's say there's another field called "Total" also in that UDT.

How would I write my query if I wanted data returned in two columns with Headings "Created Date" and "Total" the corresponding values for the Row from the UDT?

I see the UDT stored proc's simply return them by UserDefinedRowId, so I would assume the UDT BLL will then loop thorugh each record, separating them by Rows.

A reason for my question could be to implement it into the Reports Module or something similar.

Regards!

---

---

Andrew Walker

If you enjoy making your own beer and/or wine - be sure to check out http://www.ForemostBrewing.com

UDT stored all data serialized for flexibility - so all user defined tables with different structures can be stores in the same database tables. the tradeoff is, that it is more difficult to access the data. it is easier, if you use SQL 2005 with the pivot statement, UDT itself used a core function that created a dataset from the serialized data.

For your concrete example I you can use the following query (assume that FieldID of CreatedDate is 132 and FieldID of Total is 143):

SELECT C2.Value as Total, C1.Value as CreatedDate 
FROM UserDefinedData AS C1 INNER JOIN UserDefinedData AS C2 on C1.RowID = C2.RowID 
WHERE C1.FieldID = 132 and C2.FieldID = 143

---

Cheers from Germany,
Sebastian Leupold

   

Speed up your DNN Websites with TurboDNN

Sebastian,

Many many thanks!!  It makes sense as I look at it.  I didn't notice your reply earlier, I must've been typing when it came through!

But one thing, it doesn't seem good to have the fields returned as ntext - well in my case the columns returned in SQL Server MGMT Studio Express, I have to scroll right to view only the second column.  But an rtrim() won't work.  Seems if alot of rows are passed, this could affect performance?

Also,  I can't select between different dates  ie: Between 1/1/2007 and 1/1/2006 because of the ntext.  Any Ideas here?  It seems my "easiest solution" isn't that after all.

---

---

Andrew Walker

If you enjoy making your own beer and/or wine - be sure to check out http://www.ForemostBrewing.com