Selecting column by its number

Question

How to select table's columns using its number? For example:

Select col:1, col:2
From Banks

instead of

Select Id, Name
From Banks

I have problem like this - I have list of selecting queries in database, but first and second columns has different names/aliases:

• Id, Name
• Name, Description
• 1 CODE, 'Male' VALUE
• ...

I want to filter them with a parameter:

Select Id, Name
From Banks
Where lower(Name) like lower(''%' + p_bank_name + '%'')'

So, how can I write this code:

Select col:1, col:2
From Banks
Where lower(col:2) like lower(''%' + p_bank_name + '%'')'

??

Also, I can write sub queries:

Select col:1, col:2
From (
  Select Id, Name
  From Banks
) r
Where lower(col:2) like lower(''%' + p_bank_name + '%'')'

But, how??

Answer 1

This is not exactly same as what you are trying to do. However, It is almost there. It won't select column by number, however you dont have to specify the explicit column from your real table while writing this query.

As all us suggested, you have to use the dynamic SQL. This is a little idea I created:

create table test1(name1 varchar(10), address1 varchar(10), zipcode1 varchar(10))

insert into test1 values('Test1.1','USA','12344')
insert into test1 values('Test1.2','USA','12344')
insert into test1 values('Test1.3','USA','12344')
insert into test1 values('Test1.4','USA','12344')

create table test2(name2 varchar(10), address2 varchar(10), zipcode2 varchar(10))

insert into test2 values('Test2.1','USA','12344')
insert into test2 values('Test2.2','USA','12344')
insert into test2 values('Test2.3','USA','12344')
insert into test2 values('Test2.4','USA','12344')

You see, the Table name, and the Column name are completely different in both. Now this sql statement doesn't care about column names :

select * from
(
    select '' as T1, '' as T2, '' as T3
    union all
    select * from test1   --No matter whether it is Id, Name or description
    union all
    select * from test2   --No matter whether it is Id, Name or description
) as D
where D.T1<>''  -- your other conditions!

Only issue is, since we are using Union, you have to match the number of columns when you specify your empty columns:

select '' as T1, '' as T2, '' as T3, '' as T4, 0 as T5 -- and so on

Here's the output: