regarding the query which retrieves the data depending on search criteria entered

 SELECT A.CIPACTIVITY_SYS_CODE, A.CIP_NAME, TO_CHAR(A.CIP_START_DATE,'DD/MM/YYYY'),
 A.DURATION_HRS, A.DURATION_MINS, A.CONTEXT_TYPE_ICODE, A.CREATOR_NAME, A.RECORD_VERSION_NO, X.CODE_FULL_DESC, Y.CODE_FULL_DESC,
 Z.ICODE_DESC, A.CIP_CATEGORY_CODE
 FROM CP_CIP_SCHOOL_ACTIVITY A, CP_CODE_CCA_CIP X, CP_CODE_CCA_CIP Y, (select ICODE_VAL, ICODE_DESC FROM CP_ARCH_ICODE WHERE CATEGORY_NAME = 'CIPACTIVITYCATEGORY') Z
 WHERE UPPER(A.CIP_NAME) LIKE UPPER('%') AND (A.CIP_START_DATE BETWEEN TO_DATE('01/01/2008', 'DD/MM/YYYY') AND TO_DATE('31/12/2008', 'DD/MM/ YYYY'))
 AND A.CONTEXT_TYPE_ICODE LIKE '%'
 AND (A.CIP_SECTOR_CODE LIKE '%' AND A.CIP_SECTOR_CODE = X.CODE_VALUE AND X.CATEGORY_NAME='CIPSECTOR')
 AND (A.CIP_ACTIVITYTYPE_CODE LIKE '%' AND A.CIP_ACTIVITYTYPE_CODE = Y.CODE_VALUE AND Y.CATEGORY_NAME='CIPACTIVITYTYPE')  AND (A.CIP_CATEGORY_CODE LIKE '%' AND(A.CIP_CATEGORY_CODE = Z.ICODE_VAL (+)))
 AND( A.SCHOOL_CODE = '7111') ORDER BY A.CIP_START_DATE DESC; This is the query ......and the result of this query is

it retrieves the data with a category code(A.CIP_CATEGORY_CODE LIKE '%' )
but i want the data with category null also from the database by writing A.CIP_CATEGORY_CODE LIKE '%' retrieves not null values only......how can i modify this statement so that it retrives not null values also when nothing is enetered for category code from the application and retrieves only that particular code values when something is entered....

Thanks in advance Received on Thu Nov 29 2007 - 21:07:21 CST