Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Unanswered: multiple field "search and replace" query

    As a trivial example, suppose I have two tables:

    Table "INDEX" where FLDA is the index and FLDB is an arbitrary matching value
    Code:
    	FLDA	FLDB
    
    1 	AAAA	1111
    2 	BBBB	2222
    3 	CCCC	3333
    4 	DDDD	4444
    Table "MASTER" where there are over 30 SYN# fields
    Code:
    	SYN1 	SYN2 	SYN3 	SYN4
    
    1 	AAAA 	BBBB 	CCCC 	DDDD
    2 	AAAA 	AAAA 	AAAA 	BBBB
    3 	AAAA 	BBBB 	BBBB 	BBBB
    4 	BBBB 	CCCC 	CCCC 	DDDD
    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:
    Code:
    	SYN1 	SYN2 	SYN3 	SYN4
    
    1 	1111 	2222 	3333 	4444
    2 	1111	1111 	1111 	2222
    3 	1111	2222 	2222 	2222
    4 	2222 	3333 	3333 	4444
    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...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: multiple field "search and replace" query

    Originally posted by Gherald
    ...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.

  3. #3
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    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.
    Regards
    Suneel

  4. #4
    Join Date
    Oct 2003
    Posts
    10

    Re: multiple field "search and replace" query

    Thanks, it works great!

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

    Originally posted by andrewst
    Looks more like a job for a database designer!
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •