Error "Each GROUP BY expression must contain at least one column that is not an outer reference" for valid SQL (4307754)

Chatta subito con l'assistenza

Restituisci

Feedback inviato

Questo articolo ti è servito per risolvere il problema?

Seleziona valutazione

Titolo

Error "Each GROUP BY expression must contain at least one column that is not an outer reference" for valid SQL
Descrizione

When optimizing, the following valid query prompts for bind variables when it shouldn't.
select Test_Bench.BenchName,
Test_Attributes.Name
FROM Test_Bench LEFT
JOIN Test_Session
ON PK_Test_Bench = FK_Test_Bench
LEFT JOIN Test_Suite
...
where Test_Bench.PK_Test_Bench = '10'
group by Test_Bench.BenchName, Test_Attributes.Name
ORDER BY Test_Attributes.Name
After clicking OK, the following error occurs:
"Each GROUP BY expression must contain at least one column that is not an outer reference"
Causa

Optimizer formats the query so that the LEFT is placed on line above i.e. FROM Test_Bench LEFT
so that the LEFT is now being interpreted as a table alias, hence the prompt for variables. The two columns now being interpreted as bind variables cause the GROUP BY to fail.
Risoluzione

WORKAROUND
Change the statement to include LEFT OUTER instead of LEFT i.e.
SELECT Test_Bench.BenchName,
Test_Attributes.Name
FROM Test_Bench
LEFT OUTER JOIN Test_Session
.
.
.
STATUS
Waiting for fix in a future release of release of SQL Optimizer for SQL Server.
ID difetto

QSOSS-568

Feedback inviato

Questo articolo ti è servito per risolvere il problema?

Seleziona valutazione

Contenuti consigliati

Prodotto/i:: SQL Optimizer for SQL Server
10.1.2, 10.1.1, 10.1, 10.0.3, 10.0.1

Cronologia articoli:: Data di creazione: 8/12/2016
Ultimo aggiornamento: 5/7/2023

Titolo