creating an alias for a tablename

I'm new to the ng, don't mean to step on anyone's toes, but I've
sesrched the archives with no success on this one.

I'd like to create aliases for tablenames. I don't mean a "table
alias", which is really a "row alias" (thanks to Bill Karwin for that
datum); that won't help me because I would still have to enter the
tablename at least once per query, per row. I mean an alias for a
tablename that can be used in queries, and stored either in the schema
somewhere or maybe applied once on a per-session basis.

I have *inherited* a db with some really stupidly long tablenames, so
writing queries is a hassle. Any typo in a tablename breaks the whole
query, of course. Here's an example of what I mean: Let's say I have
a table called tbl_data_reference_stock_22 ... here's a typical query:

SELECT tbl_data_reference_stock_22.uid,
tbl_data_reference_stock_22.store_id FROM tbl_data_reference_stock_22
WHERE tbl_data_reference_stock_22.session_id = '1'

I'd like to alias the tablename to "drs22". So the query would
become:

SELECT drs22.uid, drs22.store_id FROM drs22 WHERE drs22.session_id =
'1'

Is there a way to do this?

thanks in advance

--
FW

mySQL version 5.0

FastWolf wrote:
> I'm new to the ng, don't mean to step on anyone's toes, but I've
> sesrched the archives with no success on this one.
>
> I'd like to create aliases for tablenames. I don't mean a "table
> alias", which is really a "row alias" (thanks to Bill Karwin for that
> datum); that won't help me because I would still have to enter the
> tablename at least once per query, per row. I mean an alias for a
> tablename that can be used in queries, and stored either in the schema
> somewhere or maybe applied once on a per-session basis.
>
> I have *inherited* a db with some really stupidly long tablenames, so
> writing queries is a hassle. Any typo in a tablename breaks the whole
> query, of course. Here's an example of what I mean: Let's say I have
> a table called tbl_data_reference_stock_22 ... here's a typical query:
>
> SELECT tbl_data_reference_stock_22.uid,
> tbl_data_reference_stock_22.store_id FROM tbl_data_reference_stock_22
> WHERE tbl_data_reference_stock_22.session_id = '1'
>
> I'd like to alias the tablename to "drs22". So the query would
> become:
>
> SELECT drs22.uid, drs22.store_id FROM drs22 WHERE drs22.session_id =
> '1'
>
> Is there a way to do this?
>
> thanks in advance
>
> --
> FW
>
> mySQL version 5.0


There was a reference in the MYSQL forums that indicated back in 2004
that they were considering something like Oracle synonyms but I was
unable to find anything in the 5.0 or 5.1 docs that would indicate they
actually did the work...

Barring that...

create view shrtnm as select * from verylongname;

You would need to test performance to ensure it still performs
satisfactorily.

On Feb 27, 6:26 pm, Michael Austin <maus...@firstdbasource.com> wrote:
> FastWolf wrote:
> > I'm new to the ng, don't mean to step on anyone's toes, but I've
> > sesrched the archives with no success on this one.
>
> > I'd like to create aliases for tablenames. I don't mean a "table
> > alias", which is really a "row alias" (thanks to Bill Karwin for that
> > datum); that won't help me because I would still have to enter the
> > tablename at least once per query, per row. I mean an alias for a
> > tablename that can be used in queries, and stored either in the schema
> > somewhere or maybe applied once on a per-session basis.
>
> > I have *inherited* a db with some really stupidly long tablenames, so
> > writing queries is a hassle. Any typo in a tablename breaks the whole
> > query, of course. Here's an example of what I mean: Let's say I have
> > a table called tbl_data_reference_stock_22 ... here's a typical query:
>
> > SELECT tbl_data_reference_stock_22.uid,
> > tbl_data_reference_stock_22.store_id FROM tbl_data_reference_stock_22
> > WHERE tbl_data_reference_stock_22.session_id = '1'
>
> > I'd like to alias the tablename to "drs22". So the query would
> > become:
>
> > SELECT drs22.uid, drs22.store_id FROM drs22 WHERE drs22.session_id =
> > '1'
>
> > Is there a way to do this?
>
> > thanks in advance
>
> > --
> > FW
>
> > mySQL version 5.0
>
> There was a reference in the MYSQL forums that indicated back in 2004
> that they were considering something like Oracle synonyms but I was
> unable to find anything in the 5.0 or 5.1 docs that would indicate they
> actually did the work...
>
> Barring that...
>
> create view shrtnm as select * from verylongname;
>
> You would need to test performance to ensure it still performs
> satisfactorily.

