I have to table like below records
Select * from pa_tbl where emplid=’ 5705838’
Gives the Below result.
From above result I have to select only ‘state_code’
I have to select state code like ‘AK’, ‘NV’, ‘CO’, ‘PR’ among lot of state_code .
Above example only for CO state_code
I am able to extract sate code like ’CO’ using below query
FROM PS pa_tbl S
WHERE S.EMPLID ='5705838'
AND S.RESIDENT = 'N'
AND S.STATE IN('AK','NV','CO','MI')
AND S.EFFDT = (SELECT MAX(EFFDT)
FROM PS pa_tbl
WHERE EMPLID = S.EMPLID
AND COMPANY = S.COMPANY
AND EFFDT <=SYSDATE);
Here user entered 1/1/2001 state_code is PA and resident=Y which is latest. In this case my query should return NULL/NODATA found.
But my query returns STATE_CODE=CO (this is wrong)
How to write a sql query?
This is not a forum for sql or plsql questions. Please post in appropriate forums.