[SOLVED] MySQL - Alias in queries

[compuwatch]

In my recent attempt to learn / use MySql I noticed that all my queries aliases (AS) are ignored and columns keep the original name. I am a complete ROOKIE to MySQL and any help is greatly appreciated. Their is a good chance I configured the server wrong. Are their any additional SCHEMA privileges or settings I forgot to consider?

As always,
_________________
Kind Regards,

HANS

[compuwatch]

I just realized Alias worked on the result side like, CONCAT(Company,Phone) AS Test but it will not change the existing field names if an ALIAS was given.

As always,
_________________
Kind Regards,

HANS

[Sliderule]

Hans:

I do not understand your question . . . assuming you are writing a Query with Base ( in OpenOffice ) . . . you can use define your alias as desired whether the database is HSQL, or MySQL ( same is true with dBase, Access, etc too ).

Can you be more specific with what your question is, and, perhaps, give an example of what is happening, and, what you expect to happen.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.

[compuwatch]

You are truly a LIFELINE and I thank you for helping all the needy people on this board. Like in the query you where kind enough to compose for my watch repair shop.

Example:

[compuwatch]

Originally I thought it is my setup at home. I realized the same happened in my office as well.
AS is just ignored on existing COLUMN names. It worked however when the query has to create a new column example calculation then the Alias is used....
_________________
Kind Regards,

HANS

[Sliderule]

Hans:

I think, you have asked three separate question (of course, I could be mistaken, would not be the first, nor the last time today ).

1. About, OpenOffice Base, using MySQL as your database, not 'accepting' the AS clause when creating an Alias for 'existing COLUMN names' . . . I canNOT recreate this problem on my computer. Put another way, it works for me. Sorry.
   
2. Regarding the IF statement returning a three digit number withOUT CONCAT etc . . . I can only say that it does work for me, and, I do NOT know why it is not working for you . . . my only suggestion is to 'break' it down into smaller pieces, and see what is working, and, what is not. For example, add a new line withOUT the IF portion, and, see what you get. Sorry.
   
3. Regarding DATES returned as 5 Digit numbers . . . I will assume, if you run the same Query in SQLyog, OR, MySQLQuery, it works as desired. If so, in OpenOffice, just RIGHT CLICK on the column name, and, FORMAT the column to display as a date ( with the format you want ). Next, re-save the query. Next time you run it, it should ( I hope ) display the column as a date.

I hope this helps.

Sliderule

[compuwatch]

Thanks for your answer. I knew it had to be on my side. I would assume ooBASE and MySQL worked great. Could I ask you which version MySQL do you recommend or use? Also what kind of Java version do you run with ooBASE?

On my side I run:
JAVA 1.6.0_03
MySQL 5.1
MySQL connectorJava 5.0.8

As always,
_________________
Kind Regards,

HANS

[Sliderule]

Hans:

I am NOT an expert in this area . . . and . . . perhaps, Drew or someone else would be more helpful.

But, to answer your questions . . . I am using:

• JAVA: 1.6.0_03
  
• MySQL version: 5.0.19-nt ( I determined this with this SQL statement running against MySQL directly: SELECT VERSION() )
  
• MySQL connection is: ODBC

By the way . . . a very good "Tutorial" for "Connecting OpenOffice.org To A Remote MySQL Database" can be found at:

http://ericzander.com/Publications/OO_MySQL_Tutorial/Tutorial.htm

Sliderule

[compuwatch]

Found the solution by down-grading MySQL to Version 5.0 with the corresponding JAVA connector. All is well now and columns can be renamed using AS. All formatting problems disappeared as well.


As always,
_________________
Kind Regards,

HANS

[phasel]

Hi,

I am new to this forum, as I am also quite new to databases.

I was trying to use base to make it a simple way to create queries and forms for mysql (that I decided was what I needed for my databases), but I found this problem building queries. ALIASES are just ignored, so if I try to take in the query fields from different tables but that share the same name, it won't work (it just fills with the first name that fits that name all over the query)
Strangely, even SQL directly does not get aliases right. If I execute the same command directly in the mysql console, it works perfectly.
I found a similar post that found the solution in downgrading mysql, but I would prefer not to get in that mess.
My specs:

mysql 5.0.51a-3ubuntu5.1
Java 1.6.0_06
Openoffice 2.4.0

I can partially solve it using concats everywhere, because for some reason, if I use concats it works, but it is a mess...
Does anyone have any idea on how to solve this?

Thanks a lot for your help,

Best regards,

Rodolfo

[sirio81]

I wanted to say that I have the same problem with the aliases.
I'm using OOo3 on Centos 5 (linux) and I'm connected with a simple mysql database.

Aliases are not shown in the queries.
Not only, they cause problems in the report!!!
I'm having also other kind of problem like I can't edit a query after I closed its windows.
When I try to open it again I receive the message "Impossibile to connect".

[sirio81]

I just tried to download the latest 3.0.1 OOo version and the aliases are still not working fine.

I forgot the say that I'm using mysql-connector-java-5.1.7 (jdbc).

I made a new database in the native odb format and there the alaises work fine.

I'm going to try with a older OOo version (2.0.4) and see how it works.

I wonder if the problem is realted to OOo or the connector.

What do you think?
I see the tag [solved] in the topic title but I can't see the solution.
Thank you.

[mlebek]

maybe a follow-up:
http://www.oooforum.org/forum/viewtopic.phtml?p=503579#503579