Solved

oracle query help

Posted on 2016-10-06
29
62 Views
Last Modified: 2016-10-12
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
0
Comment
Question by:anumoses
  • 21
  • 8
29 Comments
 
LVL 6

Author Comment

by:anumoses
ID: 41832512
Query 2 I have no data right now but its all parts with items no tails and not in query 1
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41833534
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
 
LVL 6

Author Comment

by:anumoses
ID: 41833569
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
 
LVL 6

Author Comment

by:anumoses
ID: 41833597
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41833608
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
 
LVL 6

Author Comment

by:anumoses
ID: 41833609
thanks
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41833623
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
 
LVL 6

Author Comment

by:anumoses
ID: 41833633
PERFECT!!!!!!!
0
 
LVL 6

Author Comment

by:anumoses
ID: 41838679
On the contrary I am not getting correct results if I have different sprof_id in the table. Any clue?
0
 
LVL 6

Author Comment

by:anumoses
ID: 41838687
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
 
LVL 6

Author Comment

by:anumoses
ID: 41838700
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
 
LVL 6

Author Comment

by:anumoses
ID: 41838704
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
 
LVL 6

Author Comment

by:anumoses
ID: 41838774
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41839578
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 6

Author Comment

by:anumoses
ID: 41839892
When I was adding the parameter I closed the cursor in the wrong place. Your answers are perfect.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41839897
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
 
LVL 6

Author Comment

by:anumoses
ID: 41839915
I still have an issue . I am loading sample data for you to help me
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41839924
Ok but if is another issue you should open another question.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41839937
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
 
LVL 6

Author Comment

by:anumoses
ID: 41839938
Not another issue. Its data issue
0
 
LVL 6

Author Comment

by:anumoses
ID: 41839982
Not able to send code
0
 
LVL 6

Author Comment

by:anumoses
ID: 41839993
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
 
LVL 6

Author Comment

by:anumoses
ID: 41839995
I should get

MANUFACTURER   N

but getting 2 rows

MANUFACTURER    N
MANUFACTURER    Y
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840001
Profile type may be the same but tail data overrides all profile types. So I have to get 1 line
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840007
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41840021
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 41840022
thanks
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840025
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41840041
I will follow the new question.
Cheers
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
select query - oracle 16 81
Pfile and SPfile - Oracle 2 35
dates - loop 12 41
SQL anywhere 11 databases 1 29
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now