MySQL Forums
Forum List  »  General

Using a MERGE Table to export column names...
Posted by: Nick Roper
Date: February 04, 2005 04:57AM

Hi,

First of all, I'm trying to output data from a table and to include column names on the first row. The customer is using MySQL version 3.23.41 and I'm using 3.23.55

My 'solution' is as follows:

1) Create a duplicate table. I then have two tables - 'opportunities' - which has the data, and 'headers' which has one 'dummy' row in it with value, say, 'xxx' for the 'id' column.

2) Then I create a merge table 'opp_merge' based on the two tables, and making sure that all table definitions are identical.

3) Finally, I execute a query with an 'INTO OUTFILE...' clause against the 'opp-merge' table and make sure that it returns the 'dummy' row from the headers table, and order the result set so that this row is at the top.

4) The SELECT clause in the query goes as follows:

SELECT IF(opp.id = 'xxx', 'Ref Number', opp.id) as id,
IF(opp.id = 'xxx', 'Month Logged', month_logged),
IF(opp.id = 'xxx', 'Account Manager',
etc...
INTO OUTFILE 'blah...'
FROM opp_merge as opp
etc...
So, it return sthe column names if opp.id equals 'xxx', otherwise the actual data.

I test it to make sure it works, and then send a .sql file to the customer to build that database at their end.

However, it then appears that it doesn't work properly for them and they get nulls where there should be data values or column headings. I got them to send me an export of the database and have noticed a couple of things:

a) Some ofthe column types are now different between the tables - with columns defined as CHAR now showing as VARCHAR. I know that MySQL will carry out 'silent' column type changes if records in certain circumstances, but the changes are inconsistent acroo sthe relevant tables.

b) Data showing in the 'haeders' table is represented differently in the 'opp_merge' table. For example:

#
# Dumping data for table 'headers'
#
INSERT INTO headers VALUES("1","xxxxxxxx","","","xxxxxxxx","","xxxxxxxx","","xxxxxxxx","xxxxxxxx","","","","","","","","O","","xxxx","","","","","","","","");


INSERT INTO opp_merge
....
VALUES("1","","","","","","","","xxxxxxxx","","","","","","","","0.00","","0","","","","","","","","","");

This is all a bit of a 'hack' to try and get column names out, but has anyone else got any experience of issues with column types when using MERGE tables ?

Or, has anyone got a simpler solution for exporting column names as the first row of a record set ?

Phew...

Thanks,

Nick

--
Nick Roper

Options: ReplyQuote


Subject
Written By
Posted
Using a MERGE Table to export column names...
February 04, 2005 04:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.