We help IT Professionals succeed at work.

Oracle Forms question

anumoses
anumoses asked
on
Oracle forms question

I have 2 blocks ( master, detail- look like but not exactly)

Both blocks are form same table. Table is PROFILES

1st block (profile_type = 'CONFIGATR')

2nd block (profile_type =  CONFIGVAL')

1st block queries fine as where clause in the block is profile_type = 'CONFIGATR'

2nd block that acts like detail has to query where profile_type = 'CONFIGVAL' and profile = 'AD' ( as block has various profiles)  

Help is needed as I have to do this programatically - building a where clause
If user clicks on AD of the first block 3 records for AD has to be displayed. EO -1 record has to be displayed.

CREATE TABLE DSS.TAB1
(
  PROFILE_TYPE  VARCHAR2(10 BYTE)               NOT NULL,
  PROFILE       VARCHAR2(12 BYTE)               NOT NULL,
  DESCRIPTION   VARCHAR2(60 BYTE),
  ACTIVE        VARCHAR2(1 BYTE)                NOT NULL
)


SET DEFINE OFF;
Insert into DSS.TAB1
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE)
 Values
   ('CONFIGATR', 'AD', ' Config Attr AD', 'Y');
Insert into DSS.TAB1
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE)
 Values
   ('CONFIGATR', 'EO', ' Config Attr EO', 'Y');
Insert into DSS.TAB1
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE)
 Values
   ('CONFIGATR', 'MOD', ' Config Attr MOD', 'Y');
Insert into DSS.TAB1
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE)
 Values
   ('CONFIGATR', 'REV', ' Config Attr REV', 'Y');
Insert into DSS.TAB1
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE)
 Values
   ('CONFIGATR', 'SB', ' Config Attr SB', 'Y');
Insert into DSS.TAB1
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE)
 Values
   ('CONFIGATR', 'SIL', ' Config Attr SIL', 'Y');
COMMIT;

Open in new window


CREATE TABLE DSS.TAB2
(
  PROFILE_TYPE       VARCHAR2(10 BYTE)          NOT NULL,
  PROFILE            VARCHAR2(12 BYTE)          NOT NULL,
  DESCRIPTION        VARCHAR2(60 BYTE),
  ACTIVE             VARCHAR2(1 BYTE)           NOT NULL,
  PROFILE_ATTRIBUTE  VARCHAR2(60 BYTE)
)


SET DEFINE OFF;
Insert into DSS.TAB2
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE, PROFILE_ATTRIBUTE)
 Values
   ('CONFIGVAL', '100', 'Config Val M for AD', 'Y', 'AD');
Insert into DSS.TAB2
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE, PROFILE_ATTRIBUTE)
 Values
   ('CONFIGVAL', '101', 'Config Val M for AD', 'Y', 'AD');
Insert into DSS.TAB2
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE, PROFILE_ATTRIBUTE)
 Values
   ('CONFIGVAL', '102', 'Config Val M for AD', 'Y', 'AD');
Insert into DSS.TAB2
   (PROFILE_TYPE, PROFILE, DESCRIPTION, ACTIVE, PROFILE_ATTRIBUTE)
 Values
   ('CONFIGVAL', '200', 'Config Val O for EO', 'Y', 'EO');
COMMIT;

Open in new window

form-screen.jpg
Comment
Watch Question

Author

Commented:
for 2nd block where profile_type= 'CONFIGVAL', here profile_attribute = profile from 1st block.
Helena Markováprogrammer-analyst

Commented:
Why don't you create master-detail relation based on TAB1.PROFILE(block 1) and TAB2.PROFILE_ATTRIBUTE(block2) ?
Commented:
where clause on the 2nd block

profile_type = 'CONFIGVAL' and profile_attribute = :profiles1.profile

This works