Dante S.
|
| Posted: 06/13/2002, 1:22 AM |
|
Hello everyone.
How do I properly use the 'is null' condition? What should be the value of
the parameter source field? I tried using URL and viewed the generated SQL,
and I find no error in it. Here's the SQL:
SELECT CUST_CONTR, CUST_PROD_TYPE, CUST_LAST_NAME, CUST_FIRST_NAME,
CUST_MIDDLE_INITIAL, CUST_FCONTRACT_ISSUED, CUST_FCONTRACT_ISSUED_DT,
CUST_LOCATION, PROD_NAME
FROM CUSTOMER, PRODUCT
WHERE ( (CUSTOMER.CUST_PROD_TYPE = PRODUCT.PROD_CODE) ) AND ( CUST_LOCATION
= '{UserLocation}' AND CUST_PROD_TYPE = '{s_CUST_PROD_TYPE}' AND (
CUST_FCONTRACT_ISSUED IS NULL OR CUST_FCONTRACT_ISSUED_DT IS NULL ) )
ORDER BY CUST_CONTR
When I tested my app, it still lists records where CUST_FCONTRACT_ISSUED and
CUST_FCONTRACT_ISSUED_DT fields are not null. What am I missing?
Thanks,
Dante S.
|
|
|
 |
Alexey Alexapolsky
|
| Posted: 06/13/2002, 6:49 AM |
|
They are probably not null, but contain empty strings
--
Alex
CodeCharge Developer
"Dante S." <disandigan@loyolaplans.com> wrote in message
news:ae9ko9$a2i$1@news.codecharge.com...
> Hello everyone.
>
> How do I properly use the 'is null' condition? What should be the value
of
> the parameter source field? I tried using URL and viewed the generated
SQL,
> and I find no error in it. Here's the SQL:
>
> SELECT CUST_CONTR, CUST_PROD_TYPE, CUST_LAST_NAME, CUST_FIRST_NAME,
> CUST_MIDDLE_INITIAL, CUST_FCONTRACT_ISSUED, CUST_FCONTRACT_ISSUED_DT,
> CUST_LOCATION, PROD_NAME
> FROM CUSTOMER, PRODUCT
> WHERE ( (CUSTOMER.CUST_PROD_TYPE = PRODUCT.PROD_CODE) ) AND
CUST_LOCATION
> = '{UserLocation}' AND CUST_PROD_TYPE = '{s_CUST_PROD_TYPE}' AND (
> CUST_FCONTRACT_ISSUED IS NULL OR CUST_FCONTRACT_ISSUED_DT IS NULL ) )
> ORDER BY CUST_CONTR
>
> When I tested my app, it still lists records where CUST_FCONTRACT_ISSUED
and
> CUST_FCONTRACT_ISSUED_DT fields are not null. What am I missing?
>
> Thanks,
> Dante S.
>
>
|
|
|
|
Dante S.
|
| Posted: 06/13/2002, 4:03 PM |
|
Alex,
Thanks for the reply. I am sure they are null because when I run my sql
script in sql plus (btw, the database is oracle) it only returns records
whose two fields are null. And to further prove my point, I've done a
workaround by adding the following line in the Private Sub Class_Initialize
in the Code Window:
and (CUST_FCONTRACT_ISSUED_DT is Null or CUST_FCONTRACT_ISSUED is
Null)
I appended the above in the Where variable there, and when I ran my program
it worked. So I know for sure that they are null and do not contain empty
strings. I hope you could explain why is this strange behaviour occurs.
Furthermore, I don't think this is the 'normal' way of testing fields for
null values. Is there something wrong with the 'Is Null' in the table
parameter window? The documentation is not clear on how to use this
operator, I hope you can shed some light into this.
Thanks,
Dante
"Alexey Alexapolsky" <alexa@codecharge.com> wrote in message
news:aea7sp$gru$2@news.codecharge.com...
> They are probably not null, but contain empty strings
>
> --
> Alex
> CodeCharge Developer
>
>
> "Dante S." <disandigan@loyolaplans.com> wrote in message
>news:ae9ko9$a2i$1@news.codecharge.com...
> > Hello everyone.
> >
> > How do I properly use the 'is null' condition? What should be the value
> of
> > the parameter source field? I tried using URL and viewed the generated
> SQL,
> > and I find no error in it. Here's the SQL:
> >
> > SELECT CUST_CONTR, CUST_PROD_TYPE, CUST_LAST_NAME, CUST_FIRST_NAME,
> > CUST_MIDDLE_INITIAL, CUST_FCONTRACT_ISSUED, CUST_FCONTRACT_ISSUED_DT,
> > CUST_LOCATION, PROD_NAME
> > FROM CUSTOMER, PRODUCT
> > WHERE ( (CUSTOMER.CUST_PROD_TYPE = PRODUCT.PROD_CODE) ) AND
> CUST_LOCATION
> > = '{UserLocation}' AND CUST_PROD_TYPE = '{s_CUST_PROD_TYPE}' AND (
> > CUST_FCONTRACT_ISSUED IS NULL OR CUST_FCONTRACT_ISSUED_DT IS NULL ) )
> > ORDER BY CUST_CONTR
> >
> > When I tested my app, it still lists records where CUST_FCONTRACT_ISSUED
> and
> > CUST_FCONTRACT_ISSUED_DT fields are not null. What am I missing?
> >
> > Thanks,
> > Dante S.
> >
> >
>
>
|
|
|
|
DonB
|
| Posted: 06/13/2002, 7:06 PM |
|
I believe you have a paren out of place
You have:
WHERE
(
(CUSTOMER.CUST_PROD_TYPE = PRODUCT.PROD_CODE)
)
AND
( CUST_LOCATION = '{UserLocation}'
AND
CUST_PROD_TYPE = '{s_CUST_PROD_TYPE}'
AND
(CUST_FCONTRACT_ISSUED IS NULL
OR
CUST_FCONTRACT_ISSUED_DT IS NULL )
)
I'm guessing you meant:
WHERE
(
(CUSTOMER.CUST_PROD_TYPE = PRODUCT.PROD_CODE)
AND
( CUST_LOCATION = '{UserLocation}'
AND
CUST_PROD_TYPE = '{s_CUST_PROD_TYPE}'
)
<---- move it here???
AND
(CUST_FCONTRACT_ISSUED IS NULL
OR
CUST_FCONTRACT_ISSUED_DT IS NULL )
)
"Dante S." <disandigan@loyolaplans.com> wrote in message
news:aeb8cp$m8p$1@news.codecharge.com...
> Alex,
>
> Thanks for the reply. I am sure they are null because when I run my sql
> script in sql plus (btw, the database is oracle) it only returns records
> whose two fields are null. And to further prove my point, I've done a
> workaround by adding the following line in the Private Sub
Class_Initialize
> in the Code Window:
> and (CUST_FCONTRACT_ISSUED_DT is Null or CUST_FCONTRACT_ISSUED is
> Null)
>
> I appended the above in the Where variable there, and when I ran my
program
> it worked. So I know for sure that they are null and do not contain empty
> strings. I hope you could explain why is this strange behaviour occurs.
> Furthermore, I don't think this is the 'normal' way of testing fields for
> null values. Is there something wrong with the 'Is Null' in the table
> parameter window? The documentation is not clear on how to use this
> operator, I hope you can shed some light into this.
>
> Thanks,
> Dante
>
> "Alexey Alexapolsky" <alexa@codecharge.com> wrote in message
>news:aea7sp$gru$2@news.codecharge.com...
> > They are probably not null, but contain empty strings
> >
> > --
> > Alex
> > CodeCharge Developer
> >
> >
> > "Dante S." <disandigan@loyolaplans.com> wrote in message
> >news:ae9ko9$a2i$1@news.codecharge.com...
> > > Hello everyone.
> > >
> > > How do I properly use the 'is null' condition? What should be the
value
> > of
> > > the parameter source field? I tried using URL and viewed the generated
> > SQL,
> > > and I find no error in it. Here's the SQL:
> > >
> > > SELECT CUST_CONTR, CUST_PROD_TYPE, CUST_LAST_NAME, CUST_FIRST_NAME,
> > > CUST_MIDDLE_INITIAL, CUST_FCONTRACT_ISSUED, CUST_FCONTRACT_ISSUED_DT,
> > > CUST_LOCATION, PROD_NAME
> > > FROM CUSTOMER, PRODUCT
> > > WHERE ( (CUSTOMER.CUST_PROD_TYPE = PRODUCT.PROD_CODE) ) AND
> > CUST_LOCATION
> > > = '{UserLocation}' AND CUST_PROD_TYPE = '{s_CUST_PROD_TYPE}' AND (
> > > CUST_FCONTRACT_ISSUED IS NULL OR CUST_FCONTRACT_ISSUED_DT IS NULL ) )
> > > ORDER BY CUST_CONTR
> > >
> > > When I tested my app, it still lists records where
CUST_FCONTRACT_ISSUED
> > and
> > > CUST_FCONTRACT_ISSUED_DT fields are not null. What am I missing?
> > >
> > > Thanks,
> > > Dante S.
> > >
> > >
> >
> >
>
>
|
|
|
|
Dante S.
|
| Posted: 06/13/2002, 7:24 PM |
|
In the table parameter window, when you test for null on a certain field
(e.g. field1 is null) what do you put in the parameter source field and what
should its type be? Based on my knowledge, testing for null is a unary
operator, thereby requiring only one operand. I tried not putting anything
on the parameter source field but CCS insists that this field is required.
Anyone out there who can shed light on me on how to use null/is not null
definition on the table parameter window?'
Thanks,
Dante S.
|
|
|
|
DonB
|
| Posted: 06/13/2002, 7:59 PM |
|
Ahh... all AND's so that doesn't matter.
"DonB" <7432D63DBB01D03A196B1EDD80E8@comcast.net> wrote in message
news:aebj2m$ap3$1@news.codecharge.com...
> I believe you have a paren out of place
>
>
> You have:
>
> WHERE
> (
> (CUSTOMER.CUST_PROD_TYPE = PRODUCT.PROD_CODE)
> )
> AND
> ( CUST_LOCATION = '{UserLocation}'
> AND
> CUST_PROD_TYPE = '{s_CUST_PROD_TYPE}'
> AND
> (CUST_FCONTRACT_ISSUED IS NULL
> OR
> CUST_FCONTRACT_ISSUED_DT IS NULL )
> )
>
> I'm guessing you meant:
>
> WHERE
> (
> (CUSTOMER.CUST_PROD_TYPE = PRODUCT.PROD_CODE)
> AND
> ( CUST_LOCATION = '{UserLocation}'
> AND
> CUST_PROD_TYPE = '{s_CUST_PROD_TYPE}'
> )
> <---- move it here???
> AND
> (CUST_FCONTRACT_ISSUED IS NULL
> OR
> CUST_FCONTRACT_ISSUED_DT IS NULL )
> )
>
>
> "Dante S." <disandigan@loyolaplans.com> wrote in message
>news:aeb8cp$m8p$1@news.codecharge.com...
> > Alex,
> >
> > Thanks for the reply. I am sure they are null because when I run my sql
> > script in sql plus (btw, the database is oracle) it only returns records
> > whose two fields are null. And to further prove my point, I've done a
> > workaround by adding the following line in the Private Sub
> Class_Initialize
> > in the Code Window:
> > and (CUST_FCONTRACT_ISSUED_DT is Null or CUST_FCONTRACT_ISSUED
is
> > Null)
> >
> > I appended the above in the Where variable there, and when I ran my
> program
> > it worked. So I know for sure that they are null and do not contain
empty
> > strings. I hope you could explain why is this strange behaviour occurs.
> > Furthermore, I don't think this is the 'normal' way of testing fields
for
> > null values. Is there something wrong with the 'Is Null' in the table
> > parameter window? The documentation is not clear on how to use this
> > operator, I hope you can shed some light into this.
> >
> > Thanks,
> > Dante
> >
> > "Alexey Alexapolsky" <alexa@codecharge.com> wrote in message
> >news:aea7sp$gru$2@news.codecharge.com...
> > > They are probably not null, but contain empty strings
> > >
> > > --
> > > Alex
> > > CodeCharge Developer
> > >
> > >
> > > "Dante S." <disandigan@loyolaplans.com> wrote in message
> > >news:ae9ko9$a2i$1@news.codecharge.com...
> > > > Hello everyone.
> > > >
> > > > How do I properly use the 'is null' condition? What should be the
> value
> > > of
> > > > the parameter source field? I tried using URL and viewed the
generated
> > > SQL,
> > > > and I find no error in it. Here's the SQL:
> > > >
> > > > SELECT CUST_CONTR, CUST_PROD_TYPE, CUST_LAST_NAME, CUST_FIRST_NAME,
> > > > CUST_MIDDLE_INITIAL, CUST_FCONTRACT_ISSUED,
CUST_FCONTRACT_ISSUED_DT,
> > > > CUST_LOCATION, PROD_NAME
> > > > FROM CUSTOMER, PRODUCT
> > > > WHERE ( (CUSTOMER.CUST_PROD_TYPE = PRODUCT.PROD_CODE) ) AND
> > > CUST_LOCATION
> > > > = '{UserLocation}' AND CUST_PROD_TYPE = '{s_CUST_PROD_TYPE}' AND (
> > > > CUST_FCONTRACT_ISSUED IS NULL OR CUST_FCONTRACT_ISSUED_DT IS
NULL ) )
> > > > ORDER BY CUST_CONTR
> > > >
> > > > When I tested my app, it still lists records where
> CUST_FCONTRACT_ISSUED
> > > and
> > > > CUST_FCONTRACT_ISSUED_DT fields are not null. What am I missing?
> > > >
> > > > Thanks,
> > > > Dante S.
> > > >
> > > >
> > >
> > >
> >
> >
>
>
|
|
|
|
|
