Re: How I can select where a date is null..

,Paola <paolaa_at_libero.it> wrote:
: Hi, my problem is the following:
: I have different selection parameters for my query. So, the user can insert
: one or more of these selection fields.

: I think that an example can explain you better.
: this is my table:
: TAB1 (
: field1 VARCHAR2(25) NULL,
: filed2 VARCHAR2(25) NULL,
: ...
: DATA1 NUMBER(7) NULL)

: (field DATA1 is a date in JULIAN format)

: field1 field2 data1
: -----------------------
: A A 2450341
: B BB

: My query, supposed that var1 = '%' and var2 = '%') is

: SELECT field1 FROM "tab1"
: WHERE ( upper("tab1".field1") like :var1) AND
: ( upper("tab1"."field1") like :var2) AND
: ...
: ( "tab1"."DATA_1" >=
: TO_NUMBER(to_char(TO_DATE(:var_data,'DD/MM/YYYY'),'J')))

: This SELECT has no result because DATA1 in the second record is null.
: How can resolve this problem?

: Thank in advance!
: Paola

When correct typo in WHERE ( upper("tab1".field1") like :var1) AND returns row where field1 is 'A' for me. However, if I set var1 to 'A%' and var2 to 'B%' nothing is returned. This is to be expected. Field1 cannot be like both var1 AND var2. If you really meant to compare field1 to var2 then change the AND to OR. Could it be a typo? Did you mean to compare field2 to var2!

HTH Helen Received on Mon Apr 19 2004 - 08:54:29 CDT