[SOLVED] Simple (?) SQL: 'consolidating' fields in a query?

whuppins

I'm trying to query a table that has three fields that are all really used to store one piece of information. For example, the following are all possible entries:
[html]
NAME ID_FIELD_1 ID_FIELD_2 ID_FIELD_3

---

---

---

---

Entry A 56892
Entry B 56893
Entry C 56894
Entry D 56895 56895
[/html]
So, it's pretty random, but for a given row, at least one of these fields will always have the ID, and the other(s) will always be blank.

Since they all act as one big field anyway, I want to be able to work with them as though I were working with a single field -- at least for the purpose of being able to a.) Display them as a single column and b.) sort by that column. What's the best way to do this? My limited knowledge of SQL tells me that I could probably just use nested if/then logic to check each field until a non-null value is found, but I'm hoping there's a slightly more elegant solution. And no, I can't alter the DB in any way. The query has to do all the work.

It's an Oracle DB if that helps. Thanks!

Vrtra Theory

You should be able to just concatenate all the fields together, I believe Oracle treats NULL as empty strings during concatenation.

[html]select name, (id_field_1 || id_field_2 || id_field_3) from table;[/html]

And order exactly the same way:

[html]select * from table order by (id_field_1 || id_field_2 || id_field_3)[/html]

vonPoonBurGer

That table design is... weird. I'll just assume there's some compelling reason why it's designed that way. Anyway, Oracle has aggregation functions (sum, max, min, avg, etc.), but they're used to aggregate data from a single column across rows within a table, you can't really use them to aggregate data from different columns within a single row. The simplest way I can think of to do this would be to use two nested NVL statements, as follows:

select nvl(id_field_1, nvl(id_field_2, id_field_3)) from table order by 1;

Basically, if id_field_1 is not null, that's the value that gets returned. If id_field_1 is null, though, it evaluates the inner NVL statement. That NVL statement will return id_field_2 if that value is not null; otherwise it returns id_field_3. As long as you don't have any rows where all three ID columns are null, you should get the value in the first column that doesn't contain a null. Obviously, this query structure doesn't work well if you can have rows with different ID values in different fields, or if you can have rows where all the ID values are null. For the sample table posted though, it does give you a way to aggregate those three columns into one ID column, with no duplication of the values in the event that a row has an ID value repeated in more than one ID column.

whuppins

Both these solutions look really good so far. Thank you. And yes, there's a semi-legitimate reason for this table structure. It involves different vendors' products being able to find a common ground in Oracle.

Edit: Er, wait. I don't think the first solution will work because it's possible for a row to have two of the three fields filled (albeit with the same value). I'll press on with the nested NVLs. Thanks again.

Lewisham

vonPoonBurGer wrote: »

That table design is... weird. I'll just assume there's some compelling reason why it's designed that way.

Very compelling.

foggrat

whuppins wrote: »

[html]
NAME ID_FIELD_1 ID_FIELD_2 ID_FIELD_3

---

---

---

---

Entry A 56892
Entry B 56893
Entry C 56894
Entry D 56895 56895
[/html]

Assuming those big chunks in the data are nulls, you can use the coalesce function.

In MS-SQL, it'd be:

select coalesce(ID_FIELD_1, ID_FIELD_2, ID_FIELD_3) as ID_FIELD, * from [CrazyTable]

I assume the oracle syntax is similar

whuppins

Ooh, coalesce looks perfect. Thanks for the suggestion. I think I'll call this solved now, since I have at least two good working solutions.