This is a multi-part message in MIME format.

------=_NextPartTM-000-eadcd511-462a-429e-aadc-9dcd5c9cb168
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi All,
Thanks for your help. Al, I used the ifnull function and it =
works fine.

Regards,
Asheesh.

> -----Original Message-----
> From: Al Sharka [mailto:asharka@my-deja.com]
> Sent: Wednesday, September 03, 2003 8:41 PM
> To: informix-list@iiug.org
> Subject: Re: Urgent:Help on SQL
>=20
>=20
> Rajib Sarkar <rsarkar@us.ibm.com> wrote: =20
> >Rastogi, Asheesh wrote:
> >> =20
> >> I am running the following SQL on Redbrick 6.1 OS - AIX 4.3:
> >>=20
> >> select ia_sk_SKU_key,hs_department_number from dim_sku where
> >> ia_sku_level=3D'De-BRAND' and dim_sku.hs_department_number not in
> >> (select distinct int(DEPARTMENT_NUMBER) from
> >> STAGE_STAT_LINE_ITEMS);=20
> >>=20
> >> Here the hs_department_number is of type SMALLINT
> >> department_number is of type CHAR(5). Logically I should be
> >> getting a big result set, however I donot get any results. When I
> >> run the same query by removing the not clause I get the expected
> >> result set.=20
> >>=20
> >> Is there something I am missing here? Your comments/suggesttions
> >> are most welcome.
> >
> > Is there a possibility that one of the DEPARTMENT_NUMBER is NULL=20
> > ..if that is so, then the result set will probably be nil... maybe
> > you should change your subquery to ..
> > select distinct int(nvl(department_number,0)) from
> > STAGE_STAT_ITEMS ...provided there's an NVL function in REDBRICK
>=20
> If I read this correctly, the "nvl" function is called "ifnull" in=20
> RedBrick, so that subquery should look like this:
>=20
> select distinct int(ifnull(department_number,'0')) from ....
>=20
> You need the single quotes around the value substituted by the ifnull=20
> function to return the same datatype as the department_number column,=20
> which is char. I tested this with a NULL value in the table and it=20
> does exibit the problem of the OP, so I'd say that's probably his=20
> problem.
>=20
>=20
>=20
>=20
>=20
> =20
>=20

------=_NextPartTM-000-eadcd511-462a-429e-aadc-9dcd5c9cb168
Content-Type: text/plain;
name="InterScan_Disclaimer.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="InterScan_Disclaimer.txt"


This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly
prohibited and may be unlawful.

Visit us at http://www.cognizant.com


------=_NextPartTM-000-eadcd511-462a-429e-aadc-9dcd5c9cb168--
sending to informix-list