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. query 1 data query 3
LVL 6
anumosesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
In my opinion you only need 2 queries (2 and 3 are the same):

Query 1
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

Open in new window

     
Query2
SELECT TAB2.ID, TAB2.PROFILE_TYPE
FROM TAB2
WHERE NOT EXISTS (SELECT 1
		FROM TAB4 
		WHERE TAB2.SPARTS_ID = TAB4.SPARTS_ID)

Open in new window

0
 
anumosesAuthor Commented:
Query 2 I have no data right now but its all parts with items no tails and not in query 1
0
 
anumosesAuthor Commented:
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
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
anumosesAuthor Commented:
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
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Yes, I also got that part:
SELECT TAB2.SPARTS_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)

Open in new window

Working on the 3rd query.
0
 
anumosesAuthor Commented:
thanks
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Easy way is to use previous solutions and except from there:
SELECT DISTINCT TAB2.ID, TAB2.PROFILE_TYPE
FROM TAB2
WHERE TAB2.PROFILE_TYPE NOT IN (SELECT TAB2.PROFILE_TYPE
				FROM TAB2
					INNER JOIN TAB3 ON TAB2.SPARTS_ID = TAB3.ID
					INNER JOIN TAB4 ON TAB2.SPARTS_ID = TAB4.SPARTS_ID
				UNION	
				SELECT 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))

Open in new window

0
 
anumosesAuthor Commented:
PERFECT!!!!!!!
0
 
anumosesAuthor Commented:
On the contrary I am not getting correct results if I have different sprof_id in the table. Any clue?
0
 
anumosesAuthor Commented:
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
0
 
anumosesAuthor Commented:
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

0
 
anumosesAuthor Commented:
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
0
 
anumosesAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
 
anumosesAuthor Commented:
When I was adding the parameter I closed the cursor in the wrong place. Your answers are perfect.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good.
If your issue has been solved please close this question by choosing the best comment(s) that helped you solving the issue.
Cheers
0
 
anumosesAuthor Commented:
I still have an issue . I am loading sample data for you to help me
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok but if is another issue you should open another question.
0
 
anumosesAuthor Commented:
screen 1screen 2screen 3
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

0
 
anumosesAuthor Commented:
Not another issue. Its data issue
0
 
anumosesAuthor Commented:
Not able to send code
0
 
anumosesAuthor Commented:
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
0
 
anumosesAuthor Commented:
I should get

MANUFACTURER   N

but getting 2 rows

MANUFACTURER    N
MANUFACTURER    Y
0
 
anumosesAuthor Commented:
Profile type may be the same but tail data overrides all profile types. So I have to get 1 line
0
 
anumosesAuthor Commented:
If it seems like a new question I can still have a new question and close this with best comments. Please let me know.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
anumosesAuthor Commented:
thanks
0
 
anumosesAuthor Commented:
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I will follow the new question.
Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.