As a trivial example, suppose I have two tables:

Table "INDEX" where FLDA is the index and FLDB is an arbitrary matching value
Table "MASTER" where there are over 30 SYN# fields
Each MASTER.SYN# field will allways match a record of INDEX.FLDA. I want to return the value of INDEX.FLDB, so my desired query resultset is:
So, I can do one field at a time with something like:

SELECT INDEX.FLDB FROM INDEX, MASTER
WHERE MASTER.SYN1=INDEX.FLDA;

... but how do I do SYN2, SYN3, etc all at once?

Short of nesting 30 queries or creating 30 copies of the INDEX table, is there a way to accomplish this with regular SQL?

Failing that, I guess I'll have to figure out how to make a VB or PL/SQL routine (client is access, served by oracle), but this seemed like a job for SQL...

Looks more like a job for a database designer! The MASTER table is horribly denormalised, which makes for horrible queries, as you have found. Instead of 30 SYN# columns in MASTER, there should be 1 SYN column and 30 rows.

Hi ,

You can use scalar subquery in this regard. But that is also not a best solution.. But anyway here i am presenting..


select ( select fldb from index where flda = syn1), ( select fldb from index where flda = syn2), ( select fldb from index where flda = syn3),
( select fldb from index where flda = syn4), ... ( select fldb from index where flda = syn30) from master;

For the above above query to work, flada should be unique in INDEX table.

Thanks, it works great!

Takes a few hours to process everything, but thats alright.

Well what do you think I'm DOING? =P

The company that developed this poor excuse for a recordkeeping system should have been out of business years ago... I'm just an intern working to RE the thing so we can feed CSV files of our data to the start-up that is developing the replacement software.