By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 759 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Crosstab subqueries

P: 3
Hi everyone,

I can't seem to get this query right. It seems that crosstab queries don't support subqueries or is it? How should i rewrite this? It's too nasty to expand the whole query in four statements and union them together...

Expand|Select|Wrap|Line Numbers
  1. SELECT t1.companyname AS Supplier, t1.questionid, t1.Assigned, t1.Question, t1.Weight, c.CategoryName AS Category, t1.Score
  2. FROM (
  3.  
  4. SELECT s2.companyname, q.id as questionid, IIf((select count(*) from assignedquestions as aq where aq.assignedto=[MemberID] and aq.questionid=q.id )>0,"X","") AS Assigned, q.questionnumber as Question, q.questionweight as Weight, q.category as Category,
  5.  
  6. (select score from score as s where s.teammemberid=[MemberID] and s.supplierid=s2.id and s.questionid=q.id) as Score FROM Question AS q, supplier AS s2
  7. GROUP BY q.id, q.questionnumber, q.questionweight, q.category, s2.id, s2.companyname
  8.  
  9. )  AS t1 LEFT JOIN Category AS c ON t1.Category = c.id;
  10.  
  11.  
Mar 27 '10 #1
Share this Question
Share on Google+
4 Replies


patjones
Expert 100+
P: 931
Hi -

One thing you might want to check is what you are selecting in the first subquery versus what's in the GROUP BY clause. When you're grouping, whatever you are SELECTing has to appear in GROUP BY. In your case, the correlated subqueries that you are aliasing as 'Assigned' and 'Score' don't appear in the grouping.

It seems like you might need to do the grouping first, then join the result to 'Assigned' and 'Score' to get what you want.

Pat
Mar 27 '10 #2

P: 3
Thanks for your reply Zepphead80.

However, I tried grouping them in the outer query (you cant do this in the inner query because of the aliases). This still gives the following error message: "Database engine does not recognize q.id as a valid field name or expression"

This is the adjusted query:
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS MemberID Short;
  2. TRANSFORM Sum(t1.Score) AS SumOfScore
  3. SELECT t1.questionid, t1.Assigned, t1.Question, t1.Weight, c.CategoryName AS Category
  4. FROM (SELECT s2.companyname, q.id as questionid, IIf((select count(*) from assignedquestions as aq where aq.assignedto=[MemberID] and aq.questionid=q.id )>0,"X","") AS Assigned, q.questionnumber as Question, q.questionweight as Weight, q.category as Category,
  5.  
  6. (select score from score as s where s.teammemberid=[MemberID] and s.supplierid=s2.id and s.questionid=q.id) as Score FROM Question AS q, supplier AS s2
  7. GROUP BY q.id, q.questionnumber, q.questionweight, q.category, s2.id, s2.companyname
  8.  
  9. )  AS t1 LEFT JOIN Category AS c ON t1.Category = c.id
  10. GROUP BY t1.questionid, t1.Assigned, t1.Question, t1.Weight, c.CategoryName
  11. PIVOT t1.companyname;
  12.  
Do you know what I am doing wrong? It seems that you cant refer to fields outside the subquery in a crosstab, am i correct?
Mar 29 '10 #3

patjones
Expert 100+
P: 931
christian -

I have a deadline today and won't be able to look at your query for a bit, but I will take a look at it in the next day or so. If you should happen to figure out the problem before then, just drop a line here to let us know what it was.

Pat
Mar 30 '10 #4

patjones
Expert 100+
P: 931
If you try to run the main subquery by itself (notice that I have included 'Assigned' and 'Score' in the GROUP BY clause), what happens?

Expand|Select|Wrap|Line Numbers
  1. SELECT s2.companyname,
  2.         q.id AS questionid,
  3.         IIf((SELECT COUNT(*) FROM assignedquestions AS aq WHERE aq.assignedto=[MemberID] AND aq.questionid=q.id)>0,"X","") AS Assigned,
  4.         q.questionnumber AS Question,
  5.         q.questionweight AS Weight,
  6.         q.category AS Category
  7.         (SELECT score FROM score AS s WHERE s.teammemberid=[MemberID] AND s.supplierid=s2.id AND s.questionid=q.id) AS Score
  8. FROM Question AS q, supplier AS s2
  9. GROUP BY q.id, q.questionnumber, q.questionweight, q.category, s2.id, s2.companyname, Score, Assigned
Mar 31 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.