Re: "Order by" clause

While there is no actual ambiguity, the parser is not so sure.
Your select list contains the STATE column twice: once
explicitly as the first column and once again in the *. The
parser does not know whether you want to sort on the
source column sop30200.STATE or on the output column
STATE. They are the same in this query, but there are
queries where they could be different because some
output expression or column is aliased as STATE. The
parser does not try to figure out the meaning of the query.

There are two solutions. One is to specify

ORDER BY sop30200.STATE

and the other is to list all the columns you want in the output
separately and one each, not using the * notation. The second
solution is the better one, at least in production, because neither
* nor duplicate columns in the output are typically a good idea.
But if this is just a quick ad hoc query, the first solution is fine.

-- Steve Kass
-- Drew University
-- http://www.stevekass.com


chas2006 wrote:

I run the script below in sql2005 and am receiving the error msg 209. Do I need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want them displyed in front.

Thank you.

Charlie

select STATE, TAXAMNT, SUBTOTAL, * from sop30200 where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','TN')
AND SOPTYPE IN (3,4)
AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
--AND TAXEXMT1 = ' '
AND SUBTOTAL <> 0
--AND SOPNUMBE LIKE 'NCINV%'
AND VOIDSTTS = 0
AND SOPNUMBE NOT IN
(SELECT INVNO FROM VERTEX..REGPRERETURNSTBL
WHERE COMPCD = 'ABS'
and invno between '20070201' and '20070228')
--AND SHIPTOGEOCODE LIKE '31%')
ORDER BY STATE DESC


Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'STATE'.


.

Relevant Pages

  • Re: Parsing ambiguities
    ... elegant way to generate parsers that can deal with ambiguity. ... Error Correcting Parser Combinators: ... If yours is "the same" as Parsec (no way ... prepositional phrases just plain aren't in the grammar ...
    (comp.lang.misc)
  • Re: Parsing fully context-free grammars
    ... This is one of the problems with GLR parsers. ... then you get a parser that is ambiguous also. ... This problem can be resolved at the grammar level and avoid the ... The problem is the ambiguity in the grammar, ...
    (comp.compilers)
  • Re: Parsing ambiguities
    ... I found a paper on "limited repair" of parse trees, ... obvious way to deal with ambiguity is to generate all the (combinatorial ... A standard technique for this is to parse using a context-free parser, ... An efficient method for doing this is GLR parsing, ...
    (comp.lang.misc)
  • Re: question about SQL Parser
    ... query I send it. ... The parser currently won't break on foo.bar syntax but can't handle foo.bar.baz, that wouldn't be too hard to add. ... For Data Definition Language (DDL), you should check out SQL::Translator, it is much more complete for DDL. ... At the moment your best option may be wrapping the parsing in an eval and creating two output files - one of successfully parsed statements and one of statements that couldn't be parsed. ...
    (perl.dbi.users)
  • Re: Look ups with multiple parameters
    ... Yes, the query is defined with two parameters, that's why if i send one ... However, if i send two parameters, the parser finds an error as i said. ... Please check the definition of the lookup again. ... You may also consider using explicit casting of data types to e.g. a ...
    (microsoft.public.sqlserver.dts)