Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)

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
 
L

Lynn Trapp

So what is "B"? You use it in your select statement AND in your join
statement, but it is never identified anywhere in a from clause. Is "B" the
name of the table or is it intended to be an alias to some other table name?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


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

the table B reference is in
INNER JOIN B On....

That first query was a simple one for illistration to highlight the FROM
(TRANSFORM..
.. Next time, I'll use tblA, tblB.

Toward the bottom I have the (disguised) real query
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;

Stephen Rasey
Houston

Lynn Trapp said:
So what is "B"? You use it in your select statement AND in your join
statement, but it is never identified anywhere in a from clause. Is "B" the
name of the table or is it intended to be an alias to some other table name?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


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

Pike

Check out the RAC help file if your considering
any version of sql server 2000.
You can do just about anything with the RAC
@select statement.All RAC parameters can easily be
parametized with @userN.This includes @from and
@where.
You could solve your problem within one RAC execute.

RAC v2.2 and QALite @
www.rac4sql.net
 
J

John Spencer (MVP)

One thing I do know is that in Access the subquery cannot have square braces in
it. [YY] and [CC] would need to be referenced as YY and CC. I haven't the time
to experiment right now, but other than that I think your query should work
barring any syntax errors.

Stephen 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

I tried a slightly simplier query. (I do not wish to post the real thing)
When I use a [] around the subquery (as I saw that the query designer did to
the first subquery)
I get the message 'TRANSFORM First(... ...GROUP BY A1SetDe' is not a valid
name. Make sure that it does not include invalid caharacters or
punctuation and that it is not too long.

The "A1SetDe" is a truncation. Either the query is too long, or the it
is just the error message that could not hold the entire subquery. I think
the latter. See below.

I saw on a successful subquery example, the Designer uses square bracets
around the subquery and ends it with a period. Like
ORDER BY Avg(H71.YearNo), Avg(H71.CumCapexYear)]. AS T5 INNER JOIN
note the "." before the AS T5.

When I have [ at the begining of the sub query and ]. (square-bracket
period), my test query executes for a second then
errors with
"Cannot use the crosstab of a non-fixed column as a subquery"

And that pretty well settles the matter. At least for Jet.

Stephen Rasey

John Spencer (MVP) said:
One thing I do know is that in Access the subquery cannot have square braces in
it. [YY] and [CC] would need to be referenced as YY and CC. I haven't the time
to experiment right now, but other than that I think your query should work
barring any syntax errors.

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

John Spencer (MVP)

Yes, I can see that you would have to use an IN clause in the Crosstab query and
specify the column names and since you cannot do that according to you earlier
messages then I think you are stuck unless you use some VBA to get the column
names from the just the crosstab and then build your overall query with vba.

:-(
 
S

Stephen Rasey

No, I'm not stuck. I'm done.

Option 2: create another table to hold the items that were in the IN clause
was the way to go.
It is table H82 in the following disguised query tree.

With 5 elements in the H82 table (returning 5 columns in the query as part
of the T4.* in T1J6), the query executes in 1 second, 1000 records returned,
which I think is splended. Add 0.25 seconds for a dao delete and write for
up to 12 elements in H82, and we have a very acceptable application
response.

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

SELECT T2.A2IDSet, T2.OO, T2.PP

FROM ((H80 AS H80 INNER JOIN PP ON H80.IDH=H71.IDH) INNER JOIN J1T2qmax AS
T2 ON H71.OO=T2.OO) INNER JOIN H82 ON H80.IDTraceH=H82.IDTraceH

GROUP BY T2.A2IDSet, T2.OO, T2.PP

ORDER BY T2.OO

PIVOT H80.IDTraceH;


T1J5qxt:
SELECT T2.A2IDSet, T2.OO, T2.PP, Avg(H71.YY) AS AvgOfYY, Avg(H71.CC) AS
AvgOfCC, Avg(H71.CCT) AS AvgOfCCT, Min(H71.CC) AS MinOfCC, Min(H71.CCT) AS
MinOfCCT

FROM H82 INNER JOIN ((H80 INNER JOIN H71 ON H80.IDH = H71.IDH) INNER JOIN T2
ON H71.OO = T2.OO) ON H82.IDTraceH = H80.IDTraceH

GROUP BY T2.A2IDSet, T2.OO, T2.PP

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



T1J6qnuAggSortedCrossTab:
SELECT T5.OO, T5.PP, T5.AvgOfYY, T5.AvgOfCC, T5.AvgOfCumCapexTrace, T4.*

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

ORDER BY T5.AvgOfYY, T5.AvgOfCC;


Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org
 

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.... Syntax error 2

Top