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

Sat, 9 Nov 2002 17:51:41 +0000
snpe <snpe@snpe.co.yu>
PostgreSQL JDBC and sub-select
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>



Sat, 9 Nov 2002 11:07:17 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: PostgreSQL JDBC and sub-select
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

Sun, 10 Nov 2002 00:27:53 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] PostgreSQL JDBC and sub-select 
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



10 Nov 2002 13:51:08 +0500
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] PostgreSQL JDBC and sub-select

> > 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


Sun, 10 Nov 2002 09:37:44 -0500
"Nick Fankhauser" <nickf@ontko.com>
Re: [JDBC] [HACKERS] PostgreSQL JDBC and sub-select
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

Sun, 10 Nov 2002 14:51:56 +0000
snpe <snpe@snpe.co.yu>
Re: [HACKERS] PostgreSQL JDBC and sub-select
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

Sun, 10 Nov 2002 14:52:58 +0000
snpe <snpe@snpe.co.yu>
Re: [HACKERS] PostgreSQL JDBC and sub-select
Hello,
	Query :

        select id i 
        from tab

don't work in PostgreSQL

	I know that 

	select id as i
        from tab

work, but I think that AS in 'as cluse' is optional

Please comment.

regards
Haris Peco

Sun, 10 Nov 2002 15:41:54 +0000
snpe <snpe@snpe.co.yu>
SQL syntax (column alias)
On Sun, 2002-11-10 at 10:41, snpe wrote:
> Hello,
> 	Query :
> 
>         select id i 
>         from tab
> 
> don't work in PostgreSQL
> 
> 	I know that 
> 
> 	select id as i
>         from tab
> 
> work, but I think that AS in 'as cluse' is optional

Thats true.  Section 7.11 has it as optional

<derived column> ::=
	 <value expression> [ <as clause> ]

 <as clause> ::= [ AS ] <column name>


But this isn't going to be very nice to fix.

conflicts:  1378 shift/reduce, 44 reduce/reduce

-- 
  Rod Taylor


10 Nov 2002 11:15:59 -0500
Rod Taylor <rbt@rbt.ca>
Re: SQL syntax (column alias)
snpe wrote:
> work, but I think that AS in 'as cluse' is optional
> 
> Please comment.

See:
   http://developer.postgresql.org/docs/postgres/sql-select.html


Near the bottom:

" SQL92

SELECT Clause

In the SQL92 standard, the optional keyword AS is just noise and can be 
omitted without affecting the meaning. The PostgreSQL parser requires this 
keyword when renaming output columns because the type extensibility features 
lead to parsing ambiguities in this context. AS is optional in FROM items, 
however."

Joe


Sun, 10 Nov 2002 08:38:37 -0800
Joe Conway <mail@joeconway.com>
Re: SQL syntax (column alias)
Joe Conway <mail@joeconway.com> quotes:
> In the SQL92 standard, the optional keyword AS is just noise and can be 
> omitted without affecting the meaning. The PostgreSQL parser requires this 
> keyword when renaming output columns because the type extensibility features 
> lead to parsing ambiguities in this context. AS is optional in FROM items, 
> however.

Actually, I think it's not so much datatype extensibility as operator
extensibility, and specifically the fact that we allow postfix
operators.  If AS were optional, then

		SELECT 1 + x FROM foo;

could be parsed either as "(1 + x)" (infix +, x presumably a column name)
or as "(1 +) x" (postfix +, x an AS-name).

So allowing AS to be optional would at minimum require taking out
postfix operators.  There might be other features we'd have to lose,
too; I haven't tried messing with the grammar to see what would happen.

			regards, tom lane

Sun, 10 Nov 2002 12:44:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SQL syntax (column alias) 
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

Thu, 14 Nov 2002 13:31:11 +0000
snpe <snpe@snpe.co.yu>
Re: [JDBC] [HACKERS] PostgreSQL JDBC and sub-select
> 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

The SQL standard requires that you alias it.  Postgres follows the standard
on this:

select * from (select * from tab) as sub where 1=2;

Chris


Fri, 15 Nov 2002 11:13:58 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [JDBC] PostgreSQL JDBC and sub-select