All Forums Database
nagendratrpth 25 posts Joined 03/14
13 Jun 2016
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.

sk8s3i 35 posts Joined 06/13
13 Jun 2016

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
;

 

-Thanks Shardul

You must sign in to leave a comment.