Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to add if then else in sql query

756945Aug 5 2010 — edited Aug 5 2010
Hi All

I want to replace the case statment with if then else statment. Is this possible. Kindly suggest me its solution.
Below is the query which includes a case statment kindly replace it with if then else statments

SELECT B.BED_ID,
HIS.PKG_PATIENT.GET_RANK_TITLE_NAME (P.MRNO)
|| HIS.PKG_PATIENT.GET_DEP_RELATION_TEXT_REP (P.MRNO) PAT_NAME,
P.SS_NO SERVICE_NO, HIS.FUN_SHORT_RANK (P.MRNO) RANKS,
HIS.FUN_UNIT (P.MRNO) UNIT, R.ORDER_LOCATION_ID ORDER_LOCATION_ID1,
SUBSTR (OL.DESCRIPTION, 11) FLOORS, OL.DESCRIPTION FLOOR_DESC,
R.CAPACITY, RC.REPORT_ORDER, SUBSTR (RC.DESCRIPTION, 1, 60) BED_TYPE,
B.MRNO MRNO, B.NAME, B.MRNO FULL_MRNO,
TRUNC (MONTHS_BETWEEN (B.ADMISSION_DATE, B.DOB) / 12, 0) AGE,
B.ADMISSION_DATE, B.ADMISSION_NO, B.ORDER_TYPE_ID, B.ORDER_NO,
B.LOCATION_ID, B.ORDER_LOCATION_ID,
RPC.DESCRIPTION ROOM_PAYMENT_CATEGORY, B.ADMISSION_FINAL,
B.DOCTOR_ID, D.FULL_NAME DOCTOR_NAME, UPPER (B.SEX) SEX,
B.HOSPITALIST_MRNO, B.HOSPITALIST_NAME,

CASE WHEN (B.DETAINED_ADMITED='Y' AND B.DETAINED_CONVERTED_DATE IS NULL) THEN
'DETAINED'
ELSE
'ADMITTED'
END DETAIN ,

B.COVERING_HOSPITALIST_MRNO
FROM DEFINITIONS.BEDS B,
DEFINITIONS.ROOMS R,
DEFINITIONS.ORDER_LOCATION OL,
DEFINITIONS.DOCTOR D,
DEFINITIONS.ROOM_CATEGORY RC,
REGISTRATION.PATIENT P,
DEFINITIONS.ROOM_PAYMENT_CATEGORY RPC,
ORDERENTRY.ORDER_PATIENT_ADMISSION OPA
WHERE P.MRNO = B.MRNO
AND B.ACTIVE = 'Y'
AND B.ROOM_ID = R.ROOM_ID
AND R.LOCATION_ID = OL.LOCATION_ID
AND R.ORDER_LOCATION_ID = OL.ORDER_LOCATION_ID
AND R.CATEGORY_ID = RC.CATEGORY_ID
AND B.ORDER_TYPE_ID = OPA.ORDER_TYPE_ID(+)
AND B.ORDER_NO = OPA.ORDER_NO(+)
AND B.LOCATION_ID = OPA.LOCATION_ID(+)
AND B.ORDER_LOCATION_ID = OPA.ORDER_LOCATION_ID(+)
AND D.DOCTOR_ID(+) = B.DOCTOR_ID
AND R.ROOM_PAYMENT_CATEGORY_ID = RPC.ROOM_PAYMENT_CATEGORY_ID(+)
AND OPA.MRNO = NVL (:P_MRNO, OPA.MRNO)
AND OPA.ADMISSION_TYPE = NVL (:P_ADMISSION_TYPE, OPA.ADMISSION_TYPE)
AND OL.ORDER_LOCATION_ID =
NVL (:P_ORDER_LOCATION_ID, OL.ORDER_LOCATION_ID)
AND B.ADMISSION_FINAL = 'Y'
ORDER BY B.ADMISSION_DATE
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 2 2010
Added on Aug 5 2010
7 comments
762 views