Solved

oracle query help

Posted on 2016-10-12
36
65 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
Comment Utility
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
Comment Utility
Not able to send my testing code.
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Is there a way to send the testing code. Not able to attach code here
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
YES correct
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
Comment Utility
What will the full union now?
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
For the full union  query I need to get this line
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
User is running query not passing the tail. SO I am getting error
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility

 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Author Comment

by:anumoses
Comment Utility
Added another question.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
What about this one?
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
that is still being tested
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
yes
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
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
Comment Utility
added a line to tab2

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

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
I sent the screen print as I am not able to add code here
TRACE
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
data
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
sending text file
0
 
LVL 45

Expert Comment

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

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
Thanks

For more I will post new question
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
asking a new question with sample data. Need help
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
People Counting Software 2 59
SQL Query - Oracle 10g - Subract date from next data row 4 37
sql query 5 52
linux ssh 4 26
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.

744 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

18 Experts available now in Live!

Get 1:1 Help Now