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

On Nov 29, 10:07 pm, maha.sasibin... (AT) gmail (DOT) com wrote:
Even if you changed:
A.CIP_CATEGORY_CODE LIKE '%'
to
( A.CIP_CATEGORY_CODE LIKE '%' OR A.CIP_CATEGORY_CODE IS NULL )

(Have you not read the SQL manual about three valued logic???))

the next part of your condition clause:
AND(A.CIP_CATEGORY_CODE = Z.ICODE_VAL (+)
will filter it out.

You will need to make two queries an union them together for one set
of results. Obviously in the second query where
A.CIP_CATEGORY_CODE IS NULL
you will not be able to join to the Z inline view.

HTH,
ed