hi chirag.. its here .. i guess u already might hv got it....
-------------------
SELECT * FROM
(
SELECT J.JOB_ID,
decode(J.STATUS,'STP',0,'NTP',1,'SFT',2,'NFT',3) as stat,
decode(P.IS_VRE,'Y',0,'T',1) as vre,
j.status
FROM
JOBS J,
WORKTYPES W,
PHYSICIANS P,
HOSPITALS_LOCATIONS H
WHERE
H.HOSPITAL_ID = J.HOSPITAL_ID
AND H.LOCATION_ID = J.LOCATION_ID
AND H.STATUS='Y'
AND J.LOCATION_ID = W.LOCATION_ID
AND J.HOSPITAL_ID = W.HOSPITAL_ID
AND J.WORKTYPE_ID = W.WORKTYPE_ID
AND W.STATUS= 'Y'
AND J.HOSPITAL_ID = P.HOSPITAL_ID
AND J.LOCATION_ID = P.LOCATION_ID
AND J.PHYSICIAN_ID = P.PHYSICIAN_ID
AND P.IS_VRE= '&phyFlag'
AND P.STATUS= 'Y'
AND J.JOB_ID NOT IN( SELECT NETCARE_JOB_ID FROM DRAGON_FILES )
AND J.User_ID = &dragonUserID
and J.status in('NFT','SFT','NTP','STP') order by stat,vre )
WHERE ROWNUM < 2;


Deepa Kale -DBA
Jyoti Structures - Nasik(IT)



"Chirag
Majmundar" To: "LazyDBA.com Discussion" <[Email Address Removed]
<[Email Address Removed] cc:
mail.com> Subject: Interesting SQL

02/27/2004 01:01
PM






Hi Friends..!!

Yesterday I have put this query, and in the continuation of that. My
Problem was I wanted to order the unordered list. eg here
I wans jobs with status STP , NTP , SFT , NFT respectively, which I am
getting through decode function.

I want only 1 row to process. but when I put "rownum<2" I am not getting
the desired result.

i tried put the whole following sql in select * from (following sal) where
rownum < 2, but not working.

what to do in this case??

SELECT J.JOB_ID,
decode(J.STATUS,'STP',0,'NTP',1,'SFT',2,'NFT',3) as stat,
decode(P.IS_VRE,'Y',0,'T',1) as vre,
j.status
FROM
JOBS J,
WORKTYPES W,
PHYSICIANS P,
HOSPITALS_LOCATIONS H
WHERE
H.HOSPITAL_ID = J.HOSPITAL_ID
AND H.LOCATION_ID = J.LOCATION_ID
AND H.STATUS='Y'
AND J.LOCATION_ID = W.LOCATION_ID
AND J.HOSPITAL_ID = W.HOSPITAL_ID
AND J.WORKTYPE_ID = W.WORKTYPE_ID
AND W.STATUS= 'Y'
AND J.HOSPITAL_ID = P.HOSPITAL_ID
AND J.LOCATION_ID = P.LOCATION_ID
AND J.PHYSICIAN_ID = P.PHYSICIAN_ID
AND P.IS_VRE= '&phyFlag'
AND P.STATUS= 'Y'
AND J.JOB_ID NOT IN( SELECT NETCARE_JOB_ID FROM DRAGON_FILES )
AND J.User_ID = &dragonUserID
and J.status in('NFT','SFT','NTP','STP')
order by stat,vre
/



- Regards

- Chirag Majmundar.../
Focus Infosys..
+919886169102
------------------------------------------------------------------------------------------------------------------------------------

Good Judgment Comes From Experience.Experiences Comes From Bad
Judgments.
------------------------------------------------------------------------------------------------------------------------------------






Oracle LazyDBA home page