Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

pattern/ matrix table

AQHNov 7 2018 — edited Nov 7 2018

i have tables iat and ia. iat has partC1,partC2 and partC3.

ia has fk with iat and contains values of

partC1 from -4,-3.75 ... (shown in light gold color in image)

partC2 from 0.00,-0.25...(shown in light blue)

partC3 from 0.25,0.50 .. (shown in green)

above table has to use in matrix chart for me. for example i have to get followng values from my query using above table iat and ia.

data in above table may be combination of partC1 X partC2 and sometime partC1 X partC2 X partC3 for example

combination example partC1XpartC2 (shown in yellow) -2.25 X -0.50

combination example partC1xpartC2xpartC3 (shown in red) -1.00 X -4.00 X 3.25

partC3 is occasionally use in combination.

should i create one more table with all combination define(insert) in this new column so i can easy use and get this table without query these iat and ia?

can i easily create these max combination using iat and ia values partC1,partC2 & partC3.

please guide

Capture.PNG

| **iat
** | **ia
** |
|

CREATE TABLE IAT

(

IATCODE VARCHAR2(6 BYTE) NOT NULL,

TITLE VARCHAR2(50 BYTE) NOT NULL,

PREFIX VARCHAR2(50 BYTE)

);

insert into iat (iatcode,title) values ('000001','partC1');

insert into iat (iatcode,title) values ('000002','partC2');

insert into iat (iatcode,title) values ('000003','partC3');

|

CREATE TABLE IA

(

IACODE VARCHAR2(20 BYTE) NOT NULL,

IATCODE VARCHAR2(6 BYTE) NOT NULL,

DESCRIPTION_NUMBER NUMBER(5,2)

);

insert into ia (iacode,iatcode,description_number) values ('10165','000001',-4);

insert into ia (iacode,iatcode,description_number) values ('10166','000001',-3.75);

insert into ia (iacode,iatcode,description_number) values ('10167','000001',-3.5);

insert into ia (iacode,iatcode,description_number) values ('10168','000001',-3.25);

insert into ia (iacode,iatcode,description_number) values ('10169','000001',-3);

insert into ia (iacode,iatcode,description_number) values ('10170','000001',-2.75);

insert into ia (iacode,iatcode,description_number) values ('10171','000001',-2.5);

insert into ia (iacode,iatcode,description_number) values ('10172','000001',-2.25);

insert into ia (iacode,iatcode,description_number) values ('10173','000001',-2);

insert into ia (iacode,iatcode,description_number) values ('10174','000001',-1.75);

insert into ia (iacode,iatcode,description_number) values ('10175','000001',-1.5);

insert into ia (iacode,iatcode,description_number) values ('10176','000001',-1.25);

insert into ia (iacode,iatcode,description_number) values ('10177','000001',-1);

insert into ia (iacode,iatcode,description_number) values ('10178','000001',-0.75);

insert into ia (iacode,iatcode,description_number) values ('10179','000001',-0.5);

insert into ia (iacode,iatcode,description_number) values ('10180','000001',-0.25);

insert into ia (iacode,iatcode,description_number) values ('10181','000001',0);

insert into ia (iacode,iatcode,description_number) values ('10182','000001',0.25);

insert into ia (iacode,iatcode,description_number) values ('10183','000001',0.5);

insert into ia (iacode,iatcode,description_number) values ('10184','000001',0.75);

insert into ia (iacode,iatcode,description_number) values ('10185','000001',1);

insert into ia (iacode,iatcode,description_number) values ('10186','000001',1.25);

insert into ia (iacode,iatcode,description_number) values ('10187','000001',1.5);

insert into ia (iacode,iatcode,description_number) values ('10188','000001',1.75);

insert into ia (iacode,iatcode,description_number) values ('10189','000001',2);

insert into ia (iacode,iatcode,description_number) values ('10190','000001',2.25);

insert into ia (iacode,iatcode,description_number) values ('10191','000001',2.5);

insert into ia (iacode,iatcode,description_number) values ('10192','000001',2.75);

insert into ia (iacode,iatcode,description_number) values ('10193','000001',3);

insert into ia (iacode,iatcode,description_number) values ('10194','000001',3.25);

insert into ia (iacode,iatcode,description_number) values ('10195','000001',3.5);

insert into ia (iacode,iatcode,description_number) values ('10196','000001',3.75);

insert into ia (iacode,iatcode,description_number) values ('10197','000001',4);

insert into ia (iacode,iatcode,description_number) values ('10198','000002',0);

insert into ia (iacode,iatcode,description_number) values ('10199','000002',-0.25);

insert into ia (iacode,iatcode,description_number) values ('10200','000002',-0.5);

insert into ia (iacode,iatcode,description_number) values ('10201','000002',-0.75);

insert into ia (iacode,iatcode,description_number) values ('10202','000002',-1);

insert into ia (iacode,iatcode,description_number) values ('10203','000002',-1.25);

insert into ia (iacode,iatcode,description_number) values ('10204','000002',-1.5);

insert into ia (iacode,iatcode,description_number) values ('10205','000002',-1.75);

insert into ia (iacode,iatcode,description_number) values ('10206','000002',-2);

insert into ia (iacode,iatcode,description_number) values ('10207','000002',-2.25);

insert into ia (iacode,iatcode,description_number) values ('10208','000002',-2.5);

insert into ia (iacode,iatcode,description_number) values ('10209','000002',-2.75);

insert into ia (iacode,iatcode,description_number) values ('10210','000002',-3);

insert into ia (iacode,iatcode,description_number) values ('10211','000002',-3.25);

insert into ia (iacode,iatcode,description_number) values ('10212','000002',-3.5);

insert into ia (iacode,iatcode,description_number) values ('10213','000002',-3.75);

insert into ia (iacode,iatcode,description_number) values ('10214','000002',-4);

insert into ia (iacode,iatcode,description_number) values ('10215','000003',0.25);

insert into ia (iacode,iatcode,description_number) values ('10216','000003',0.5);

insert into ia (iacode,iatcode,description_number) values ('10217','000003',0.75);

insert into ia (iacode,iatcode,description_number) values ('10218','000003',1);

insert into ia (iacode,iatcode,description_number) values ('10219','000003',1.25);

insert into ia (iacode,iatcode,description_number) values ('10220','000003',1.5);

insert into ia (iacode,iatcode,description_number) values ('10221','000003',1.75);

insert into ia (iacode,iatcode,description_number) values ('10222','000003',2);

insert into ia (iacode,iatcode,description_number) values ('10223','000003',2.25);

insert into ia (iacode,iatcode,description_number) values ('10224','000003',2.5);

insert into ia (iacode,iatcode,description_number) values ('10225','000003',2.75);

insert into ia (iacode,iatcode,description_number) values ('10226','000003',3);

insert into ia (iacode,iatcode,description_number) values ('10227','000003',3.25);

insert into ia (iacode,iatcode,description_number) values ('10228','000003',3.5);

insert into ia (iacode,iatcode,description_number) values ('10229','000003',3.75);

insert into ia (iacode,iatcode,description_number) values ('10230','000003',4);

|
This post has been answered by mathguy on Nov 7 2018
Jump to Answer
Comments
Post Details
Added on Nov 7 2018
#sql, #sqlplus
16 comments
338 views