Table data in Alphabetical Column Name order in SQLA

Database
Enthusiast

Table data in Alphabetical Column Name order in SQLA

Hi,

How can I get "Select * from tablename" result in alphabetical order of column names in SQLA window ?

Ex. Table/view has columns defind in order A,C,D,B , then sel * from table should show columns in order A, B ,C ,D.

Tags (1)
1 REPLY
Enthusiast

Re: Table data in Alphabetical Column Name order in SQLA

Hi Nagendra,

I don't think there is any straight forward query to achieve this. However, you can try this:

CREATE MULTISET TABLE YourDBName.Tbl_ColOrderedQuery (
ColName VARCHAR(50),
CommaCol VARCHAR(1),
DatabaseName VARCHAR(30),
TabName VARCHAR(30)
) PRIMARY INDEX (DatabaseName, TabName);

REPLACE PROCEDURE YourDBName.ColOrderedQuery (
IN DB_Name VARCHAR(30),
IN Tbl_name VARCHAR(30)
)

BEGIN
DECLARE SqlTxtTmp VARCHAR(500);
DECLARE SqlTxt VARCHAR(600);

FOR cur AS
SELECT
ColumnName
, CASE WHEN AlphaColNum = 1 THEN '' ELSE ',' END AS Comma
, DataBaseName
, TABLENAME
FROM (
SELECT
TRIM(ColumnName) AS ColumnName,
TRIM(DatabaseName) AS DataBaseName,
TRIM(TABLENAME) AS TABLENAME,
ROW_NUMBER() OVER (ORDER BY ColumnName DESC) AS AlphaColNum
FROM dbc.Columns
WHERE DatabaseName = :DB_Name
AND TABLENAME = :Tbl_Name
) A
ORDER BY ColumnName ASC

DO
SET SqlTxtTmp =
'INSERT INTO YourDBName.Tbl_ColOrderedQuery (ColName, CommaCol, DatabaseName, TabName) ' ||
'VALUES (''' || cur.ColumnName || ''',''' || cur.Comma || ''',''' || cur.DataBaseName ||
''',''' || cur.TABLENAME || ''');';
CALL dbc.sysexecsql(:SqlTxtTmp);

END FOR;

END;

CALL YourDBName.ColOrderedQuery('YourDBName', 'YourTableName');

/* Query to generate Column Ordered Select Query */
SELECT
CASE WHEN RowNumAsc = 1 THEN 'SELECT ' || ColName || CommaCol
WHEN RowNumAsc > 1 AND TRIM(CommaCol) = ',' THEN ColName || CommaCol
ELSE ColName || CommaCol || ' FROM ' || DatabaseName || '.' || TabName || ';'
END AS MyCustQry
FROM (
SELECT
ColName,
CommaCol,
DatabaseName,
TabName,
ROW_NUMBER() OVER (ORDER BY ColName ASC) AS RowNumAsc
FROM YourDBName.Tbl_ColOrderedQuery
) A
;