SQL Extract Data that single status alone...

Question

with the above query I get those entries with Status=1020 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.

Iftikar Hussain · Answer

mistake try like this SELECT A.PODOID AS Doc_No Data.Status FROM pfods.MPDOPE_100 A INNER JOIN ( SELECT A.PODOIDMIN(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

TAN WhoAMI · Answer

the correct query should be as... SELECT A.PODOIDMIN(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'

TAN WhoAMI · Answer

https://join.me/984-711-355

Iftikar Hussain · Answer

try this SELECT A.PODOID AS Doc_No Data.Status FROM pfods.MPDOPE_100 A INNER JOIN ( SELECT A.PODOIDMIN(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

Iftikar Hussain · Answer

in which line you are getting? Regards Iftikar

Muralidharan Deenathayalan · Answer

can we do join.me?

TAN WhoAMI · Answer

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'

Muralidharan Deenathayalan · Answer

Change to a.podoid

TAN WhoAMI · Answer

what is t.PoId?

Iftikar Hussain · Answer

try like this SELECT A.PODOID AS Doc_No Data.Status FROM pfods.MPDOPE_100 A INNER JOIN ( SELECT A.PODOIDMIN(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

TAN WhoAMI · Answer

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

Muralidharan Deenathayalan · Answer

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?

Iftikar Hussain · Answer

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

TAN WhoAMI · Answer

does not work.. you see.. but 923-0020 does contain Status=1020 see below: I want to extract out those entries with Status = 90 alone and does not have Status=10 or Status=20.

Iftikar Hussain · Answer

Hi Try like this select *from table_name where TRIM(PHSTAT)='90' Regards Iftikar