Hi all,
Wondering if SQL can do this natively, here goes.
I have a static table with the same amount of rows and columns, which never changes; it represents distances between various locations (e.g. countries).
It's basically a distance matrix (you can find a nice example on
Wikipedia .)
My SQL statement needs to extract distances from this matrix, based on country IDs located within another table.
Selecting a row is simple:
SELECT * from cdistances where countryid =
(select countryid from countries where upper(country) = upper(&getcountry));
When I run this, I get a row of all distances to all countries from the country I enter when asked. But I would like to limit the result based on a destination country that I input.
Of course, a CASE statement would resolve this nicely, but since we are talking about over 200 countries, that's going to become unmanageable.
Another method that I have approached is to use a variable as column, such as below:
SELECT &getdestination from cdistances where countryid =
(select countryid from countries where upper(country) = upper(&getcountry));
That works well when I enter the column identifier directly (manually, from keyboard) but I'd like to automate it even further.
My column names in the distance matrix are named as follows:
country1, country2, country3... country200 - where the number id the actual country ID, preceeded by the constant string "country".
So I have tried defining a variable as a concat between "country" and another variable:
def getd2 = concat("D2C",&testcountryid);
Of course, that doesn't work; the script output is:
old:def getd2 = concat("country",&testcountryid)
new:def getd2 = concat("country",12)
where I expected it to be "country12".
So I'm kind of at a loss of thoughts. Maybe I am pushing my train of thought into the wrong direction, so I guess the general question would be: how to dynamically select a specific column from a table based on input from outside the SQL statement?