Solved

oracle query help

Posted on 2016-10-13
18
77 Views
Last Modified: 2016-10-31
select distinct spi.profile_type,exclude
  from tab2 spi, 
	   tab3 spp,
	   tab4 sppt 
 where spi.sparts_id = spp.id
  and spi.sparts_id = sppt.sparts_id 
  and spi.sprof_id = 12031497315
  and spp.pspec_id = 313312
  and sppt.tail = 'TEST TAIL'     
 --2  
union    
select spi.profile_type,exclude
  from tab2 spi, 
	   tab3 spp 
 where spi.sparts_id = spp.id
   and spi.sprof_id = 12031497315
  and spp.pspec_id = 313312
   and not exists (select 1
				     from tab4 sppt
				    where sppt.sparts_id = spi.sparts_id 
	                 )
   and not exists (select 1
                     from tab2 spi, 
	                      tab3 spp,
	                      tab4 sppt 
                    where spi.sparts_id = spp.id
                      and spi.sparts_id = sppt.sparts_id 
                      and spi.sprof_id = 12031497315
                      and spp.pspec_id = 313312)                           		     
union
--3
select spi.profile_type,exclude
  from tab2 spi
 where spi.sparts_id is null 
   and spi.sprof_id =  12031497315
   and not exists 
             (select 1
				from tab2 spi, 
	                 tab3 spp 
	           where spi.sparts_id = spp.id
				 and spi.sprof_id = 12031497315
                 and spp.pspec_id = 313312)
  and not exists (select 1
				     from tab4 sppt
				    where sppt.sparts_id = spi.sparts_id )
  and not exists (select 1 from 
                   tab2 spi, 
	               tab3 spp,
	               tab4 sppt 
                where spi.sparts_id = spp.id
                  and spi.sparts_id = sppt.sparts_id 
                  and spi.sprof_id = 12031497315
                  and spp.pspec_id = 313312
                  and sppt.tail = 'TEST TAIL')
  group by profile_type
              ,exclude; 

Open in new window

0
Comment
Question by:anumoses
  • 12
  • 5
18 Comments
 
LVL 6

Author Comment

by:anumoses
ID: 41842453
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;

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
   (12031768558, 'N', 'TRACE TYPE', 12031497315, 'ADWARAM', 
    TO_DATE('10/13/2016 11:40:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7091, NULL, NULL, NULL, 
    NULL, NULL, NULL, 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
   (12031768559, 'N', 'TRACE', 12031497315, 'ADWARAM', 
    TO_DATE('10/13/2016 11:40:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, 4658, NULL, 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
   (12031768560, 'N', 'SPEC CODE', 12031497315, 'ADWARAM', 
    TO_DATE('10/13/2016 11:40:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), 5167, NULL, NULL, NULL, 
    NULL, NULL, NULL, 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
   (12031768561, 'N', 'PO VENDOR', 12031497315, 'ADWARAM', 
    TO_DATE('10/13/2016 11:40:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, 9895708365, NULL, 
    NULL, NULL, NULL, 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
   (12031768562, 'N', 'PART CAT', 12031497315, 'ADWARAM', 
    TO_DATE('10/13/2016 11:40:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), 6372, NULL, NULL, NULL, 
    NULL, NULL, NULL, 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
   (12031768563, 'N', 'OH VENDOR', 12031497315, 'ADWARAM', 
    TO_DATE('10/13/2016 11:40:30 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, 10560365254, NULL, 
    NULL, NULL, NULL, 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
   (12031768567, 'Y', 'TRACE', 12031497315, 'ADWARAM', 
    TO_DATE('10/13/2016 11:44:07 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, 4658, NULL, NULL, NULL, 
    NULL, NULL, 12031768565);
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
   (12031768565, 313312, 12031497315, 'A/C HDWR', 'A', 
    'ADWARAM', TO_DATE('10/13/2016 11:43:29 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
)


Insert into TAB4
   (ID, SPARTS_ID, TAIL, USR_CREATED, DT_CREATED, 
    USR_MODIFIED, DT_MODIFIED)
 Values
   (12031768566, 12031768565, 'TEST TAIL', 'ADWARAM', TO_DATE('10/13/2016 11:43:36 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
    NULL, NULL);
COMMIT;

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 41842458
Requirement

query 1- Tail data- have 1 record. I should get that (Trace) id -12031768567

query 2 no data

query 3 - all data except TRACE. As TRACE is in tail. (TRACE Profile Type)

So Data that I should get is

data
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842465
My query only gives 1 line data it TRACE from TAIL data (tab4)

tab1 - profiles
tab2- items
tab3 parts
tab4 tail
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 41842599
That is because in your "query 3", both the first and the last "not exists..." sub-queries cause all rows to be excluded.

I haven't taken the time to study why this is true, but I suspect that you need to add another condition (or two?)  to the first and third sub-queries in your "query 3" (after the second "union")  to make sure that these do not include all rows.  Because, if these sub-queries return all rows, then your "not exists" check will cause all rows to be excluded from the outer query.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41843454
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
)

LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;


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);
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
   (12031758780, 'N', 'TRACE', 12031757458, 'DRUSH', 
    TO_DATE('10/13/2016 8:01:50 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, NULL, NULL, NULL, 
    NULL, 4019, NULL, 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
   (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
   (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);
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
   (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
   (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


I added another profile. And data that I should see

select distinct spi.profile_type,exclude
  from tab2 spi, 
	   tab3 spp,
	   tab4 sppt 
 where spi.sparts_id = spp.id
  and spi.sparts_id = sppt.sparts_id 
  and spi.sprof_id = 12031757458
  and spp.pspec_id = 1162466
  and sppt.tail = null      
 --2  
union    
select spi.profile_type,exclude
  from tab2 spi, 
	   tab3 spp 
 where spi.sparts_id = spp.id
   and spi.sprof_id = 12031757458
   and spp.pspec_id = 1162466
   and not exists (select 1
				     from tab4 sppt
				    where sppt.sparts_id = spi.sparts_id 
	                )
   and not exists (select 1
                     from tab2 spi, 
	                      tab3 spp,
	                      tab4 sppt 
                    where spi.sparts_id = spp.id
                      and spi.sparts_id = sppt.sparts_id 
                      and spi.sprof_id = 12031757458
                      and spp.pspec_id = 1162466
                      and sppt.tail = null)                                 		     
union
--3
select spi.profile_type,exclude
  from tab2 spi
 where spi.sparts_id is null 
   and spi.sprof_id = 12031757458
   and not exists 
             (select 1
				from tab2 spi, 
	                 tab3 spp 
	           where spi.sparts_id = spp.id
				 and spi.sprof_id = 12031757458
				 and spp.pspec_id = 1162466)
  and not exists (select 1
				     from tab4 sppt
				    where sppt.sparts_id = spi.sparts_id )
  and not exists (select 1 from 
                   tab2 spi, 
	               tab3 spp,
	               tab4 sppt 
                where spi.sparts_id = spp.id
                  and spi.sparts_id = sppt.sparts_id 
                  and spi.sprof_id = 12031757458
                  and spp.pspec_id = 1162466
                  and sppt.tail = null )
  and spi.sprof_id = 12031757458
  group by profile_type
              ,exclude;  

Open in new window



If no tail is passed I should get
MANUFACTURER   Y
TRACE                      N

WHEN TAIL IS PASSED ('TEST TAIL' )
MANUFACTURER   N
0
 
LVL 6

Author Comment

by:anumoses
ID: 41843460
3 PARAMETERS PASSED BY USER

SPROF_ID
PSPEC_ID
TAIL

TAIL MAY BE NULL OR MAY NOT BE

BASED ON THIS I NEED TO GET DATA.

TRIED SO MANY QUERIES, BUT EVERYTIME SOMETHING IS FAILING IN GETTING THE CORRECT DATA.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 41843555
This condition will never work (or at least never be true) in Oracle queries:
"and sppt.tail = null"

You have to use a condition more like these:
"and sppt.tail like (nvl('&tail_parameter',sppt.tail)"
or
"and sppt.tail = (nvl('&tail_parameter',sppt.tail)"

Where the "&tail_parameter" is either an actual tail value, or null.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41843615
what will happen if instead of tail NULL is passed? Will this work? it means tails data does not exist in sppt.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 6

Author Comment

by:anumoses
ID: 41843642
select distinct spi.id,spi.profile_type,exclude
  from tab2 spi, 
	   tab3 spp,
	   tab4 sppt 
 where spi.sparts_id = spp.id
  and spi.sparts_id = sppt.sparts_id 
  and spi.sprof_id = 12031497315--12031757458
  and spp.pspec_id = 313312--1162466
  --and sppt.tail = 'TEST TAIL'--'XXYYZZ'     
  --and sppt.tail = (nvl(in_tail,sppt.tail)
  and sppt.tail = (nvl('TEST TAIL',sppt.tail))
 --2  
union    
select spi.id,spi.profile_type,exclude
  from tab2 spi, 
	   tab3 spp 
 where spi.sparts_id = spp.id
   and spi.sprof_id = 12031497315--12031757458
  and spp.pspec_id = 313312--1162466
     and sparts_id not in (select sparts_id
				     from tab4 sppt
				    where sppt.sparts_id = spi.sparts_id )
   and profile_type not in (select profile_type from 
                   tab2 spi, 
	               tab3 spp,
	               tab4 sppt 
                where spi.sparts_id = spp.id
                  and spi.sparts_id = sppt.sparts_id 
                  and spi.sprof_id = 12031497315--12031757458
                  and spp.pspec_id = 313312--1162466
                  --and sppt.tail = 'TEST TAIL')--'XXYYZZ')
                  and sppt.tail = (nvl('TEST TAIL',sppt.tail) ))                          		     
union
--3
select spi.id,spi.profile_type,exclude
  from tab2 spi
 where spi.sparts_id is null 
   and spi.sprof_id =  12031497315--12031757458
   and profile_type not in
             (select profile_type
				from tab2 spi, 
	                 tab3 spp 
	           where spi.sparts_id = spp.id
				 and spi.sprof_id = 12031497315--12031757458
                 and spp.pspec_id = 313312)--1162466)
  and sparts_id not in (select sparts_id
				     from tab4 sppt
				    where sppt.sparts_id = spi.sparts_id )
  and profile_type not in (select profile_type from 
                   tab2 spi, 
	               tab3 spp,
	               tab4 sppt 
                where spi.sparts_id = spp.id
                  and spi.sparts_id = sppt.sparts_id 
                  and spi.sprof_id = 12031497315--12031757458
                  and spp.pspec_id = 313312--1162466
                  --and sppt.tail = 'TEST TAIL')--'XXYYZZ')
                  and sppt.tail = (nvl('TEST TAIL',sppt.tail)))
  group by id,profile_type
              ,exclude;  

Open in new window


This is working for me.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41843652
select distinct spi.id,spi.profile_type,exclude
  from tab2 spi, 
	   tab3 spp,
	   tab4 sppt 
 where spi.sparts_id = spp.id
  and spi.sparts_id = sppt.sparts_id 
  and spi.sprof_id = 12031757458
  and spp.pspec_id = 1162466
  --and sppt.tail = 'TEST TAIL'--'XXYYZZ'     
  --and sppt.tail = (nvl(in_tail,sppt.tail)
  and sppt.tail = (nvl(NULL,sppt.tail))
 --2  
union    
select spi.id,spi.profile_type,exclude
  from tab2 spi, 
	   tab3 spp 
 where spi.sparts_id = spp.id
   and spi.sprof_id = 12031757458
  and spp.pspec_id = 1162466
     and sparts_id not in (select sparts_id
				     from tab4 sppt
				    where sppt.sparts_id = spi.sparts_id )
   and profile_type not in (select profile_type from 
                   tab2 spi, 
	               tab3 spp,
	               tab4 sppt 
                where spi.sparts_id = spp.id
                  and spi.sparts_id = sppt.sparts_id 
                  and spi.sprof_id = 12031757458
                  and spp.pspec_id = 1162466
                  --and sppt.tail = 'TEST TAIL')--'XXYYZZ')
                  and sppt.tail = (nvl(NULL,sppt.tail) ))                          		     
union
--3
select spi.id,spi.profile_type,exclude
  from tab2 spi
 where spi.sparts_id is null 
   and spi.sprof_id =  12031757458
   and profile_type not in
             (select profile_type
				from tab2 spi, 
	                 tab3 spp 
	           where spi.sparts_id = spp.id
				 and spi.sprof_id = 12031757458
                 and spp.pspec_id = 1162466)
  and sparts_id not in (select sparts_id
				     from tab4 sppt
				    where sppt.sparts_id = spi.sparts_id )
  and profile_type not in (select profile_type from 
                   tab2 spi, 
	               tab3 spp,
	               tab4 sppt 
                where spi.sparts_id = spp.id
                  and spi.sparts_id = sppt.sparts_id 
                  and spi.sprof_id = 12031757458
                  and spp.pspec_id = 1162466
                  --and sppt.tail = 'TEST TAIL')--'XXYYZZ')
                  and sppt.tail = (nvl(NULL,sppt.tail)))
  group by id,profile_type
              ,exclude;  

Open in new window


expected data
12031757569      MANUFACTURER  Y
12031758780      TRACE                     N

But I am getting

12031757572    MANUFACTURER  N
12031758780    TRACE                     N
0
 
LVL 6

Author Comment

by:anumoses
ID: 41843655
Sorry I think results are ok as tail overrides all.

12031757572    MANUFACTURER  N
12031758780    TRACE                     N
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 41843853
"what will happen if instead of tail NULL is passed? Will this work?
Yes, as long as your statement includes the "nvl" something like this:
and sppt.tail = nvl(in_tail,sppt.tail)

"it means tails data does not exist in sppt"
Be careful with this.  The "nvl" operator can handle a null (blank) value in a single column, but the row must exist.  If there is no matching row at all in a table, then "nvl" does not solve that problem.  Then you need an outer join.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41843891
With the sample data and code I sent, in the end, do you think it will handle it tail does not have a row?
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 41843928
I didn't take the time to test the data you posted today.  I may or may not get a chance to do that today.  Normally, no an "nvl" operator does not handle a "missing row" problem.  The "nvl" operator only handles the problem of blank data in a column, when the row does exist.  If what you have though is a case where sometimes a table has a record that matches, and sometimes the table does not have a matching record at all, you will likely need an outer join.  That is exactly what an outer join is: allow a table to be included in a multi-table query even if there is no matching record in that table, to allow the values from the other table(s) to still be returned.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41843946
when you find time can you please look into it and let me know.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 41864585
No, if the tail (sppt) table in your latest query does not have a matching row, I do not expect this query to return any results.

Why not?  This first part of the query:
select distinct spi.id,spi.profile_type,exclude
  from tab2 spi,
         tab3 spp,
         tab4 sppt
 where spi.sparts_id = spp.id
  and spi.sparts_id = sppt.sparts_id
  and spi.sprof_id = 12031757458
  and spp.pspec_id = 1162466
  --and sppt.tail = 'TEST TAIL'--'XXYYZZ'    
  --and sppt.tail = (nvl(in_tail,sppt.tail)
  and sppt.tail = (nvl(NULL,sppt.tail))
--
will definitely *NOT* return any rows if there is no record where these conditions are true:
  and spi.sparts_id = sppt.sparts_id
  and sppt.tail = (nvl(NULL,sppt.tail))

Note : the outer set of parenthesis in this last line make no difference. That line could simply be:
  and sppt.tail = nvl(NULL,sppt.tail)
and the results would be the same.

If you need this query to return a row even if there is no matching row in the sppt table, then you need to change those two lines to this (outer join) syntax:
  and spi.sparts_id = sppt.sparts_id (+)
  and nvl(sppt.tail,'x') = nvl(sppt.tail,'x')

Note: make sure that you choose a value for the nvl operator that does *NOT* exist in any valid rows in this table.  I chose a lower-case "x" here, but I don't know if that is ever a valid value in your database in this column, or not.  You may need to use something more like this:
  and nvl(sppt.tail,'xxyyzz') = nvl(sppt.tail,'xxyyzz')
That is a character string that is quite unlikely to occur in any English-language database.

The other two parts of your latest three-part "union" query are harder for me to predict whether they will return any rows or not, if there is no matching row in the sppt table.  Your "not in" sub-query will return an empty result set if there is no matching row.  Then the "profile_type not in" condition will likely evaluate to false for all rows, so I expect that the entire three-part, union query will return no rows then, when that condition (no matching row in the sppt table) is true.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 41866727
thanks
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
null value 15 62
how to fix this error 14 46
parse convert xml feed to text (python) 2 30
How to simplify my SQL statement? 10 13
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

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

9 Experts available now in Live!

Get 1:1 Help Now