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

On Nov 29, 10:07 pm, maha.sasibin..._at_gmail.com wrote:
> 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

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 Received on Thu Nov 29 2007 - 23:24:37 CST