SQL Extract Data that single status alone...

TAN WhoAMI

with the above query, I get those entries with Status=10,20 and 90.

I want to check for entries which have Status=90 alone, and which DO NOT HAVE Status=10 or Status=20. I did below, but seem not correct.

15 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted answer
mistake
try like this

SELECT A.PODOID AS Doc_No, Data.Status
FROM pfods.MPDOPE_100 A
INNER JOIN
(
SELECT A.PODOID,MIN(B.Phstat) As Status
FROM pfods.MPDOPE_100 A INNER JOIN pfods.MPDHED_100 B ON B.Phprno=A.Poprno
where (A.PODOID LIKE '923-%' AND (A.POPLGR = '130300' OR A.POPLGR = '130400' OR A.POPLGR = '190301' OR A.POPLGR = '190302' OR A.POPLGR LIKE '4%')
AND (A.POOPDS = 'P--- AID/RESOURCE/FIXTURE' OR A.POOPDS = 'AID/RESOURCE/FIXTURE') AND A.POFACI ='MF1'
AND POSTRT ='STD')
GROUP BY A.PODOID
) as Data
Data.PODOID =A.PODOID AND
Data.Status='90'

Regards,
Iftikar
0
TAN WhoAMI13y ago
the correct query should be as...

SELECT A.PODOID,MIN(B.Phstat) As Status
FROM pfods.MPDOPE_100 A INNER JOIN pfods.MPDHED_100 B ON B.Phprno=A.Poprno
where (A.PODOID LIKE '923-%' AND (A.POPLGR = '130300' OR A.POPLGR = '130400' OR A.POPLGR = '190301' OR A.POPLGR = '190302' OR A.POPLGR LIKE '4%')
AND (A.POOPDS = 'P--- AID/RESOURCE/FIXTURE' OR A.POOPDS = 'AID/RESOURCE/FIXTURE') AND A.POFACI ='MF1'
AND POSTRT ='STD')
GROUP BY A.PODOID
) data
where data.Status='90'
0
TAN WhoAMI13y ago
https://join.me/984-711-355
0
Iftikar Hussain13y ago
try this

SELECT A.PODOID AS Doc_No, Data.Status
FROM pfods.MPDOPE_100 A
INNER JOIN
(
SELECT A.PODOID,MIN(B.Phstat) As Status
FROM pfods.MPDOPE_100 A INNER JOIN pfods.MPDHED_100 B ON B.Phprno=A.Poprno
where (A.PODOID LIKE '923-%' AND (A.POPLGR = '130300' OR A.POPLGR = '130400' OR A.POPLGR = '190301' OR A.POPLGR = '190302' OR A.POPLGR LIKE '4%')
AND (A.POOPDS = 'P--- AID/RESOURCE/FIXTURE' OR A.POOPDS = 'AID/RESOURCE/FIXTURE') AND A.POFACI ='MF1'
AND POSTRT ='STD')
GROUP BY A.PODOID
) as Data
ON
Data.PODOID =A.PODOID AND
Data.Status='90'

Regards,
Iftikar
0
Iftikar Hussain13y ago
in which line you are getting?

Regards,
Iftikar
0
13y ago
can we do join.me?
0
TAN WhoAMI13y ago
same problem: ORA-00905: missing keyword
SELECT A.PODOID AS Doc_No, Data.Status
FROM pfods.MPDOPE_100 A
INNER JOIN
(
SELECT A.PODOID, MIN(B.PHSTAT) As Status
FROM pfods.MPDOPE_100 A INNER JOIN pfods.MPDHED_100 B ON B.PHPRNO=A.POPRNO
where (A.PODOID LIKE '923-%' AND (A.POPLGR = '130300' OR A.POPLGR = '130400'
OR A.POPLGR = '190301' OR A.POPLGR = '190302' OR A.POPLGR LIKE '4%')
AND (A.POOPDS = 'P--- AID/RESOURCE/FIXTURE' OR A.POOPDS = 'AID/RESOURCE/FIXTURE')
AND A.POFACI ='MF1' AND POSTRT ='STD')
GROUP BY A.PODOID
) as Data
Data.PODOID = A.PODOID AND
Data.Status='90'
0
13y ago
Change to a.podoid
0
TAN WhoAMI13y ago

what is t.PoId?
0
Iftikar Hussain13y ago
try like this

SELECT A.PODOID AS Doc_No, Data.Status
FROM pfods.MPDOPE_100 A
INNER JOIN
(
SELECT A.PODOID,MIN(B.Phstat) As Status
FROM pfods.MPDOPE_100 A INNER JOIN pfods.MPDHED_100 B ON B.Phprno=A.Poprno
where (A.PODOID LIKE '923-%' AND (A.POPLGR = '130300' OR A.POPLGR = '130400' OR A.POPLGR = '190301' OR A.POPLGR = '190302' OR A.POPLGR LIKE '4%')
AND (A.POOPDS = 'P--- AID/RESOURCE/FIXTURE' OR A.POOPDS = 'AID/RESOURCE/FIXTURE') AND A.POFACI ='MF1'
AND POSTRT ='STD')
GROUP BY A.PODOID
) as Data
Data.PODOID = t.PoId AND
Data.Status='90'

Regards,
Iftikar
0
TAN WhoAMI13y ago
SELECT A.PODOID AS Doc_No, B.Phstat As Status
FROM pfods.MPDOPE_100 A INNER JOIN pfods.MPDHED_100 B ON B.Phprno=A.Poprno
where (A.PODOID LIKE '923-%' AND (A.POPLGR = '130300' OR A.POPLGR = '130400' OR A.POPLGR = '190301' OR A.POPLGR = '190302' OR A.POPLGR LIKE '4%')
AND (A.POOPDS = 'P--- AID/RESOURCE/FIXTURE' OR A.POOPDS = 'AID/RESOURCE/FIXTURE') AND A.POFACI ='MF1'
AND POSTRT ='STD')
-- and TRIM(PHSTAT)='90'
--AND (POTDAT is NULL OR POTDAT >= to_date('2013-07-31','yyyy-mm-dd')))
Order by PODOID ASC
0
13y ago
Hi,
It looks like somewhere the records are duplicating. Make sure, your join conditions are correct and it is not forming any duplicate records.

If possible can be post the query as text instead of screenshot?
0
Iftikar Hussain13y ago
ok, I got your question now, can you try like this

SELECT t.PoId, t.Status
FROM Table1 t
INNER JOIN (
SELECT B.PoId, MIN(B.Status) AS Sts
FROM Table1 B
GROUP BY B.PoId
) data
ON
data.PoId = t.PoId AND data.Sts = t.Status
and data.Sts='90'

Regards,
Iftikar
0
TAN WhoAMI13y ago
does not work.. you see..

but 923-0020 does contain Status=10,20, see below:

I want to extract out those entries with Status = 90 alone, and does not have Status=10 or Status=20.
0
Iftikar Hussain13y ago
Hi,
Try like this

select *from table_name where TRIM(PHSTAT)='90'

Regards,
Iftikar
0