Hi,
i want to create this one store procedures, but there is else condition i don't understand, how can I create about this, please kindly guide. seems this is a single query only. need to create with bind variables.
SELECT charge_id, C.CHARGE_DEFINITION_ID, CD.CHARGE_NAME, CD.E2K_CHARGE_CD, CD.NOTE_LIST, CD.NOTE_REQUIRED_IND, C.ATTRIBUTE_CD, CD.OCCURS_FREQUENTLY_IND as frequent,
(CASE WHEN C.ATTRIBUTE_CD IS NULL THEN CD.UOM_CATEGORY
ELSE
(SELECT RA.UOM_CATEGORY from rating_attribute_type RA where C.ATTRIBUTE_CD = RA.ATTRIBUTE_CD)
END) as uom_category, 1 as pref FROM charge C, charge_definition CD
WHERE C.RATE_SCHEDULE_ID = ' ' AND C.SERVICE_NAME = ' '
AND CD.CHARGE_DEFINITION_ID = C.CHARGE_DEFINITION_ID
UNION
SELECT charge_id,C.CHARGE_DEFINITION_ID, CD.CHARGE_NAME, CD.E2K_CHARGE_CD, CD.NOTE_LIST, CD.NOTE_REQUIRED_IND,
C.ATTRIBUTE_CD, CD.OCCURS_FREQUENTLY_IND as frequent,
(CASE WHEN C.ATTRIBUTE_CD IS NULL THEN CD.UOM_CATEGORY
ELSE
(SELECT RA.UOM_CATEGORY from rating_attribute_type RA where C.ATTRIBUTE_CD = RA.ATTRIBUTE_CD)
END) as uom_category, 2 as pref
FROM charge C, charge_definition CD
WHERE C.RATE_SCHEDULE_ID = ' ' AND C.SERVICE_NAME IS NULL
AND CD.CHARGE_DEFINITION_ID = C.CHARGE_DEFINITION_ID
UNION
SELECT charge_id, C.CHARGE_DEFINITION_IDCD.CHARGE_NAME, CD.E2K_CHARGE_CD, CD.NOTE_LIST,
CD.NOTE_REQUIRED_IND, C.ATTRIBUTE_CD, CD.OCCURS_FREQUENTLY_IND as frequent,
(CASE WHEN C.ATTRIBUTE_CD IS NULL THEN CD.UOM_CATEGORY
ELSE (SELECT RA.UOM_CATEGORY from rating_attribute_type RA where C.ATTRIBUTE_CD = RA.ATTRIBUTE_CD)
END) as uom_category, 3 as pref FROM charge C, charge_definition CD
WHERE C.RATE_SCHEDULE_ID = ' ' AND C.SERVICE_NAME = ' '
AND CD.CHARGE_DEFINITION_ID = C.CHARGE_DEFINITION_ID
UNION
SELECT charge_id, C.CHARGE_DEFINITION_ID, CD.CHARGE_NAME, CD.E2K_CHARGE_CD, CD.NOTE_LIST,
CD.NOTE_REQUIRED_IND, C.ATTRIBUTE_CD, CD.OCCURS_FREQUENTLY_IND as frequent,
(CASE WHEN C.ATTRIBUTE_CD IS NULL THEN CD.UOM_CATEGORY
ELSE
(SELECT RA.UOM_CATEGORY from rating_attribute_type RA where C.ATTRIBUTE_CD = RA.ATTRIBUTE_CD)
END) as uom_category, 4 as pref FROM charge C, charge_definition CD
WHERE C.RATE_SCHEDULE_ID = ' ' AND C.SERVICE_NAME IS NULL
AND CD.CHARGE_DEFINITION_ID = C.CHARGE_DEFINITION_ID ORDER BY 5 ASC;