Complex Subquery: ... FROM (TRANSFORM.... Syntax error

S

Stephen Rasey

Summary Question:

Is it possible to in Access SQL or SQLServer SQL to have a Compound SQL
statement where one of the inner queryies is a crosstab TRANSFORM query as
in:



SELECT A.*,B.*

FROM

(TRANSFORM Score
SELECT t2.C, t2.D FROM T2
GROUP By T2.C, T2D
ORDER BY T2.C

PIVOT T2.E) As A

INNER JOIN B ON A.C = B.C;



I get a syntax error. I suspect that a TRANSFORM cannot be an inline
subquery, but maybe there is a simple syntax error.



Stephen Rasey

Houston

http://wiserways.com

http://excelsig.org



Question in Detail:



This is a cross tab query that works.

Call it J1T4qxt.

TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score

SELECT T2.A2IDSet, T2.OO, T2.DD

FROM (H80 INNER JOIN H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY T2.OO

PIVOT H80.IDTraceH;



Note the WHERE ... IN clause. I need the PK's in the cross tab query to
have high speed.



I need to sort the results based upon an Aggregate. This, too, runs
quickly.

A subquery for the sort is J1T5qnu

SELECT T2.A2IDSet, T2.OO, T2.DD, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC) AS
AvgOfCC, Avg(H71.TT) AS AvgOfTT

FROM (H80 INNER JOIN AS H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY Avg(H71.YY), Avg(H71.CC);



This WHERE has the same IN Clause as J1T4.



I join J1T4 and J1T5 for the final result. It is a fast query for what it
does. About 3 sec.

J1T6:

SELECT T5.OO, T5.DD, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*

FROM J1T5qnu AS T5 INNER JOIN J1T4qxt AS T4 ON T5.OO = T4.OO

ORDER BY T5.AvgOfYY, T5.AvgOfCC;





The problem is that J1T5 and J1T6 both have an ' In (1410,1488,1520) '
clause that is necessary for speed of execution.




To make this useful, the parent application must change the IN clause
dynamically.



My options are:

1. use Parameter queries for J1T5 and J1T4. I've never tried Parameters
for an entire IN clause so I don't know if that will work at all. I guess
this is another question to post.

2. Create another table H81 that has only the IDTraceH values to use and
rewrite J1T5 and J1T6 to JOIN on H81.

3. Build J1T4 and J1T5 in QueryDef and Execute J1T6. I think option 2
is superior to this.

4. Create a compound SQL string programatically that will look something
like this:




SELECT T5.OO, T5.DD, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*

FROM
(SELECT T2.A2IDSet, T2.OO, T2.DD, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC) AS
AvgOfCC, Avg(H71.TT) AS AvgOfTT

FROM (H80 INNER JOIN AS H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY Avg(H71.YY), Avg(H71.CC))

AS T5

INNER JOIN

( TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score

SELECT T2.A2IDSet, T2.OO, T2.DD

FROM (H80 INNER JOIN H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY T2.OO

PIVOT H80.IDTraceH)

AS T4 ON T5.OO = T4.OO

ORDER BY T5.AvgOfYY, T5.AvgOfCC;




When I try this, I get a " Syntax Error in FROM Clause" and TRANSFORM is
highlighted.



I have pretty well decided to go with Option 2. But I thought I would ask:



Does anyone have a why of making Option 4 work?



Can anyone think of an option 5?



Thank you for your time.



Stephen Rasey

Houston

http://wiserways.com

http://excelsig.org
 
M

Michel Walsh

Hi,


Score is neither aggregated, neither Group, neither an expression of later
two, so the crosstab query won't work, all by itself, as a stand alone
query.


Even if the crosstab is working as a stand alone, it cannot be used as
virtual table. On the other hand, you can use a saved crosstab query.


Hoping it may help,
Vanderghast, Access MVP


Stephen Rasey said:
Summary Question:

Is it possible to in Access SQL or SQLServer SQL to have a Compound SQL
statement where one of the inner queryies is a crosstab TRANSFORM query as
in:



SELECT A.*,B.*

FROM

(TRANSFORM Score
SELECT t2.C, t2.D FROM T2
GROUP By T2.C, T2D
ORDER BY T2.C

PIVOT T2.E) As A

INNER JOIN B ON A.C = B.C;



I get a syntax error. I suspect that a TRANSFORM cannot be an inline
subquery, but maybe there is a simple syntax error.



Stephen Rasey

Houston

http://wiserways.com

http://excelsig.org



Question in Detail:



This is a cross tab query that works.

Call it J1T4qxt.

TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score

SELECT T2.A2IDSet, T2.OO, T2.DD

FROM (H80 INNER JOIN H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY T2.OO

PIVOT H80.IDTraceH;



Note the WHERE ... IN clause. I need the PK's in the cross tab query to
have high speed.



I need to sort the results based upon an Aggregate. This, too, runs
quickly.

A subquery for the sort is J1T5qnu

SELECT T2.A2IDSet, T2.OO, T2.DD, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC) AS
AvgOfCC, Avg(H71.TT) AS AvgOfTT

FROM (H80 INNER JOIN AS H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY Avg(H71.YY), Avg(H71.CC);



This WHERE has the same IN Clause as J1T4.



I join J1T4 and J1T5 for the final result. It is a fast query for what
it
does. About 3 sec.

J1T6:

SELECT T5.OO, T5.DD, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*

FROM J1T5qnu AS T5 INNER JOIN J1T4qxt AS T4 ON T5.OO = T4.OO

ORDER BY T5.AvgOfYY, T5.AvgOfCC;





The problem is that J1T5 and J1T6 both have an ' In (1410,1488,1520) '
clause that is necessary for speed of execution.




To make this useful, the parent application must change the IN clause
dynamically.



My options are:

1. use Parameter queries for J1T5 and J1T4. I've never tried
Parameters
for an entire IN clause so I don't know if that will work at all. I
guess
this is another question to post.

2. Create another table H81 that has only the IDTraceH values to use and
rewrite J1T5 and J1T6 to JOIN on H81.

3. Build J1T4 and J1T5 in QueryDef and Execute J1T6. I think option 2
is superior to this.

4. Create a compound SQL string programatically that will look something
like this:




SELECT T5.OO, T5.DD, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*

FROM
(SELECT T2.A2IDSet, T2.OO, T2.DD, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC) AS
AvgOfCC, Avg(H71.TT) AS AvgOfTT

FROM (H80 INNER JOIN AS H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY Avg(H71.YY), Avg(H71.CC))

AS T5

INNER JOIN

( TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score

SELECT T2.A2IDSet, T2.OO, T2.DD

FROM (H80 INNER JOIN H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY T2.OO

PIVOT H80.IDTraceH)

AS T4 ON T5.OO = T4.OO

ORDER BY T5.AvgOfYY, T5.AvgOfCC;




When I try this, I get a " Syntax Error in FROM Clause" and TRANSFORM is
highlighted.



I have pretty well decided to go with Option 2. But I thought I would
ask:



Does anyone have a why of making Option 4 work?



Can anyone think of an option 5?



Thank you for your time.



Stephen Rasey

Houston

http://wiserways.com

http://excelsig.org
Click to expand...
 
S

Stephen Rasey

Score is aggregated as FIRST(). the CrossTab works by itself.

The failure is that you cannot use a non-fixed field as a output of a
subquery. While technically you can use a crosstab query as a subquery,
you cannot use the column header and value results in the parent query.
So there is no point in using a cross tab in a subquery.

Thanks for the reply
Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org



Michel Walsh said:
Hi,


Score is neither aggregated, neither Group, neither an expression of later
two, so the crosstab query won't work, all by itself, as a stand alone
query.


Even if the crosstab is working as a stand alone, it cannot be used as
virtual table. On the other hand, you can use a saved crosstab query.


Hoping it may help,
Vanderghast, Access MVP


Stephen Rasey said:
Summary Question:

Is it possible to in Access SQL or SQLServer SQL to have a Compound SQL
statement where one of the inner queryies is a crosstab TRANSFORM query as
in:



SELECT A.*,B.*

FROM

(TRANSFORM Score
SELECT t2.C, t2.D FROM T2
GROUP By T2.C, T2D
ORDER BY T2.C

PIVOT T2.E) As A

INNER JOIN B ON A.C = B.C;



I get a syntax error. I suspect that a TRANSFORM cannot be an inline
subquery, but maybe there is a simple syntax error.



Stephen Rasey

Houston

http://wiserways.com

http://excelsig.org



Question in Detail:



This is a cross tab query that works.

Call it J1T4qxt.

TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score

SELECT T2.A2IDSet, T2.OO, T2.DD

FROM (H80 INNER JOIN H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY T2.OO

PIVOT H80.IDTraceH;



Note the WHERE ... IN clause. I need the PK's in the cross tab query to
have high speed.



I need to sort the results based upon an Aggregate. This, too, runs
quickly.

A subquery for the sort is J1T5qnu

SELECT T2.A2IDSet, T2.OO, T2.DD, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC) AS
AvgOfCC, Avg(H71.TT) AS AvgOfTT

FROM (H80 INNER JOIN AS H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY Avg(H71.YY), Avg(H71.CC);



This WHERE has the same IN Clause as J1T4.



I join J1T4 and J1T5 for the final result. It is a fast query for what
it
does. About 3 sec.

J1T6:

SELECT T5.OO, T5.DD, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*

FROM J1T5qnu AS T5 INNER JOIN J1T4qxt AS T4 ON T5.OO = T4.OO

ORDER BY T5.AvgOfYY, T5.AvgOfCC;





The problem is that J1T5 and J1T6 both have an ' In (1410,1488,1520) '
clause that is necessary for speed of execution.




To make this useful, the parent application must change the IN clause
dynamically.



My options are:

1. use Parameter queries for J1T5 and J1T4. I've never tried
Parameters
for an entire IN clause so I don't know if that will work at all. I
guess
this is another question to post.

2. Create another table H81 that has only the IDTraceH values to use and
rewrite J1T5 and J1T6 to JOIN on H81.

3. Build J1T4 and J1T5 in QueryDef and Execute J1T6. I think option 2
is superior to this.

4. Create a compound SQL string programatically that will look something
like this:




SELECT T5.OO, T5.DD, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*

FROM
(SELECT T2.A2IDSet, T2.OO, T2.DD, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC) AS
AvgOfCC, Avg(H71.TT) AS AvgOfTT

FROM (H80 INNER JOIN AS H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY Avg(H71.YY), Avg(H71.CC))

AS T5

INNER JOIN

( TRANSFORM First([YY] & " @ " & Format([CC],"#")) AS Score

SELECT T2.A2IDSet, T2.OO, T2.DD

FROM (H80 INNER JOIN H71 ON H80.IDHash = H71.IDHash)

INNER JOIN T2 ON H71.OO = T2.OO

WHERE (((H80.IDTraceH) In (1410,1488,1520)))

GROUP BY T2.A2IDSet, T2.OO, T2.DD

ORDER BY T2.OO

PIVOT H80.IDTraceH)

AS T4 ON T5.OO = T4.OO

ORDER BY T5.AvgOfYY, T5.AvgOfCC;




When I try this, I get a " Syntax Error in FROM Clause" and TRANSFORM is
highlighted.



I have pretty well decided to go with Option 2. But I thought I would
ask:



Does anyone have a why of making Option 4 work?



Can anyone think of an option 5?



Thank you for your time.



Stephen Rasey

Houston

http://wiserways.com

http://excelsig.org
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks) 7
Syntax Error in From Clause 1
Expression too complex Error 8
Syntax error in FROM clause 3
Syntax Error in From Clause 2
Error in Reporting from Crosstab Query 3
Query syntax, error in FROM clause 4
MSACCESS 97 APPLICATION ERROR CONVERTING FROM TEXT TO MEMO 4

Top