Hi,

When I do this query :

SELECT O_CODE, 'P'+SUBSTRING(O_CODE FROM 1 FOR 2) AS Coef
FROM Ouvrages
WHERE (Coef IN (SELECT * FROM Vue1))

I have this error message :

ElevateDB Error #700 An error was found in the statement at line 3 and column 8 (ElevateDB Error #401 The column Coef does not exist in the table Ouvrages)

With previous versions of ElevateDB, it seemed that it worked

What I am doing wrong ?

Thanks for your help

Bruno

Josselin


I can confirm it doesn't work in 2.05b11 but I don't have a memory of it working at any point (it would simplify a number of my sqls if it did). AFAIK you have to repeat the 'P'+SUBSTRING(O_CODE FROM 1 FOR 2)


Do you know which version of ElevateDB it worked with?

Roy Lambert

I don't believe you can use a correlation names, (e.g. "AS Coef") in the
WHERE clause.  I couldn't find anything specific about it in the SQL manual,
but whenever I need to do that, I have to copy the entire "definition" of
the correlation name.

So yours would be something like this:

SELECT O_CODE, 'P'+SUBSTRING(O_CODE FROM 1 FOR 2) AS Coef
FROM Ouvrages
WHERE ('P'+SUBSTRING(O_CODE FROM 1 FOR 2) IN (SELECT * FROM Vue1))

There might be a more efficient way to structure you query, but I don't know
your dataset.


David Cornelius
Cornelius Concepts

Bruno,

<< I have this error message : >>

David is correct - you cannot use the Coef correlation name in the WHERE
clause, rather you need to repeat the expression used in the SELECT list for
the WHERE clause condition.

--
Tim Young
Elevate Software
www.elevatesoft.com