Solved

oracle query help

Posted on 2016-10-12
36
91 Views
Last Modified: 2016-10-13
https://www.experts-exchange.com/questions/28974859/oracle-query-help.html

Additional help on the existing question

 I was trying the code with sprof_id = 12031757458 and Tail = XXYYZZ and pspec_id = 1162466

I should get

MANUFACTURER   N

but getting 2 rows

MANUFACTURER    N
MANUFACTURER    Y

Profile type may be the same but tail data overrides all profile types. So I have to get 1 line
0
Comment
Question by:anumoses
  • 26
  • 10
36 Comments
 
LVL 6

Author Comment

by:anumoses
ID: 41840020
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: 41840048
Not able to send my testing code.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840061
Is there a way to send the testing code. Not able to attach code here
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 6

Author Comment

by:anumoses
ID: 41840174
If the profiles are same then it has to take the profile type from the tails as it over rides all.

If profiles are different then it has to take all unique ones taking tails first and then from parts not in tails and then in items not in tails and parts.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840177
your query from the previous question worked because I had different profile types. Now the user is testing with the same profile type and its a true statement as it happens. So we have to take the profile type from the tails and ignore all others
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41840178
There's an Attach File option below the comment box.
You can also use the code block as you used for the CREATE and INSERT statements.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840188
upload is hanging. Some issues with this site. Can you run the queries from previous question
putting values for

sprof_id = 12031757458

 pspec_id = 1162466

tail = XXYYZZ
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41840211
Please confirm if the queries are the following:
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


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)


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: 41840217
YES correct
0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41840233
Ok, then I added the filter and got only one record (I just used the CREATE and INSERT statements from this question):
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
WHERE TAB2.sprof_id = 12031757458 and TAB4.Tail = 'XXYYZZ' and TAB3.pspec_id = 1162466

Open in new window

Returned:
SPARTS_ID                               ID                                      PROFILE_TYPE
--------------------------------------- --------------------------------------- --------------------------------------------------
12031757570                             12031757572                             MANUFACTURER

(1 row(s) affected)
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840289
What will the full union now?
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840291
For the full union  query I need to get this line
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41840311
For the full union  query I need to get this line
Then it's a new requirement because before you wanted everything that wasn't returned by the 2 previous queries, right? And that's why that line doesn't appear in this last query (check the NOT IN clause).
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840338
That is why I put a new question

The user is running query where no tail is being passed

select distinct spi.profile_type,exclude
from tab2 spi 
	inner join tab3 spp 
	       on spi.sparts_id = spp.id
	inner join tab4 sppt 
	      on spi.sparts_id = sppt.sparts_id 
where spi.sprof_id = in_sprof_id
and spp.pspec_id = in_pspec_id
and sppt.tail = in_tail	        
 --2  
union    
select spi.profile_type,exclude
				from tab2 spi
				where spi.sparts_id is not null 
				  and spi.sprof_id = in_sprof_id
				  and not exists (select 1
						            from tab2 spi 
	                               inner join tab3 spp 
	                                 on spi.sparts_id = spp.id
	                               inner join tab4 sppt 
	                                 on spi.sparts_id = sppt.sparts_id 
                                  where spi.sprof_id = in_sprof_id
                                    and spp.pspec_id = in_pspec_id
                                    and sppt.tail = in_tail	  )		     
union
--3
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 = in_sprof_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 spi.sprof_id = in_sprof_id
				  and not exists (select 1
						            from tab4 sppt 
						           where spi.sparts_id = sppt.sparts_id
						             and sppt.tail = in_tail)) 
  and spi.sprof_id = in_sprof_id
  group by profile_type
              ,exclude;  

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 41840341
I think it is more complex now

1. all profiles with tails
2. I think we have to have 2 not exists
    Not sure how to get this


3. not in tails and parts
0
 
LVL 6

Author Comment

by:anumoses
ID: 41840354
User is running query not passing the tail. SO I am getting error
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41840368
So your last query returns:
profile_type                                       exclude
-------------------------------------------------- -------
MANUFACTURER                                       N

(1 row(s) affected)

That's the same that is returned by 1st SELECT, meaning that 2nd and 3rd SELECT are returning nothing.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41840376

 1. all profiles with tails
 2. I think we have to have 2 not exists
     Not sure how to get this
 3. not in tails and parts
Better you give expected results so I can try to work on those queries.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41841986
Added another question.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41841993
What about this one?
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842000
that is still being tested
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842002
having issues entering question. SO with support
will update soon as a get the question. I need urgent help
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842057
Not able to add question due to some issues. Support is helping me. But how can I get that answered?
Is there a way to help me and I will enter the question later and close grading you with A
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41842061
Is there a way to help me and I will enter the question later and close grading you with A
Not really.
If issue is related with this question?
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842066
yes
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842067
1st query is fine in the union

from tail

2nd query in union has to ignore profile type picked by tail
0
 
LVL 6

Author Comment

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

Author Comment

by:anumoses
ID: 41842074
added a line to tab2

and it has to ignore MANUFACTURER and take TRACE Profile Type
data.jpg
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41842077
Can you support your comments with sample data?
It's more easy for me to understand with pictures :)
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842081
I sent the screen print as I am not able to add code here
TRACE
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842092
data
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842095
sending text file
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41842098
I just need to know what information do you want to be returned by the queries.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842100
ist query records with tail table tab4

2nd query from parts (tab3) not in the 1st query
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 41842331
Thanks

For more I will post new question
0
 
LVL 6

Author Comment

by:anumoses
ID: 41842432
asking a new question with sample data. Need help
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Fine Tune your automatic Updates for Ubuntu / Debian
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

856 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