Thread: PostgreSQL JDBC and sub-select
Hello, I work with JDeveloper and PostgreSQL JDBC and I have one problem. I get error : sub-SELECT in FORM must have an alias I can't change SQL command, but it is internal JDeveloper command Is it SQL standard (must have alias) or PostgreSQL specific ? Regards Haris Peco
snpe <snpe@snpe.co.yu> writes:
> I work with JDeveloper and PostgreSQL JDBC and I have one problem.
> I get error :
> sub-SELECT in FORM must have an alias
> Is it SQL standard (must have alias) or PostgreSQL specific ?
The SQL standard says you must write an alias. A FROM item is a <table
reference> (SQL92 section 7.4), which is defined (in SQL92 6.3) as
<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <joined table>
where
<derived table> ::= <table subquery>
The square brackets show that an alias (<correlation name>) is optional
for a plain table name, but is required for a sub-SELECT (<derived
table>).
This is not just a random whim on the part of the SQL spec writers.
One reason why they did it that way is to ensure that some specific
<correlation name> can be associated with every column produced by a
FROM clause. I used to remember some other interesting consequences,
but it's too late on a Saturday night to recall all the details...
> I can't change SQL command, but it is internal JDeveloper command
I have zero sympathy for "please change Postgres because my application
cannot be bothered to comply with the SQL specification".
regards, tom lane
On Sunday 10 November 2002 05:27 am, Tom Lane wrote: > snpe <snpe@snpe.co.yu> writes: > > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > > I get error : > > sub-SELECT in FORM must have an alias > > > > Is it SQL standard (must have alias) or PostgreSQL specific ? > > The SQL standard says you must write an alias. A FROM item is a <table > reference> (SQL92 section 7.4), which is defined (in SQL92 6.3) as > > <table reference> ::= > <table name> [ [ AS ] <correlation name> > [ <left paren> <derived column list> <right paren> ] ] > > | <derived table> [ AS ] <correlation name> > > [ <left paren> <derived column list> <right paren> ] > > | <joined table> > > where > > <derived table> ::= <table subquery> > > The square brackets show that an alias (<correlation name>) is optional > for a plain table name, but is required for a sub-SELECT (<derived > table>). > > This is not just a random whim on the part of the SQL spec writers. > One reason why they did it that way is to ensure that some specific > <correlation name> can be associated with every column produced by a > FROM clause. I used to remember some other interesting consequences, > but it's too late on a Saturday night to recall all the details... > > > I can't change SQL command, but it is internal JDeveloper command > > I have zero sympathy for "please change Postgres because my application > cannot be bothered to comply with the SQL specification". I didn't say that.I asked if that is PostgreSQL specific.Thanks for answer. regards Haris Peco
On Sunday 10 November 2002 08:51 am, Hannu Krosing wrote: > snpe kirjutas L, 09.11.2002 kell 22:51: > > Hello, > > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > > I get error : > > sub-SELECT in FORM must have an alias > > I can't change SQL command, but it is internal JDeveloper command > > You could set up query logging in the backend and see what the offending > query is. It may still be something you did (a missing or extra > something somewhere). > How ? regards Haris Peco
> > You could set up query logging in the backend and see what the offending > > query is. It may still be something you did (a missing or extra > > something somewhere). > > > How ? These settings have worked for me in a similar situation: (pulled from the admin list archives) <snip> My goal was to get all of the SQL statements from a JDBC front-end to be logged as they are executed in the postgres.log file (and not in the syslog.) Adding the following to my postgresql.conf did the job: syslog = 0 silent_mode = off debug_print_query = on debug_pretty_print = on I'm not sure if the pretty print option does anything for the SQL, but it didn't hurt. </snip> The results appear on /var/log/postgresql.log using the Debian Linux distribution. Not sure of the location in others. -Nick
On Sat, 9 Nov 2002, snpe wrote: > Hello, > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > I get error : > sub-SELECT in FORM must have an alias > I can't change SQL command, but it is internal JDeveloper command > > Is it SQL standard (must have alias) or PostgreSQL specific ? It looks to me to be standard. I think the appropriate portion of the grammar is: <table reference> := <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] <derived table> := <table subquery> <correlation name> := <identifier>
snpe kirjutas L, 09.11.2002 kell 22:51: > Hello, > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > I get error : > sub-SELECT in FORM must have an alias > I can't change SQL command, but it is internal JDeveloper command You could set up query logging in the backend and see what the offending query is. It may still be something you did (a missing or extra something somewhere). --------------- Hannu
On Sunday 10 November 2002 08:51 am, Hannu Krosing wrote: > snpe kirjutas L, 09.11.2002 kell 22:51: > > Hello, > > I work with JDeveloper and PostgreSQL JDBC and I have one problem. > > I get error : > > sub-SELECT in FORM must have an alias > > I can't change SQL command, but it is internal JDeveloper command > > You could set up query logging in the backend and see what the offending > query is. It may still be something you did (a missing or extra > something somewhere). > query is like this : select * from (select * from tab) where 1=2 This is work with Oracle,DB2,SQL Server Postgresql request alias in sub-select in from clause regards Haris Peco