Crosstab subqueries

[christian85]

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...

SELECT t1.companyname AS Supplier, t1.questionid, t1.Assigned, t1.Question, t1.Weight, c.CategoryName AS Category, t1.Score
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,

(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
GROUP BY q.id, q.questionnumber, q.questionweight, q.category, s2.id, s2.companyname

)  AS t1 LEFT JOIN Category AS c ON t1.Category = c.id;

[patjones]

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

[christian85]

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:

PARAMETERS MemberID Short;
TRANSFORM Sum(t1.Score) AS SumOfScore
SELECT t1.questionid, t1.Assigned, t1.Question, t1.Weight, c.CategoryName AS Category
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,
 
(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
GROUP BY q.id, q.questionnumber, q.questionweight, q.category, s2.id, s2.companyname
 
)  AS t1 LEFT JOIN Category AS c ON t1.Category = c.id
GROUP BY t1.questionid, t1.Assigned, t1.Question, t1.Weight, c.CategoryName
PIVOT t1.companyname;

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?

[patjones]

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

[patjones]

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?

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
	    (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
GROUP BY q.id, q.questionnumber, q.questionweight, q.category, s2.id, s2.companyname, Score, Assigned