how do you rearrange the columns in a table in alpha order in a mysql data flow?

Reply
Highlighted
Yellow Belt

how do you rearrange the columns in a table in alpha order in a mysql data flow?

 

Accepted Solutions
Highlighted
Green Belt

Re: how do you rearrange the columns in a table in alpha order in a mysql data flow?

I think we just needed to account for the backticks around the column names -

 

Try this amendment:

 

create procedure order_cols3()
begin
SET session group_concat_max_len = 10000;
set @sql = (

select
concat('CREATE TABLE sorted_tbl AS SELECT ',group_concat(col separator ','), ' FROM transform_data_1') as f

from(
select
concat('`',column_name, '`') as col
from
information_schema.columns
where table_name = 'transform_data_1'
order by
column_name)
a
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end

I work for Domo.
**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post


All Replies
Highlighted
Green Belt

Re: how do you rearrange the columns in a table in alpha order in a mysql data flow?

Hi LSmo,

 

Thank you for posting in Dojo!

 

In MySQL you are able to query the schema of the table, if you were wanting to generate a list of the column names alphabetically, you could use the following statement (and then copy and paste it into a select statement):

 

select column_name as col

from information_schema.columns

where table_name = 'your table name'

order by column_name

 

 

Or if you are looking to have the query itself select and reorder for you, you could utilize these steps:

 

Step 1:

create procedure order_cols()

begin
SET session group_concat_max_len = 10000;
set @sql = (


select
concat('CREATE TABLE sorted_tbl AS SELECT ',group_concat(col separator ', '), ' from your table name') as f


from(
select
column_name as col

from
information_schema.columns

where table_name = 'your table name'

order by
column_name)
a
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

end

 

Step 2:
call order_cols()

 

 

Step 3:
In transform or output

select
*
from
sorted_tbl

I work for Domo.
**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Tags (3)
Highlighted
Yellow Belt

Re: how do you rearrange the columns in a table in alpha order in a mysql data flow?

Thank you very much for the suggestions! Incredibly helpful!!

 

Creating the procedure works; however, calling the procedure consistently spits back the following error:

 

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Campaign.StartDate' in 'field list' MaestroSQLException{device='ec2-authoring-02', sql='call order_cols4()', type='update', catalog='dataflow_c11a13662ab8410fb6f3127e3f2bc46a', duration='3'}

 

I've included the line "call order_cols()" in a separate transform. What do you think is contributing to that error?

 

Thanks again for all your help.

Highlighted
Green Belt

Re: how do you rearrange the columns in a table in alpha order in a mysql data flow?

Hi LSmo,

 

Have you double checked that you changed the two instances of 'your table name' in the procedure?

 

 

I work for Domo.
**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
Yellow Belt

Re: how do you rearrange the columns in a table in alpha order in a mysql data flow?

yep-- should each of the table names be in ' ' ?

 

create procedure order_cols()
begin
SET session group_concat_max_len = 10000;
set @sql = (

select
concat('CREATE TABLE sorted_tbl AS SELECT ',group_concat(col separator ','), ' FROM transform_data_1') as f

from(
select
column_name as col
from
information_schema.columns
where table_name = 'transform_data_1'
order by
column_name)
a
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end

Highlighted
Green Belt

Re: how do you rearrange the columns in a table in alpha order in a mysql data flow?

I think we just needed to account for the backticks around the column names -

 

Try this amendment:

 

create procedure order_cols3()
begin
SET session group_concat_max_len = 10000;
set @sql = (

select
concat('CREATE TABLE sorted_tbl AS SELECT ',group_concat(col separator ','), ' FROM transform_data_1') as f

from(
select
concat('`',column_name, '`') as col
from
information_schema.columns
where table_name = 'transform_data_1'
order by
column_name)
a
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end

I work for Domo.
**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

Highlighted
Yellow Belt

Re: how do you rearrange the columns in a table in alpha order in a mysql data flow?

YESS!!!! IT WORKS!!!

 

AMAZING. VERY CLUTCH.

 

1000 THANK YOUS

Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.