Query on Comma Seperated Values

G

Guest

Unfortunately, I've encountered a "Memo" field with comma seperated values
that I'm trying to query on and have no idea how to do this. Essentially,
what I need to do is look up my unique ID from one table in the comma
seperated memo field in another table and return the value of another field
in the same table as the comma seperated value.

One table is named: dbo_COMPANY
the field in this table that is unique is: COMPANY

The other table is named: dbo_PREDEF_LIST
the memo field with the comma seperated values containing COMPANY is: LIST
the field value I need to return from this table associated with where
COMPANY is found in the LIST field is: NAME

Can any one help me write this query?

Thank you!
T
 
J

John Nurick

Hi Tina,

What you ask can't be done without writing procedural code, which would
be slow and hard to maintain.

Instead, you should do is to create another table to store the data
that's now in your memo field, one record in the table for each line in
the memo field for each record in the dbo_PREDEF_LIST table. Each record
in the new table must also contain a key value to link it to the
"parent" record in dbo_PREDEF_LIST. Once that's done, the querying is
simple.
 
G

Guest

I completely agree with you ... Unfortunately, this is a linked Oracle
database to tables that I can not modify. Because of this I'm trying not to
"re-create" the wheel and perform unnecessary "clean up" work. I'm not sure
why the owner of this Oracle database did not normalize this field as the
rest of the entire database but they didn't.

I could do this function and as you say easily create the query I'm looking
for. The problem arrises when the owner of the database makes changes, then
my new tables are not updated.

Any other suggestions?
 
J

John Nurick

We'll need to know a lot more about the data first. Can you post some
samples, including the field names, some examples of the contents of the
memo field and the other relevant field values togetehr with the result
you want to obtain. Also please answer the following:

1) Comma-separated values in the memo field: is it possible for commas
to appear within the data, or is every comma in the memo field
guaranteed to be a separator? If there can be commas in the data, you
need a proper CSV parser - and so far I've never found a good one
written in VB/A/Script.

2) Is the task one of retrieving the value of NAME from dbo_PREDEF_LIST
where the value of dbo_COMPANY.COMPANY appears somewhere in
dbo_PREDEF_LIST.MEMO - or does the value of NAME have to be retrieved
from the same row in the memo field in which the value of COMPANY was
found?
 
G

Guest

Ok, A little background on the tables.

The dbo_COMPANY table is a table which contains one row for each airline
name and a unique ID (COMPANY) for each airline. For example, in this table
I'll find somthing like 'CSC' in the COMPANY field which is the unique
identifier for China Southern Cargo Airlines.

The dbo_PREDEF_LIST is another table which has a row for each "group" of
airlines and in the same row the memo field which identifies each airline in
the group by their unique ID. For example, there is a row that identifies an
airline group with field NAME which contains "China Southern Airline Group"
and on the same row the memo LIST field will contain all of the unique
identifiers for the airlines who belong to this group. This is the same
unique ID as found in the dbo_COMPANY table in the field COMPANY. In this
example the LIST field contains ['CSC','CSA','CNA','CXA','TNA']. So this
tells me that China Southern Cargo Airlines is part of the China Southern
Group. In the same table there may be another row with information for
another group that this airline may be part of.

What I'm after is a query that will tell me which groups all of the airlines
in the dbo_COMPANY table are part of using the dbo_PREDEF_LIST.

Answer to your question 1 = No, they are purely all of the unique IDs only.
Answer to your question 2 = I'm trying to pull the value of NAME

Thanks,
T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top