Thanks for the tip, Michael. For my purposes I think it will work
fine.

--
FW

On Thu, 28 Feb 2008 02:26:36 GMT, Michael Austin
<maustin@firstdbasource.com> wrote:

>FastWolf wrote:
>> I'm new to the ng, don't mean to step on anyone's toes, but I've
>> sesrched the archives with no success on this one.
>>
>> I'd like to create aliases for tablenames. I don't mean a "table
>> alias", which is really a "row alias" (thanks to Bill Karwin for that
>> datum); that won't help me because I would still have to enter the
>> tablename at least once per query, per row. I mean an alias for a
>> tablename that can be used in queries, and stored either in the schema
>> somewhere or maybe applied once on a per-session basis.
>>
>> I have *inherited* a db with some really stupidly long tablenames, so
>> writing queries is a hassle. Any typo in a tablename breaks the whole
>> query, of course. Here's an example of what I mean: Let's say I have
>> a table called tbl_data_reference_stock_22 ... here's a typical query:
>>
>> SELECT tbl_data_reference_stock_22.uid,
>> tbl_data_reference_stock_22.store_id FROM tbl_data_reference_stock_22
>> WHERE tbl_data_reference_stock_22.session_id = '1'
>>
>> I'd like to alias the tablename to "drs22". So the query would
>> become:
>>
>> SELECT drs22.uid, drs22.store_id FROM drs22 WHERE drs22.session_id =
>> '1'
>>
>> Is there a way to do this?
>>
>> thanks in advance
>>
>> --
>> FW
>>
>> mySQL version 5.0
>
>
>There was a reference in the MYSQL forums that indicated back in 2004
>that they were considering something like Oracle synonyms but I was
>unable to find anything in the 5.0 or 5.1 docs that would indicate they
>actually did the work...
>
>Barring that...
>
>create view shrtnm as select * from verylongname;
>
>You would need to test performance to ensure it still performs
>satisfactorily.

Michael,

I just wanted to thank you again for your input. Your suggestion
works fine, but only for SELECT statements, of course. If there were
a way to do the same thing with UPDATE, INSERT, and DELETE I'd be
ecstatic. But there isn't, as far as I can tell. Bummer.

I can't be the only person who thinks this way. How do we tell them
we want this functionality? Not that they'd listen, but it couldn't
hurt to try.

thanks

--
FW

MySQL 5.0

FastWolf wrote:
> I'm new to the ng, don't mean to step on anyone's toes, but I've
> sesrched the archives with no success on this one.
>
> I'd like to create aliases for tablenames. I don't mean a "table
> alias", which is really a "row alias" (thanks to Bill Karwin for that
> datum); that won't help me because I would still have to enter the
> tablename at least once per query, per row. I mean an alias for a
> tablename that can be used in queries, and stored either in the schema
> somewhere or maybe applied once on a per-session basis.
>
> I have *inherited* a db with some really stupidly long tablenames, so
> writing queries is a hassle. Any typo in a tablename breaks the whole
> query, of course. Here's an example of what I mean: Let's say I have
> a table called tbl_data_reference_stock_22 ... here's a typical query:
>
> SELECT tbl_data_reference_stock_22.uid,
> tbl_data_reference_stock_22.store_id FROM tbl_data_reference_stock_22
> WHERE tbl_data_reference_stock_22.session_id = '1'
>
> I'd like to alias the tablename to "drs22". So the query would
> become:
>
> SELECT drs22.uid, drs22.store_id FROM drs22 WHERE drs22.session_id =
> '1'
>
> Is there a way to do this?
>
> thanks in advance
>
> --
> FW
>
> mySQL version 5.0
>

SELECT drs22.uid, drs22.store_id FROM tbl_data_reference_stock_22 AS
drs22 WHERE drs22.session_id = '1'

