-
query not working
Hi All,
I need to run a query that will return only the inc_num of the rows that have P1. If the inc_num has P1 and P2 then I don't want that inc_num. Here is my query, where is it wrong, it keeps returning all inc_nums even if they have more than P1 and P3, etc.
Select distinct inc_num, ename, lname
From lust, ust, elf, subst
Where elf.inc_num = lust.inc_num
And lust.id = ust.id
And ust.subst_code = subst.subst_code
and ust.subst_code not in
(select ust.subst_code
from ust
where subst_code in ('H','H1','H2', 'P', 'P2', 'P3', 'P4', 'P5', 'UN'))
Thanks in Advance.
Kathy
-
This may not be the optimum way, but ...
Code:
Select Distinct
Max(inc_num),
ename,
lname
From
lust,
ust,
elf,
subst
Where
elf.inc_num = lust.inc_num And
lust.id = ust.id And
ust.subst_code = subst.subst_code
Group By
ename,
lname
Having
Count(Distinct inc_num) = 1 And
Max(inc_num) = 'P1'
... or ...
Code:
Select
inc_num,
ename,
lname
From
(
Select Distinct
inc_num,
ename,
lname,
Count(Distinct inc_num) Over (Partition By ename,lname) inc_nums
From
lust,
ust,
elf,
subst
Where
elf.inc_num = lust.inc_num And
lust.id = ust.id And
ust.subst_code = subst.subst_code
)
Where
inc_nums = 1 And
inc_num = 'P1'
There's other ways as well, of course.
-
Thanks for the help,
I couldn't get either of these too work. Keep getting errors when trying to replace table_name, etc.
Can you take another look. Here are the tables. I need to:
Select lust_site.incident_number,
elf_submission.submit_number,
elf_submission.amt_requested,
elf_submission.amt_approved,
substance.stubstance_desc
(Only where the substance code = 'P1', if the specific incident_number exists with other codes besides 'P1', such as 'P2', 'P3' then I want to eliminate that incident_number from the result set.
lust_site
INCIDENT_NUMBER NOT NULL VARCHAR2(9)
UST_FACILITY_ID NUMBER(10)
ust_tank
UST_FACILITY_ID NOT NULL NUMBER(10)
SUBSTANCE_CODE VARCHAR2(2)
elf_submission
INCIDENT_NUMBER NOT NULL VARCHAR2(9)
SUBMIT_NUMBER NOT NULL NUMBER(6)
RECEIVED_DATE DATE
AMT_REQUESTED NUMBER(16,2)
AMT_APPROVED NUMBER(16,2)
substance
SUBSTANCE_CODE NOT NULL VARCHAR2(2)
SUBSTANCE_DESC VARCHAR2(15)
SORT_ORDER NUMBER(6)
-
I was wondering. Would a query like this be considered a
easy >> med >> difficult skill level for a developer?
thx,
Kathy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|