0

I have a table with a number field (non-null). I have another table with a varchar2 field. Examples:

Users
UID,Name
123,Mickey
41128,Joe
43,Alice

Groups
UIDS,Name
(123,43),Children
(43),Admins
(),Deleted

I want to join the two tables where the number column is "in" the varchar2 column. As you can see, the UIDS column has been developed to look like the syntax of an "in" join. However, the following does not work:

select * from users join groups
where users.uid in groups.uids;

The number field cannot be in a string. I can cast the number field to a varchar 2 field, but it doesn't recognize the groups.uids column as a list of uids. It recognizes it as one value.

I believe that the next step I need to do is to strip the ( and ) from the uids value and then split it on the comma to create a list. Is that correct or is there a simpler way to merge these two tables?

  • 2
    I feel the pain of a bad database design – kevinsky Jun 1 '15 at 16:48
  • @kevinksy It isn't a database. It is two CSV files that I've loaded into Oracle. These files will change every week, so I'm trying to avoid exhaustive manipulation of the data. – kainaw Jun 1 '15 at 16:52
0

To my knowledge, this cannot be done directly, but almost directly. It involves loading the data the compound field as a nested table. First, you have to create a nested table type:

create or replace type multi_uids as table of number(10,0);

Now, the table with the compound field must have a type of the nested table and declare that the nested table be stored as a nested table.

create table groups (
  uids multi_uids,
  name varchar2(20)
) nested table uids store as uids_tab;

When loading the raw data, cram the type in front of the compound string. So, the compound string might be (123,443,13), you actually insert multi_uids(123,443,13). This will store the compound field as a nested table.

New problem - how do you join the number from users on the nested table for groups? You have to cast the nested table to a table in the join.

select * from users
join groups
on users.id in (select * from table(groups.uids));

The end result is that you get a join where users.id is in groups.uids.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.