anumoses
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;
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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;
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PERFECT!!!!!!!
ASKER
On the contrary I am not getting correct results if I have different sprof_id in the table. Any clue?
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;
will send sample data
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;
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;
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;
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;
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
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
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;
I Think I got it
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?
ASKER
When I was adding the parameter I closed the cursor in the wrong place. Your answers are perfect.
Good.
If your issue has been solved please close this question by choosing the best comment(s) that helped you solving the issue.
Cheers
If your issue has been solved please close this question by choosing the best comment(s) that helped you solving the issue.
Cheers
ASKER
I still have an issue . I am loading sample data for you to help me
Ok but if is another issue you should open another question.
ASKER
I should get only one row data as per screen 3 , since tail over rides all the other manufacturer profile type.
sending data.
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;
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;
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;
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;
ASKER
Not another issue. Its data issue
ASKER
Not able to send code
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
ASKER
I should get
MANUFACTURER N
but getting 2 rows
MANUFACTURER N
MANUFACTURER Y
MANUFACTURER N
but getting 2 rows
MANUFACTURER N
MANUFACTURER Y
ASKER
Profile type may be the same but tail data overrides all profile types. So I have to get 1 line
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.
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.
ASKER
thanks
ASKER
I will follow the new question.
Cheers
Cheers
ASKER