• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

oracle query help

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
anumoses
Asked:
anumoses
  • 26
  • 10
3 Solutions
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
Not able to send my testing code.
0
 
anumosesAuthor Commented:
Is there a way to send the testing code. Not able to attach code here
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
anumosesAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
anumosesAuthor Commented:
YES correct
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
anumosesAuthor Commented:
What will the full union now?
0
 
anumosesAuthor Commented:
For the full union  query I need to get this line
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
User is running query not passing the tail. SO I am getting error
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:

 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
 
anumosesAuthor Commented:
Added another question.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What about this one?
0
 
anumosesAuthor Commented:
that is still being tested
0
 
anumosesAuthor Commented:
having issues entering question. SO with support
will update soon as a get the question. I need urgent help
0
 
anumosesAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
anumosesAuthor Commented:
yes
0
 
anumosesAuthor Commented:
1st query is fine in the union

from tail

2nd query in union has to ignore profile type picked by tail
0
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
added a line to tab2

and it has to ignore MANUFACTURER and take TRACE Profile Type
data.jpg
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you support your comments with sample data?
It's more easy for me to understand with pictures :)
0
 
anumosesAuthor Commented:
I sent the screen print as I am not able to add code here
TRACE
0
 
anumosesAuthor Commented:
data
0
 
anumosesAuthor Commented:
sending text file
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I just need to know what information do you want to be returned by the queries.
0
 
anumosesAuthor Commented:
ist query records with tail table tab4

2nd query from parts (tab3) not in the 1st query
0
 
anumosesAuthor Commented:
Thanks

For more I will post new question
0
 
anumosesAuthor Commented:
asking a new question with sample data. Need help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 26
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now