Or, if you have one one table (or multiple tables with no column name
conflicts:

SELECT uid, store_id FROM tbl_data_reference_stock_22 WHERE session_id = '1'

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

FastWolf wrote:
> On Thu, 28 Feb 2008 02:26:36 GMT, Michael Austin
> <maustin@firstdbasource.com> wrote:
>
>> FastWolf wrote:
>>> I'm new to the ng, don't mean to step on anyone's toes, but I've
>>> sesrched the archives with no success on this one.
>>>
>>> I'd like to create aliases for tablenames. I don't mean a "table
>>> alias", which is really a "row alias" (thanks to Bill Karwin for that
>>> datum); that won't help me because I would still have to enter the
>>> tablename at least once per query, per row. I mean an alias for a
>>> tablename that can be used in queries, and stored either in the schema
>>> somewhere or maybe applied once on a per-session basis.
>>>
>>> I have *inherited* a db with some really stupidly long tablenames, so
>>> writing queries is a hassle. Any typo in a tablename breaks the whole
>>> query, of course. Here's an example of what I mean: Let's say I have
>>> a table called tbl_data_reference_stock_22 ... here's a typical query:
>>>
>>> SELECT tbl_data_reference_stock_22.uid,
>>> tbl_data_reference_stock_22.store_id FROM tbl_data_reference_stock_22
>>> WHERE tbl_data_reference_stock_22.session_id = '1'
>>>
>>> I'd like to alias the tablename to "drs22". So the query would
>>> become:
>>>
>>> SELECT drs22.uid, drs22.store_id FROM drs22 WHERE drs22.session_id =
>>> '1'
>>>
>>> Is there a way to do this?
>>>
>>> thanks in advance
>>>
>>> --
>>> FW
>>>
>>> mySQL version 5.0
>>
>> There was a reference in the MYSQL forums that indicated back in 2004
>> that they were considering something like Oracle synonyms but I was
>> unable to find anything in the 5.0 or 5.1 docs that would indicate they
>> actually did the work...
>>
>> Barring that...
>>
>> create view shrtnm as select * from verylongname;
>>
>> You would need to test performance to ensure it still performs
>> satisfactorily.
>
> Michael,
>
> I just wanted to thank you again for your input. Your suggestion
> works fine, but only for SELECT statements, of course. If there were
> a way to do the same thing with UPDATE, INSERT, and DELETE I'd be
> ecstatic. But there isn't, as far as I can tell. Bummer.
>
> I can't be the only person who thinks this way. How do we tell them
> we want this functionality? Not that they'd listen, but it couldn't
> hurt to try.
>
> thanks
>


I suppose if you wanted a more permanent solution that would require
recoding your app...:

http://dev.mysql.com/doc/refman/5.0/...ame-table.html

I do not see CREATE SYNONYM in the 6.0 docs either.. you could go to the
MYSQL site and add an enhancement request... (Axel???)

On 4 Mar, 19:44, FastWolf <wolfsof...@NOSPAMcomcast.net> wrote:
> On Thu, 28 Feb 2008 02:26:36 GMT, Michael Austin
>
>
>
> <maus...@firstdbasource.com> wrote:
> >FastWolf wrote:
> >> I'm new to the ng, don't mean to step on anyone's toes, but I've
> >> sesrched the archives with no success on this one.
>
> >> I'd like to create aliases for tablenames. I don't mean a "table
> >> alias", which is really a "row alias" (thanks to Bill Karwin for that
> >> datum); that won't help me because I would still have to enter the
> >> tablename at least once per query, per row. I mean an alias for a
> >> tablename that can be used in queries, and stored either in the schema
> >> somewhere or maybe applied once on a per-session basis.
>
> >> I have *inherited* a db with some really stupidly long tablenames, so
> >> writing queries is a hassle. Any typo in a tablename breaks the whole
> >> query, of course. Here's an example of what I mean: Let's say I have
> >> a table called tbl_data_reference_stock_22 ... here's a typical query:
>
> >> SELECT tbl_data_reference_stock_22.uid,
> >> tbl_data_reference_stock_22.store_id FROM tbl_data_reference_stock_22
> >> WHERE tbl_data_reference_stock_22.session_id = '1'
>
> >> I'd like to alias the tablename to "drs22". So the query would
> >> become:
>
> >> SELECT drs22.uid, drs22.store_id FROM drs22 WHERE drs22.session_id =
> >> '1'
>
> >> Is there a way to do this?
>
> >> thanks in advance
>
> >> --
> >> FW
>
> >> mySQL version 5.0
>
> >There was a reference in the MYSQL forums that indicated back in 2004
> >that they were considering something like Oracle synonyms but I was
> >unable to find anything in the 5.0 or 5.1 docs that would indicate they
> >actually did the work...
>
> >Barring that...
>
> >create view shrtnm as select * from verylongname;
>
> >You would need to test performance to ensure it still performs
> >satisfactorily.
>
> Michael,
>
> I just wanted to thank you again for your input. Your suggestion
> works fine, but only for SELECT statements, of course. If there were
> a way to do the same thing with UPDATE, INSERT, and DELETE I'd be
> ecstatic. But there isn't, as far as I can tell. Bummer.
>
> I can't be the only person who thinks this way. How do we tell them
> we want this functionality? Not that they'd listen, but it couldn't
> hurt to try.
>
> thanks
>
> --
> FW
>
> MySQL 5.0

