Is there a reason why I am allowed the schema qualifier when specifying which tables to select from or join but not as part of the create table phrase eg

CREATE TABLE "Memory".XX12 AS......

If it is possible it would make it far easier for me to convert several auto-generated bits of sql where I can easily specify the schema for the table being created but not the source tables.

Roy Lambert

Roy,

<< Is there a reason why I am allowed the schema qualifier when specifying
which tables to select from or join but not as part of the create table
phrase eg

CREATE TABLE "Memory".XX12 AS...... >>

1) It's not a schema qualifier that you're using - it's a database
qualifier.
2) Because tables can only be created in the current database in the Default
schema.  Therefore, allowing other qualifiers would simply result in an
error later in the process.

<< If it is possible it would make it far easier for me to convert several
auto-generated bits of sql where I can easily specify the schema for the
table being created but not the source tables. >>

If you're thinking that specifying the database qualifier will result in EDB
automatically knowing the database to use for the AS source tables, then
you'd better rethink your design.   The SQL just simply doesn't work that
way - the only "assumed" or implicit database/catalog is the current
database, and the AS SELECT statement is completely independent of the
CREATE TABLE statement for the most part.


--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>1) It's not a schema qualifier that you're using - it's a database
>qualifier.

I'll get the jargon right some time

>2) Because tables can only be created in the current database in the Default
>schema. Therefore, allowing other qualifiers would simply result in an
>error later in the process.
>
><< If it is possible it would make it far easier for me to convert several
>auto-generated bits of sql where I can easily specify the schema for the
>table being created but not the source tables. >>
>
>If you're thinking that specifying the database qualifier will result in EDB
>automatically knowing the database to use for the AS source tables, then
>you'd better rethink your design. The SQL just simply doesn't work that
>way - the only "assumed" or implicit database/catalog is the current
>database, and the AS SELECT statement is completely independent of the
>CREATE TABLE statement for the most part.

Not quite what I was thinking. I have to specify a database for the query component which is then the current database (I think) I was just hoping that I could specify the database for the table being created and use the current database for the remainder. It would have made it a lot easier.

Roy Lambert

Roy,

<< Not quite what I was thinking. I have to specify a database for the query
component which is then the current database (I think) I was just hoping
that I could specify the database for the table being created and use the
current database for the remainder. It would have made it a lot easier. >>

Unfortunately, no.  That (obviously) relies on the one feature that it won't
do - referencing another database from within a different database's DDL
statement.  Not many database engines that I'm aware of allow for this type
of functionality.  Schema specifiers are one thing, but entirely different
databases are another.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Shame, but its what I thought. Redesign of my query generator needed now - rats.

Roy Lambert