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

Reply
Yellow Belt

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

 

Accepted Solutions
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
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)
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.

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"
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

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

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
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!