Re: Urgent:Help on SQL

Is there a possibility that one of the DEPARTMENT_NUMBER is NULL ..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 ... Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "Rastogi, Asheesh (Cognizant)" To: <redbrick-list@iiug.org>, <informix-list@iiug.org> <RAsheesh@blr.cog cc: nizant.com> Subject: Urgent:Help on SQL Sent by: owner-informix-li st@iiug.org 09/02/2003 01:50 PM Hi, I am running the following SQL on Redbrick 6.1 OS - AIX 4.3: select ia_sk_SKU_key,hs_department_number from dim_sku whereia_sku_level='De-BRAND' and dim_sku.hs_department_number not in (select distinct int(DEPARTMENT_NUMBER) from STAGE_STAT_LINE_ITEMS); 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. Is there something I am missing here? Your comments/suggesttions are most welcome. TIA, Asheesh. #### InterScan_Disclaimer.txt has been removed from this note on September 02, 2003 by Rajib Sarkar sending to informix-list

Rajib Sarkar <rsarkar@us.ibm.com> wrote: >Rastogi, Asheesh wrote: >> >> I am running the following SQL on Redbrick 6.1 OS - AIX 4.3: >> >> select ia_sk_SKU_key,hs_department_number from dim_sku where>> ia_sku_level='De-BRAND' and dim_sku.hs_department_number not in >> (select distinct int(DEPARTMENT_NUMBER) from >> STAGE_STAT_LINE_ITEMS); >> >> 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. >> >> 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 > ..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 If I read this correctly, the "nvl" function is called "ifnull" in RedBrick, so that subquery should look like this: select distinct int(ifnull(department_number,'0')) from .... You need the single quotes around the value substituted by the ifnull function to return the same datatype as the department_number column, which is char. I tested this with a NULL value in the table and it does exibit the problem of the OP, so I'd say that's probably his problem.