Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!

Richard Lynch wrote:

FWIW:

I seldom do much more than add the tables in MS Access's <OPINION> stupid
</OPINION> query design thingy. I immediately use the menu to view SQL and
just type the SQL I want. Obviously, different folks think different ways,
and maybe you actually understand and like that MS Access point and click
interface that I hate, but I want to be sure you're at least aware that if
it sticks an ORDER BY in there that you never asked for, you can just rip
the damn thing out in the SQL view and then run the query... At least,
that's how I make it give me the SQL I really want, instead of the SQL it
thinks I want, which it never gets right anyway.

Hope that helps.

Thanks, I would probably agree with you, but it makes no difference if I
like it or not, its our customers who want the graphical interface and
FINALLY, we have a fix for it! They are most likely not the types who
are gonna drop down to the pound sign and type some SQL into the
monitor!

And BTW, if you look at the SQL, you wouldn't even see the ORDER BY
thing because this clause is placed on the "keyset" query, which is the
collection of keys it uses to dynamically access rows in the query
result (i.e. the Dynaset).

Actually, I understand your hate of the Microsoft thing, but I only
recently realized the benefit and power of this Jet database engine
thing that Access uses. For example, it can update the results of a
query that has several tables joined, right while your looking at the
row on the screen, automatically translating the SQL2 syntax (i.e.,
INNER JOIN ... ON ...) into standard SQL that Postgres can use.

Byron

FWIW:

I seldom do much more than add the tables in MS Access's <OPINION> stupid
</OPINION> query design thingy. I immediately use the menu to view SQL and
just type the SQL I want. Obviously, different folks think different ways,
and maybe you actually understand and like that MS Access point and click
interface that I hate, but I want to be sure you're at least aware that if
it sticks an ORDER BY in there that you never asked for, you can just rip
the damn thing out in the SQL view and then run the query... At least,
that's how I make it give me the SQL I really want, instead of the SQL it
thinks I want, which it never gets right anyway.

Since I'm learning SQL in my spare time, I tend to use these feature in MS
Access and PgAccess to point me in the right direction or sometimes
confirm, or deny my assertions.

I like psql, but its not the kind of tool which suggests other
alternatives. It just says "this part is bogus." That's fine, but when I
fail to get it right after a dozen attempts, its nice to let something
else take a stab at it.

Bruce Tong | Got me an office; I'm there late at night.
Systems Programmer | Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE |
zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century

Since I'm learning SQL in my spare time, I tend to use these feature in MS
Access and PgAccess to point me in the right direction or sometimes
confirm, or deny my assertions.

I like psql, but its not the kind of tool which suggests other
alternatives. It just says "this part is bogus." That's fine, but when I
fail to get it right after a dozen attempts, its nice to let something
else take a stab at it.

Please tell use what else psql can tell the user. We have \h and \d
commands.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Since I'm learning SQL in my spare time, I tend to use these feature in MS
Access and PgAccess to point me in the right direction or sometimes
confirm, or deny my assertions.

I like psql, but its not the kind of tool which suggests other
alternatives. It just says "this part is bogus." That's fine, but when I
fail to get it right after a dozen attempts, its nice to let something
else take a stab at it.

Please tell use what else psql can tell the user. We have \h and \d
commands.

Example:

zztong=> SELECT * FROM school SORT DESC BY school_lname;
ERROR: parser: parse error at or near "desc"

Follow this with 10 more seemingly reasonable attempts at guessing the
correct syntax which all fail. Then I'm off to the books, or maybe I just
run PgAccess and quickly design a view soley to see the SQL.

The problem is not with psql, its with me.

I mean if you want to have psql attempt to suggest the correct syntax
based on my mistakes, I won't complain, but I really don't think it is
needed and I'm certain I would become annoyed with the feature as I became
more experienced.

I have little need for generating SQL "on the fly" as I'm just putting it
into a textfile to be run through psql from a makefile, or I'm burrying it
in a program somewhere. Thus I spend little time with SQL, so I'm learning
it slowly. I don't try to memorize things as I prefer to pick them up via
usage and I try to keep lots of books around.

Bruce Tong | Got me an office; I'm there late at night.
Systems Programmer | Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE |
zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century

Since I'm learning SQL in my spare time, I tend to use these feature in MS
Access and PgAccess to point me in the right direction or sometimes
confirm, or deny my assertions.

I like psql, but its not the kind of tool which suggests other
alternatives. It just says "this part is bogus." That's fine, but when I
fail to get it right after a dozen attempts, its nice to let something
else take a stab at it.

Please tell use what else psql can tell the user. We have \h and \d
commands.

Example:

zztong=> SELECT * FROM school SORT DESC BY school_lname;
ERROR: parser: parse error at or near "desc"

Follow this with 10 more seemingly reasonable attempts at guessing the
correct syntax which all fail. Then I'm off to the books, or maybe I just
run PgAccess and quickly design a view soley to see the SQL.

Hmm Did you try...

cary=> \h select
Command: select
Description: retrieve tuples
Syntax:
select [distinct on <attr>] <expr1> [as <attr1>], ... <exprN> [as <attrN>]
[into [table] <class_name>]
[from <from_list>]
[where <qual>]
[group by <group_list>]
[order by <attr1> [ASC | DESC] [using <op1>], ... <attrN> ]
[union [all] select ...];

That's pretty good help if you ask me, and shows that there is
no BY keyword in the order by clause.

Psql is pretty good, especially if you have the readline support or
run it from emacs. With the new query cancel it will be even better.

[snip]

-- cary

Hmm Did you try...

cary=> \h select
Command: select
Description: retrieve tuples
Syntax:
select [distinct on <attr>] <expr1> [as <attr1>], ... <exprN> [as <attrN>]
[into [table] <class_name>]
[from <from_list>]
[where <qual>]
[group by <group_list>]
[order by <attr1> [ASC | DESC] [using <op1>], ... <attrN> ]
[union [all] select ...];

I am working on uppercasing the keywords, so it will be much clearer.
The use of <> is really bogus. I will look into reporting the place of
the error, as well as the word.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)