CODEWITHSUNDEEP
mysql
Ivan
Ivan

Reputation: 7746

selecting two columns that are named strangely

I have a table schema like this:

mysql> show columns from data;
+--------------------+----------+------+-----+---------+-------+
| Field              | Type     | Null | Key | Default | Extra |
+--------------------+----------+------+-----+---------+-------+
| ('Date', '')       | datetime | YES  |     | NULL    |       |
| ('Close', 'ABBV')  | double   | YES  |     | NULL    |       |
| ('Close', 'ABMD')  | double   | YES  |     | NULL    |       |
| ('Close', 'ABT')   | double   | YES  |     | NULL    |       |
| ('High', 'ABBV')   | double   | YES  |     | NULL    |       |
| ('High', 'ABMD')   | double   | YES  |     | NULL    |       |
| ('High', 'ABT')    | double   | YES  |     | NULL    |       |
| ('Low', 'ABBV')    | double   | YES  |     | NULL    |       |
| ('Low', 'ABMD')    | double   | YES  |     | NULL    |       |
| ('Low', 'ABT')     | double   | YES  |     | NULL    |       |
| ('Open', 'ABBV')   | double   | YES  |     | NULL    |       |
| ('Open', 'ABMD')   | double   | YES  |     | NULL    |       |
| ('Open', 'ABT')    | double   | YES  |     | NULL    |       |
| ('Volume', 'ABBV') | double   | YES  |     | NULL    |       |
| ('Volume', 'ABMD') | bigint   | YES  |     | NULL    |       |
| ('Volume', 'ABT')  | bigint   | YES  |     | NULL    |       |
| ('Symbol', '')     | text     | YES  |     | NULL    |       |
+--------------------+----------+------+-----+---------+-------+
17 rows in set (0.00 sec)

If I say:

mysql> select DATE(`('Date', '')`) from data;
+----------------------+
| DATE(`('Date', '')`) |
+----------------------+
| 2010-08-10           |
| 2010-08-11           |
| 2010-08-12           |
| 2010-08-13           |
| 2010-08-16           |
...

It works. But, how do I ask for the data for Text or Double?

This doesn't work:

mysql> select DATE(`('Date', '')`), DOUBLE(`('Close', '')`) from data;

Neither does this:

mysql> select DATE(`('Date', '')`) DOUBLE(`('Close', 'ABBV')`) from data;

And this doesn't work:

mysql> select DATE(`('Date', '')`), DOUBLE(`('Close', 'ABBV')`) from data;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DOUBLE(`('Close', 'ABBV')`) from data' at line 1

Upvotes: 0

Views: 30

Answers (2)

Barmar
Barmar

Reputation: 780889

You don't need to call a conversion function, since the columns are already declared to be that type.

You can use aliases in the query to give simpler names to the columns when selecting.

select `('Date', '')` AS date, `('Close', '')` AS close from data;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

My best advice is to create a new table and insert the data:

create table data2 (
    date datetime,
    close_abbv double,
    . . .  -- repeat for all the columns
);

insert into data2
    select *
    from data;

Upvotes: 2

Related Questions