Page 2 Big SQL Statements - Reports

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

Home All Forums

1
2
3
4
5

7/10/2008 1:56 PM

cathal connolly

www.cathal.co.uk
Joined: 4/9/2003

Posts: 9676

Re: Big SQL Statements

SadisticRabbit wrote

cathal connolly wrote

you might be better off using the reports module (http://www.dotnetnuke.com/tabid/125/default.aspx) for this kind of thing - http://www.dotnetnuke.com/Community/Blogs/tabid/825/BlogID/196/Default.aspx has a bunch of blog entries that might help give you an idea of the capabilities.

Cathal

sorry confused.. I AM using the reports module.. this is the reports module forum!??! Or did you mean something else ?

sorry, sorry, I misread the original post and thought you had written a custom module and were looking to store content in in. I'd recommend that you try using stored procedures then instead of the sql directly

Cathal

Buy the new Professional DNN7: Open Source .NET CMS Platform book Amazon US

7/10/2008 2:06 PM

Charles Nurse

Joined: 2/9/2004

Posts: 3652

Re: Big SQL Statements

What you are trying to do here is a "CrossTab" Query.

IMO - the best approach is to create a View (or temporary Table) that crosstabs the UserProfile/ProfileDefinition Table - then use this temporary table in a Join with the other table(s).

This is quite a complex problem to solve - but if you investigate (google) Crosstab or Pivots this should take you in the right direction.

Alternatively - create a user function that evaluates the Select Sub Query for you - and returns the value - this will avoid all the subqueries (eg GetProfileProperty(UserID, PropertyName) ).

Charles Nurse
Chief Architect
Evoq Content Team Lead,
DNN Corp.

Want to contribute to the Platform project? - See here

MVP (ASP.NET) and
ASPInsiders Member

7/10/2008 3:25 PM

Paul Scarlett

www.tressleworks.ca
Joined: 4/16/2004

Posts: 493

Re: Big SQL Statements

You may wany to check out SQLGridSelectedView from www.tressleworks.ca The free version of this modules will support over 8000 characters in the SQL statement ... should be enough.

Paul.

7/16/2008 11:13 AM

Robert Wiseman

Joined: 11/5/2004

Posts: 14

Re: Big SQL Statements

Couldn't you use a stored procedure? Or select from a view?

Robert Wiseman
Systems Analyst II
Memorial Health System
rwiseman@memorialsb.org

7/26/2008 5:47 PM

Alan Gore

Joined: 4/21/2008

Posts: 7

Re: Big SQL Statements

cathal connolly wrote

you might be better off using the reports module

Cathal

I'm assuming he DID use the Reports module. I had the same problem with large queries. Apparently there is a dinky query size limit. My workaround was to go to stored procedures. This allows queries of any size, and multiple statements, but makes editing one leval more difficult.

Page 2 of 2

These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
No Flaming or Trolling.
No Profanity, Racism, or Prejudice.
Site Moderators have the final word on approving / removing a thread or post or comment.
English language posting only, please.

Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

DNN Corp. (DotNetNuke)