AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of anumoses
anumoses
Flag for United States of America asked on

oracle query help

DROP TABLE TAB1 ;

CREATE TABLE TAB1
(
  ID            NUMBER(38),
  NAME          VARCHAR2(20 BYTE),
  ACTIVE        VARCHAR2(1 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE),
  DT_CREATED    DATE,
  NARRATIVE     VARCHAR2(4000 BYTE),
  MV_FOLDER_ID  NUMBER(8),
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE);


Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031497315, 'TESTING NEW PROFILE', 'A', 'ADWARAM', TO_DATE('10/5/2016 8:54:13 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
COMMIT;


DROP TABLE TAB2 ;

CREATE TABLE TAB2
(
  ID                        NUMBER(38),
  EXCLUDE                   VARCHAR2(1 BYTE),
  PROFILE_TYPE              VARCHAR2(50 BYTE),
  SPROF_ID                  NUMBER(38),
  USR_CREATED               VARCHAR2(32 BYTE),
  DT_CREATED                DATE,
  PROFS_ID                  NUMBER(38),
  ORG_ID                    NUMBER(38),
  VEND_ID                   NUMBER(38),
  USR_MODIFIED              VARCHAR2(32 BYTE),
  DT_MODIFIED               DATE,
  TRACE_ID                  NUMBER(38),
  ORG_ID_MFG                NUMBER(38),
  HOURS_REMAINING_PERCENT   NUMBER(3),
  CYCLES_REMAINING_PERCENT  NUMBER(3),
  DAYS_REMAINING_PERCENT    NUMBER(3),
  SHELF_LIFE_PERCENT        NUMBER(3),
  SPARTS_ID                 NUMBER(38)
);


Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031497316, 'N', 'CYCLES REMAINING PERCENT', 12031497315, 'ADWARAM', 
    TO_DATE('10/5/2016 8:54:27 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, 100, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498664, 'N', 'MANUFACTURER', 12031497315, 'ADWARAM', 
    TO_DATE('10/6/2016 12:53:07 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 30046, NULL, NULL, 
    NULL, NULL, 12031498661);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498666, 'N', 'SHELF LIFE REMAINING', 12031497315, 'ADWARAM', 
    TO_DATE('10/6/2016 12:53:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, 100, 12031498661);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498667, 'N', 'CYCLES REMAINING PERCENT', 12031497315, 'ADWARAM', 
    TO_DATE('10/6/2016 12:53:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, 100, 
    NULL, NULL, 12031498661);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498660, 'N', 'MANUFACTURER', 12031497315, 'ADWARAM', 
    TO_DATE('10/6/2016 12:51:48 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 131951, NULL, NULL, 
    NULL, NULL, NULL);
COMMIT;

DROP TABLE TAB3 ;

CREATE TABLE TAB3
(
  ID            NUMBER(38),
  PSPEC_ID      NUMBER(38),
  SPROF_ID      NUMBER(38),
  KEYWORD       VARCHAR2(10 BYTE),
  ACTIVE        VARCHAR2(1 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE),
  DT_CREATED    DATE,
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE
);


Insert into TAB3
   (ID, PSPEC_ID, SPROF_ID, KEYWORD, ACTIVE, 
    USR_CREATED, DT_CREATED, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031498661, 313312, 12031497315, 'A/C HDWR', 'A', 
    'ADWARAM', TO_DATE('10/6/2016 12:52:39 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL);
COMMIT;

DROP TABLE TAB4 ;

CREATE TABLE TAB4
(
  ID            NUMBER(38),
  SPARTS_ID     NUMBER(38),
  TAIL          VARCHAR2(25 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE),
  DT_CREATED    DATE,
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE
);


Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031498662, 12031498661, 'TAIL1', 'ADWARAM', TO_DATE('10/6/2016 12:52:52 PM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031498663, 12031498661, 'TAIL2', 'ADWARAM', TO_DATE('10/6/2016 12:52:52 PM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
COMMIT;

Open in new window


Included sample table creation and data.

Tab 4 is the tails table

tab3 is parts table

tab2 is items table

tab1 is profiles table

Tails will over ride all the profiles.

1. Item Parts with tails
2. item parts with no tails and does not exist in (1)
3. Items that do not exist in (1) and (2)

For 1.

query is 

select distinct spi.id,profile_type
  from  tab3 spp
      , tab2  spi
  where spp.sprof_id = spi.sprof_id
    and spp.sprof_id = 12031497315
    and sparts_id is not null

Open in new window



ID                                Profile Type

12031498667   CYCLES REMAINING PERCENT
12031498664   MANUFACTURER
12031498666   SHELF LIFE REMAINING

query 3 I should get only data for Manufacturer as cycles remaining is there is query 1. Tails query over rides all.

query 2 all parts with no tails and not in query 1

Help appreciated.

I want one query with union.
Oracle DatabaseDatabases
Avatar of undefined
Last Comment
Vitor Montalvão
8/22/2022 - Mon
anumoses
ASKER
Query 2 I have no data right now but its all parts with items no tails and not in query 1
ASKER CERTIFIED SOLUTION
Vitor Montalvão
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
anumoses
ASKER
Tab4 overrides all the others

SELECT DISTINCT TAB2.SPARTS_ID, TAB2.ID, TAB2.PROFILE_TYPE
FROM TAB2
      INNER JOIN TAB3 ON TAB2.SPARTS_ID = TAB3.ID
      INNER JOIN TAB4 ON TAB2.SPARTS_ID = TAB4.SPARTS_ID

Perfect and I am getting all the data required.
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (7633362777, 'KC10 OAWR 4C1 RESTRI', 'A', 'SCERKA', TO_DATE('9/17/2011 9:34:36 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (12030905719, 'TEST PROFILE', 'A', 'DRUSH', TO_DATE('9/23/2016 9:07:14 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031497315, 'TESTING NEW PROFILE', 'A', 'ADWARAM', TO_DATE('10/5/2016 8:54:13 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
COMMIT;



Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031495891, 'N', 'SHELF LIFE REMAINING', 7633362777, 'ADWARAM', 
    TO_DATE('9/30/2016 12:15:27 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, 100, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498664, 'N', 'MANUFACTURER', 12031497315, 'ADWARAM', 
    TO_DATE('10/6/2016 12:53:07 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 30046, NULL, NULL, 
    NULL, NULL, 12031498661);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498666, 'N', 'SHELF LIFE REMAINING', 12031497315, 'ADWARAM', 
    TO_DATE('10/6/2016 12:53:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, 100, 12031498661);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498667, 'N', 'CYCLES REMAINING PERCENT', 12031497315, 'ADWARAM', 
    TO_DATE('10/6/2016 12:53:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, 100, 
    NULL, NULL, 12031498661);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498858, 'N', 'HOURS REMAINING PERCENT', 12031497315, 'ADWARAM', 
    TO_DATE('10/7/2016 6:11:14 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, 80, NULL, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12030905720, 'Y', 'MANUFACTURER', 12030905719, 'DRUSH', 
    TO_DATE('9/23/2016 9:07:40 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, 'DRUSH', 
    TO_DATE('9/23/2016 9:44:16 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 31587, NULL, NULL, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498859, 'N', 'MANUFACTURER', 12031497315, 'ADWARAM', 
    TO_DATE('10/7/2016 6:16:08 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 30046, NULL, NULL, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498860, 'N', 'SHELF LIFE REMAINING', 12031497315, 'ADWARAM', 
    TO_DATE('10/7/2016 6:19:28 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, 100, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031498862, 'N', 'DAYS REMAINING PERCENT', 12031497315, 'ADWARAM', 
    TO_DATE('10/7/2016 6:30:37 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    100, NULL, 12031498861);
COMMIT;


Insert into TAB3
   (ID, PSPEC_ID, SPROF_ID, KEYWORD, ACTIVE, 
    USR_CREATED, DT_CREATED, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031498661, 313312, 12031497315, 'A/C HDWR', 'A', 
    'ADWARAM', TO_DATE('10/6/2016 12:52:39 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL);
Insert into TAB3
   (ID, PSPEC_ID, SPROF_ID, KEYWORD, ACTIVE, 
    USR_CREATED, DT_CREATED, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031498861, 7249537, 12031497315, 'SCREW', 'A', 
    'ADWARAM', TO_DATE('10/7/2016 6:30:07 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL);
COMMIT;


Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031498662, 12031498661, 'TAIL1', 'ADWARAM', TO_DATE('10/6/2016 12:52:52 PM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031498663, 12031498661, 'TAIL2', 'ADWARAM', TO_DATE('10/6/2016 12:52:52 PM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
COMMIT;

Open in new window


Your first query perfect I will get
SHELF LIFE REMAINING
CYCLES REMAINING PERCENT
MANUFACTURER

As per the data I gave,

2nd query I should get only

DAYS REMAINING PERCENT

3rd query I have to get

HOURS REMAINING PERCENT

2nd query data not in 1

3rd query data not in 1 and 2
anumoses
ASKER
SELECT TAB2.ID, TAB2.PROFILE_TYPE
FROM TAB2
WHERE TAB2.SPARTS_ID IS NOT NULL AND NOT EXISTS (SELECT 1
            FROM TAB4
            WHERE TAB2.SPARTS_ID = TAB4.SPARTS_ID)

FOr 2nd query I added oone condition where sparts_id is not null. I got the data


Only 3rd query I am struggling
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Vitor Montalvão
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
anumoses
ASKER
thanks
SOLUTION
Vitor Montalvão
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
anumoses
ASKER
PERFECT!!!!!!!
anumoses
ASKER
On the contrary I am not getting correct results if I have different sprof_id in the table. Any clue?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
anumoses
ASKER
select distinct spi.profile_type,exclude
from tab2 spi
where spi.profile_type not in 
             (select spi.profile_type
				from tab2 spi
					inner join tab3 spp 
					   on spi.sparts_id = spp.id
					inner join tab4 sppt 
					   on spi.sparts_id = sppt.sparts_id 
			union	
			  select spi.profile_type
				from tab2 spi
				where spi.sparts_id is not null 
				  and not exists (select 1
						            from tab4 sppt 
						           where spi.sparts_id = sppt.sparts_id) 
  and spi.sprof_id = 12031757458 and spi.sparts_id is null )
  group by profile_type
              ,exclude;

Open in new window


will send sample data
anumoses
ASKER
CREATE TABLE TAB1
(
  ID            NUMBER(38),
  NAME          VARCHAR2(20 BYTE),
  ACTIVE        VARCHAR2(1 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE),
  DT_CREATED    DATE,
  NARRATIVE     VARCHAR2(4000 BYTE),
  MV_FOLDER_ID  NUMBER(8),
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE
)


SET DEFINE OFF;
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (7633362777, 'KC10 OAWR 4C1 RESTRI', 'A', 'SCERKA', TO_DATE('9/17/2011 9:34:36 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031757458, 'DR TEST', 'A', 'DRUSH', TO_DATE('10/11/2016 9:40:11 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (12030905719, 'TEST PROFILE', 'A', 'DRUSH', TO_DATE('9/23/2016 9:07:14 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031497315, 'TESTING NEW PROFILE', 'A', 'ADWARAM', TO_DATE('10/5/2016 8:54:13 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
COMMIT;

Open in new window


CREATE TABLE TAB2
(
  ID                        NUMBER(38),
  EXCLUDE                   VARCHAR2(1 BYTE),
  PROFILE_TYPE              VARCHAR2(50 BYTE),
  SPROF_ID                  NUMBER(38),
  USR_CREATED               VARCHAR2(32 BYTE),
  DT_CREATED                DATE,
  PROFS_ID                  NUMBER(38),
  ORG_ID                    NUMBER(38),
  VEND_ID                   NUMBER(38),
  USR_MODIFIED              VARCHAR2(32 BYTE),
  DT_MODIFIED               DATE,
  TRACE_ID                  NUMBER(38),
  ORG_ID_MFG                NUMBER(38),
  HOURS_REMAINING_PERCENT   NUMBER(3),
  CYCLES_REMAINING_PERCENT  NUMBER(3),
  DAYS_REMAINING_PERCENT    NUMBER(3),
  SHELF_LIFE_PERCENT        NUMBER(3),
  SPARTS_ID                 NUMBER(38)
)

SET DEFINE OFF;
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031495891, 'N', 'SHELF LIFE REMAINING', 7633362777, 'ADWARAM', 
    TO_DATE('9/30/2016 12:15:27 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, 100, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031544375, 'N', 'HOURS REMAINING PERCENT', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:14:27 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, 80, NULL, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031554569, 'N', 'MANUFACTURER', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:30:44 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 30046, NULL, NULL, 
    NULL, NULL, 12031554564);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031554570, 'N', 'SHELF LIFE REMAINING', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:30:44 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, 90, 12031554564);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031544374, 'N', 'MANUFACTURER', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:14:07 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 30046, NULL, NULL, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031564744, 'N', 'DAYS REMAINING PERCENT', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:34:12 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    90, NULL, 12031564743);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031757459, 'Y', 'MANUFACTURER', 12031757458, 'DRUSH', 
    TO_DATE('10/11/2016 9:40:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 94639, NULL, NULL, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031554568, 'N', 'CYCLES REMAINING PERCENT', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:30:44 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, 100, 
    NULL, NULL, 12031554564);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12030905720, 'Y', 'MANUFACTURER', 12030905719, 'DRUSH', 
    TO_DATE('9/23/2016 9:07:40 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, 'DRUSH', 
    TO_DATE('9/23/2016 9:44:16 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 31587, NULL, NULL, 
    NULL, NULL, NULL);
COMMIT;

Open in new window


CREATE TABLE TAB3
(
  ID            NUMBER(38),
  PSPEC_ID      NUMBER(38),
  SPROF_ID      NUMBER(38),
  KEYWORD       VARCHAR2(10 BYTE),
  ACTIVE        VARCHAR2(1 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE),
  DT_CREATED    DATE,
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE
)




SET DEFINE OFF;
Insert into TAB3
   (ID, PSPEC_ID, SPROF_ID, KEYWORD, ACTIVE, 
    USR_CREATED, DT_CREATED, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031554564, 1387575, 7633362777, 'INDCTR', 'A', 
    'ADWARAM', TO_DATE('10/11/2016 8:29:58 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL);
Insert into TAB3
   (ID, PSPEC_ID, SPROF_ID, KEYWORD, ACTIVE, 
    USR_CREATED, DT_CREATED, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031564743, 847093, 7633362777, 'MAKER', 'A', 
    'ADWARAM', TO_DATE('10/11/2016 8:34:01 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL);
COMMIT;

Open in new window


CREATE TABLE TAB4
(
  ID            NUMBER(38),
  SPARTS_ID     NUMBER(38),
  TAIL          VARCHAR2(25 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE),
  DT_CREATED    DATE,
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE
)


SET DEFINE OFF;
Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031554565, 12031554564, 'TAIL1', 'ADWARAM', TO_DATE('10/11/2016 8:30:07 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031554566, 12031554564, 'TAIL2', 'ADWARAM', TO_DATE('10/11/2016 8:30:07 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
COMMIT;

Open in new window

anumoses
ASKER
Trying with sprof_id = 12031757458

Has no parts and tail

So I should only get 1 line data and the profile type has to be MANUFACTURER

But the query is giving me DAYS REMAINING PERCENT and HOURS REMAINING PERCENT
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
anumoses
ASKER
select distinct spi.profile_type,exclude
from tab2 spi
where spi.profile_type not in 
             (select spi.profile_type
				from tab2 spi 
					inner join tab3 spp 
					   on spi.sparts_id = spp.id and spp.sprof_id = 12031757458
					inner join tab4 sppt 
					   on spi.sparts_id = sppt.sparts_id 
			union	
			  select spi.profile_type
				from tab2 spi
				where spi.sparts_id is not null  and spi.sprof_id = 12031757458
				  and not exists (select 1
						            from tab4 sppt 
						           where spi.sparts_id = sppt.sparts_id)) 
  and spi.sprof_id = 12031757458  
  group by profile_type
              ,exclude;

Open in new window


I Think I got it
Vitor Montalvão
Sorry, different time zones. Just saw your comments now but from the last one it looks like you solved the issue by yourself. Is that right?
anumoses
ASKER
When I was adding the parameter I closed the cursor in the wrong place. Your answers are perfect.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão
Good.
If your issue has been solved please close this question by choosing the best comment(s) that helped you solving the issue.
Cheers
anumoses
ASKER
I still have an issue . I am loading sample data for you to help me
Vitor Montalvão
Ok but if is another issue you should open another question.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
anumoses
ASKER
I should get only one row data as per screen 3 , since tail over rides all the other manufacturer profile type.
sending data. 

CREATE TABLE TAB1
(
  ID            NUMBER(38),
  NAME          VARCHAR2(20 BYTE),
  ACTIVE        VARCHAR2(1 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE),
  DT_CREATED    DATE,
  NARRATIVE     VARCHAR2(4000 BYTE),
  MV_FOLDER_ID  NUMBER(8),
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE
)


Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (7633362777, 'KC10 OAWR 4C1 RESTRI', 'A', 'SCERKA', TO_DATE('9/17/2011 9:34:36 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031757458, 'DR TEST', 'A', 'DRUSH', TO_DATE('10/11/2016 9:40:11 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (12030905719, 'TEST PROFILE', 'A', 'DRUSH', TO_DATE('9/23/2016 9:07:14 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
Insert into TAB1
   (ID, NAME, ACTIVE, USR_CREATED, DT_CREATED, 
    NARRATIVE, MV_FOLDER_ID, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031497315, 'TESTING NEW PROFILE', 'A', 'ADWARAM', TO_DATE('10/5/2016 8:54:13 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL, NULL, NULL);
COMMIT;

Open in new window


CREATE TABLE TAB2
(
  ID                        NUMBER(38),
  EXCLUDE                   VARCHAR2(1 BYTE),
  PROFILE_TYPE              VARCHAR2(50 BYTE),
  SPROF_ID                  NUMBER(38),
  USR_CREATED               VARCHAR2(32 BYTE),
  DT_CREATED                DATE,
  PROFS_ID                  NUMBER(38),
  ORG_ID                    NUMBER(38),
  VEND_ID                   NUMBER(38),
  USR_MODIFIED              VARCHAR2(32 BYTE),
  DT_MODIFIED               DATE,
  TRACE_ID                  NUMBER(38),
  ORG_ID_MFG                NUMBER(38),
  HOURS_REMAINING_PERCENT   NUMBER(3),
  CYCLES_REMAINING_PERCENT  NUMBER(3),
  DAYS_REMAINING_PERCENT    NUMBER(3),
  SHELF_LIFE_PERCENT        NUMBER(3),
  SPARTS_ID                 NUMBER(38)
)



Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031495891, 'N', 'SHELF LIFE REMAINING', 7633362777, 'ADWARAM', 
    TO_DATE('9/30/2016 12:15:27 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, 100, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031544375, 'N', 'HOURS REMAINING PERCENT', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:14:27 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, 80, NULL, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031554569, 'N', 'MANUFACTURER', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:30:44 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 30046, NULL, NULL, 
    NULL, NULL, 12031554564);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031554570, 'N', 'SHELF LIFE REMAINING', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:30:44 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, 90, 12031554564);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031544374, 'N', 'MANUFACTURER', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:14:07 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 30046, NULL, NULL, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031564744, 'N', 'DAYS REMAINING PERCENT', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:34:12 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    90, NULL, 12031564743);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031757459, 'N', 'MANUFACTURER', 12031757458, 'DRUSH', 
    TO_DATE('10/11/2016 9:40:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, 'DRUSH', 
    TO_DATE('10/11/2016 12:15:17 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 94639, NULL, NULL, 
    NULL, NULL, NULL);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031554568, 'N', 'CYCLES REMAINING PERCENT', 7633362777, 'ADWARAM', 
    TO_DATE('10/11/2016 8:30:44 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, 100, 
    NULL, NULL, 12031554564);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031757569, 'Y', 'MANUFACTURER', 12031757458, 'DRUSH', 
    TO_DATE('10/11/2016 1:37:29 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, NULL, 32343, NULL, NULL, 
    NULL, NULL, 12031757552);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12031757572, 'N', 'MANUFACTURER', 12031757458, 'DRUSH', 
    TO_DATE('10/11/2016 1:38:50 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, 'DSS', 
    TO_DATE('10/12/2016 6:17:49 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 134076, NULL, NULL, 
    NULL, NULL, 12031757570);
Insert into TAB2
   (ID, EXCLUDE, PROFILE_TYPE, SPROF_ID, USR_CREATED, 
    DT_CREATED, PROFS_ID, ORG_ID, VEND_ID, USR_MODIFIED, 
    DT_MODIFIED, TRACE_ID, ORG_ID_MFG, HOURS_REMAINING_PERCENT, CYCLES_REMAINING_PERCENT, 
    DAYS_REMAINING_PERCENT, SHELF_LIFE_PERCENT, SPARTS_ID)
 Values
   (12030905720, 'Y', 'MANUFACTURER', 12030905719, 'DRUSH', 
    TO_DATE('9/23/2016 9:07:40 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, 'DRUSH', 
    TO_DATE('9/23/2016 9:44:16 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 31587, NULL, NULL, 
    NULL, NULL, NULL);
COMMIT;

Open in new window


CREATE TABLE TAB3
(
  ID            NUMBER(38),
  PSPEC_ID      NUMBER(38),
  SPROF_ID      NUMBER(38),
  KEYWORD       VARCHAR2(10 BYTE),
  ACTIVE        VARCHAR2(1 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE),
  DT_CREATED    DATE,
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE
)



Insert into TAB3
   (ID, PSPEC_ID, SPROF_ID, KEYWORD, ACTIVE, 
    USR_CREATED, DT_CREATED, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031757552, 1162466, 12031757458, 'DISK T4', 'A', 
    'DRUSH', TO_DATE('10/11/2016 1:03:45 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL);
Insert into TAB3
   (ID, PSPEC_ID, SPROF_ID, KEYWORD, ACTIVE, 
    USR_CREATED, DT_CREATED, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031554564, 1387575, 7633362777, 'INDCTR', 'A', 
    'ADWARAM', TO_DATE('10/11/2016 8:29:58 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL);
Insert into TAB3
   (ID, PSPEC_ID, SPROF_ID, KEYWORD, ACTIVE, 
    USR_CREATED, DT_CREATED, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031564743, 847093, 7633362777, 'MAKER', 'A', 
    'ADWARAM', TO_DATE('10/11/2016 8:34:01 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL);
Insert into TAB3
   (ID, PSPEC_ID, SPROF_ID, KEYWORD, ACTIVE, 
    USR_CREATED, DT_CREATED, USR_MODIFIED, DT_MODIFIED)
 Values
   (12031757570, 1162466, 12031757458, 'DISK T4', 'A', 
    'DRUSH', TO_DATE('10/11/2016 1:38:31 PM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL);
COMMIT;

Open in new window


CREATE TABLE TAB4
(
  ID            NUMBER(38),
  SPARTS_ID     NUMBER(38),
  TAIL          VARCHAR2(25 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE),
  DT_CREATED    DATE,
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE
)


Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031554565, 12031554564, 'TAIL1', 'ADWARAM', TO_DATE('10/11/2016 8:30:07 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031554566, 12031554564, 'TAIL2', 'ADWARAM', TO_DATE('10/11/2016 8:30:07 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031757571, 12031757570, 'XXYYZZ', 'DRUSH', TO_DATE('10/11/2016 1:38:31 PM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
COMMIT;

Open in new window

anumoses
ASKER
Not another issue. Its data issue
anumoses
ASKER
Not able to send code
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
anumoses
ASKER
I am not able to send code here . Its hanging. Tried all browser. I was trying the code with sprof_id = 12031757458 and Tail = XXYYZZ and pspec_id = 1162466
anumoses
ASKER
I should get

MANUFACTURER   N

but getting 2 rows

MANUFACTURER    N
MANUFACTURER    Y
anumoses
ASKER
Profile type may be the same but tail data overrides all profile types. So I have to get 1 line
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
anumoses
ASKER
If it seems like a new question I can still have a new question and close this with best comments. Please let me know.
Vitor Montalvão
If you want to attract more Experts it's always better to open a new one. Usually Experts won't participate in questions with multiple interactions.
anumoses
ASKER
thanks
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
anumoses
ASKER
Vitor Montalvão
I will follow the new question.
Cheers
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent