Re: Please explain why this Select doesn't fail

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 10:02:16 +0100

On Mon, 13 Dec 2004 19:39:35 -0500, Andrew J. Kelly wrote:

>> Or do you mean that references to tables in the outer query should always
>> be qualified with table name or alias or else throw an error?
>
>Pretty much yes. Actually I would prefer that if the column in the subquery
>does not exist in the table directly referenced in the from clause of the
>sub-query it should throw an error.

Hi Andrew,

Yeah, I think that this requirement would prevent lots of foolish
mistakes. Though I also have to agree with Steve's point, that this would
break too much existing code (not mine though - I always prefix ALL
columns as soon as more than one table is used in a query)

On the other hand - this change would fix one potential source of errors,
while leaving others open. Maybe we'll just have to accept the simple fact
that SQL is NOT a simple language that one can master after a one-week
crash course. The same power and flexibility that allows one to work true
miracles with SQL also allows one to goof up big time.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... >The subquery should return the records: ...
    (microsoft.public.access.queries)
  • Re: Multiple AND criteria
    ... A subquery is like a whole SELECT query inside another query. ... Then switch it to SQL view to ... FROM tblPart INNER JOIN tblPartAttrib ...
    (microsoft.public.access.queries)
  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... Perahps we've begin to uncover some bugs in the Access query ... If you PASTE SQL test in the following format into the SQL window ... around a field or table name within the VirtTbl1 subquery, ... I would think that producing a correct error message for that sort of query ...
    (microsoft.public.access.queries)
  • Re: Ranking - dealing with ties
    ... I didn't think until too late just to post the SQL - sorry. ... qryDivScore has fields Score and Div. ... Alias Scor1) ... Alias has to be used instead of the actual query name, ...
    (microsoft.public.access.formscoding)
  • Re: The complement of a select query
    ... That is not the SQL I suggested. ... the subquery. ... FROM Zip01 as TableA LEFT JOIN as ... Your query that does work will always be slow. ...
    (microsoft.public.access.queries)