OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] MySQL - Alias in queries

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Mon Jan 28, 2008 4:00 am    Post subject: [SOLVED] MySQL - Alias in queries Reply with quote

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


Last edited by compuwatch on Tue Jan 29, 2008 3:53 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Mon Jan 28, 2008 5:48 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jan 28, 2008 9:00 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Mon Jan 28, 2008 11:39 am    Post subject: Reply with quote

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

SELECT `Customer_Timing`.`DUP` AS `Dial U`,
       `Customer_Timing`.`DUPAMP`,
       `Customer_Timing`.`DDN` AS `Dial D`,  //  IT KEEPS DDN vs Dial D in none of the instances it actuallly changes the column name. Kindly look further down as well....
       `Customer_Timing`.`DDNAMP`,
       `Customer_Timing`.`CDN` AS `Cr D`,
       `Customer_Timing`.`CDNAMP`,
       `Customer_Timing`.`CRUP` AS `Cr U`,
       `Customer_Timing`.`CRUPAMP`,
       `Customer_Timing`.`CRR` AS `Cr R`,
       `Customer_Timing`.`CRRAMP`,
       `Customer_Timing`.`CRL` AS `Cr L`,
       `Customer_Timing`.`CRLAMP`,
       ( `DUP` + `DDN` + `CDN` + `CRUP` + `CRR` + `CRL` ) / 6 AS `TIME`,
       ( `DUPAMP` + `DDNAMP` + `CDNAMP` + `CRUPAMP` + `CRRAMP` + `CRLAMP` ) / 6 AS `AMP`,

       ABS( IF( IF( `DUP` < `DDN`, IF( `DUP` < `CDN`, `DUP`, `CDN` ),
       IF( `DDN` < `CDN`, `DDN`, `CDN` ) ) < IF( `CRUP` < `CRR`, IF( `CRUP` < `CRL`, `CRUP`, `CRL` ), IF( `CRR` < `CRL`, `CRR`, `CRL` ) ), IF( `DUP` < `DDN`, IF( `DUP` < `CDN`, `DUP`, `CDN` ), IF( `DDN` < `CDN`, `DDN`, `CDN` ) ), IF( `CRUP` < `CRR`, IF( `CRUP` < `CRL`, `CRUP`, `CRL` ), IF( `CRR` < `CRL`, `CRR`, `CRL` ) ) ) -
       IF( IF( `DUP` > `DDN`, IF( `DUP` > `CDN`, `DUP`, `CDN` ), IF( `DDN` > `CDN`, `DDN`, `CDN` ) ) > IF( `CRUP` > `CRR`, IF( `CRUP` > `CRL`, `CRUP`, `CRL` ), IF( `CRR` > `CRL`, `CRR`, `CRL` ) ), IF( `DUP` > `DDN`, IF( `DUP` > `CDN`, `DUP`, `CDN` ), IF( `DDN` > `CDN`, `DDN`, `CDN` ) ), IF( `CRUP` > `CRR`, IF( `CRUP` > `CRL`, `CRUP`, `CRL` ), IF( `CRR` > `CRL`, `CRR`, `CRL` ) ) ) ) AS `DELTA`,
       
// the next IF statement is returned as <OBJECT> if entered in SQL mode. Entered in the GUI it returns a three digit number without CONCAT( :HRS) but AS PR here it does use the ALIAS.`PR`

IF( ( `DUPAMP` + `DDNAMP` + `CDNAMP` + `CRUPAMP` + `CRRAMP` + `CRLAMP` ) / 6 < 292, CONCAT( FLOOR( ( 47 + 1 - 41 ) * RAND( ) + 41 ), ':HRS' ), CONCAT( FLOOR( ( 52 + 1 - 48 ) * RAND( ) + 48 ), ':HRS' ) ) AS `PR`,

       `Customer_Order`.`CO_ID` AS `Order`,
       `Customer_Merchandise`.`M_Reference` AS `Reference`

FROM `Customer_Timing`, `Customer_Merchandise`, `Customer_Order`

WHERE ( `Customer_Timing`.`M_ID` = `Customer_Merchandise`.`M_ID`
  AND `Customer_Merchandise`.`CO_ID` = `Customer_Order`.`CO_ID` )
  AND ( ( `Customer_Order`.`CO_ID` = :Order_Number ) )


I also noticed with MySQL dates (if GROUP) are returned as 5Digit numbers. I apoligize for this long posting and truly appreciate your insight.

As always,
_________________
Kind Regards,

HANS
Back to top
View user's profile Send private message Send e-mail Visit poster's website
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Mon Jan 28, 2008 11:46 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jan 28, 2008 1:46 pm    Post subject: Reply with quote

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 ). Smile
  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
Back to top
View user's profile Send private message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Mon Jan 28, 2008 2:47 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jan 28, 2008 3:49 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Mon Jan 28, 2008 4:10 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
phasel
General User
General User


Joined: 15 Jun 2008
Posts: 13

PostPosted: Sun Jun 15, 2008 4:02 am    Post subject: Mysql alias in queries Reply with quote

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
Back to top
View user's profile Send private message
sirio81
General User
General User


Joined: 08 Mar 2007
Posts: 12

PostPosted: Thu Jan 29, 2009 6:48 am    Post subject: Reply with quote

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".
Back to top
View user's profile Send private message Visit poster's website
sirio81
General User
General User


Joined: 08 Mar 2007
Posts: 12

PostPosted: Fri Jan 30, 2009 7:58 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website
mlebek
General User
General User


Joined: 15 Jul 2004
Posts: 13
Location: Germania

PostPosted: Sun Jun 24, 2012 8:58 pm    Post subject: Reply with quote

maybe a follow-up:
http://www.oooforum.org/forum/viewtopic.phtml?p=503579#503579
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group