Views can be updateable in MySQL 5:
http://www.google.co.uk/search?q=mysql+updateable+view

On Tue, 04 Mar 2008 23:41:43 GMT, Michael Austin
<maustin@firstdbasource.com> wrote:

>FastWolf wrote:
>> On Thu, 28 Feb 2008 02:26:36 GMT, Michael Austin
>> <maustin@firstdbasource.com> wrote:
>>
>>> FastWolf wrote:
>>>> I'm new to the ng, don't mean to step on anyone's toes, but I've
>>>> sesrched the archives with no success on this one.
>>>>
>>>> I'd like to create aliases for tablenames. I don't mean a "table
>>>> alias", which is really a "row alias" (thanks to Bill Karwin for that
>>>> datum); that won't help me because I would still have to enter the
>>>> tablename at least once per query, per row. I mean an alias for a
>>>> tablename that can be used in queries, and stored either in the schema
>>>> somewhere or maybe applied once on a per-session basis.
>>>>
>>>> I have *inherited* a db with some really stupidly long tablenames, so
>>>> writing queries is a hassle. Any typo in a tablename breaks the whole
>>>> query, of course. Here's an example of what I mean: Let's say I have
>>>> a table called tbl_data_reference_stock_22 ... here's a typical query:
>>>>
>>>> SELECT tbl_data_reference_stock_22.uid,
>>>> tbl_data_reference_stock_22.store_id FROM tbl_data_reference_stock_22
>>>> WHERE tbl_data_reference_stock_22.session_id = '1'
>>>>
>>>> I'd like to alias the tablename to "drs22". So the query would
>>>> become:
>>>>
>>>> SELECT drs22.uid, drs22.store_id FROM drs22 WHERE drs22.session_id =
>>>> '1'
>>>>
>>>> Is there a way to do this?
>>>>
>>>> thanks in advance
>>>>
>>>> --
>>>> FW
>>>>
>>>> mySQL version 5.0
>>>
>>> There was a reference in the MYSQL forums that indicated back in 2004
>>> that they were considering something like Oracle synonyms but I was
>>> unable to find anything in the 5.0 or 5.1 docs that would indicate they
>>> actually did the work...
>>>
>>> Barring that...
>>>
>>> create view shrtnm as select * from verylongname;
>>>
>>> You would need to test performance to ensure it still performs
>>> satisfactorily.
>>
>> Michael,
>>
>> I just wanted to thank you again for your input. Your suggestion
>> works fine, but only for SELECT statements, of course. If there were
>> a way to do the same thing with UPDATE, INSERT, and DELETE I'd be
>> ecstatic. But there isn't, as far as I can tell. Bummer.
>>
>> I can't be the only person who thinks this way. How do we tell them
>> we want this functionality? Not that they'd listen, but it couldn't
>> hurt to try.
>>
>> thanks
>>
>
>
>I suppose if you wanted a more permanent solution that would require
>recoding your app...:
>
>http://dev.mysql.com/doc/refman/5.0/...ame-table.html

Correct, and that's a whole 'nother matter. My first inclination is
to blow it off, because in this case I wouldn't be getting paid much
for doing it. All I've really committed to do is script some SQL
pulls; CREATE VIEW is sufficient for that.

>I do not see CREATE SYNONYM in the 6.0 docs either.. you could go to the
>MYSQL site and add an enhancement request... (Axel???)

I've been thinking about doing just that, don't know if it will make
much difference but I think I will do. It's a functionality I'd find
extremely useful in the long run.

--
FW

"In the long run, we're all dead." -John Maynard Keynes

mySQL 5.0