Display text field where a number field is in a view

[TFrye]

I am trying to modify a view to place one of several text fields from one table where a number field from another table would be. These are linked by an ID. Would this be possible with a subquery? other? Not sure if I'm clear enough, so when I pull the number field from table one, I want to see a corresponding text field from table 2 instead of the number. Any thoughts on this would be appreciated.

 

[BJCooperIT]

Are you looking for somthing like:

CREATE TABLE testa (id NUMBER, vchr_fld VARCHAR2(5));
INSERT INTO testa VALUES (1,'A');
INSERT INTO testa VALUES (2,'B');
INSERT INTO testa VALUES (3,'C');
INSERT INTO testa VALUES (4,'D');
INSERT INTO testa VALUES (5,'E');
CREATE TABLE testb (ID NUMBER, chr_fld CHAR(2));
INSERT INTO testb VALUES (1,'AZ');
INSERT INTO testb VALUES (2,'NY');
INSERT INTO testb VALUES (3,'GA');
INSERT INTO testb VALUES (4,'OR');
INSERT INTO testb VALUES (5,'NM');
COMMIT;

SELECT  a.*, b.*, decode(a.ID,3,b.chr_fld,a.ID) clc
FROM    testa A, testb B
WHERE   A.ID = B.ID;

[b]ID   VCHR_FLD   ID   CHR_FLD   CLC[/b]
1    A          1    AZ        1
2    B          2    NY        2
3    C          3    GA        GA
4    D          4    OR        4

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[TFrye]

not quite I don't think. I have a table with a field containing numbers in several columns(different numbers in each column) and a new table that contains a text field which has information that now needs to be where the numbers were displayed. I take each column from one of many tables. I think I'm missing a piece of information, I will repost when I get that. Thanks for helping. Any more thoughts?

 

[TFrye]

I'll try an example:

Table A, Table B, Table C, Table D exist and are tied into a view, View A,
where A.id = B.id2
AND A.id = C.id2
And A.id = D.id2

There is an addition of a new table, Table X, which contains id and id2 and text_field_1

View A currently pulls 1-3 fields (vf_1, vf_2, vf_3) from each of tables B, C, and D, each a number that needs to be changed

Table X has a new ID, say ID_3...I need to join in Table X and based on id2 and ID3, put text_field_1
into corresponding view field, vf_1, vf_2 vf_3

 

[oradba101]

Good morning,

TFrye has just started working here and I gave him the assignment of posting here to solve this issue.

Here is some further clarification:

The view in question looks like this:

trt_plan_id FieldA FieldB FieldC FieldD
----------- ------ ------ ------ ------
999999 102000 94040 130200 94922
1000000 97685 102200 99034 104033

The numbers above correspond with a field in a table which holds the text data we are trying to return. For example

trt_plan_id ID Source ID Text
----------- ------ ---------- ---------------
999999 102000 9312 The customer is angry
000000 94040 4567 Ronald McDonald has red hair.

Basically, we are trying to return the text from the second table to the view. So we have to relate the trt_plan_id fields together and also the values of fieldA, fieldB, fieldC and FieldD.

Regards,


William Chadbourne
Oracle DBA