Join not working between varchar fields

Database

Join not working between varchar fields

Hi, I have two tables TableA ( INV_NBR varchar (15) ) and TableB ( INV_NBR varchar (15) ). In both the tables, TableA and TableB  the INV_NBR has values '14-2/324', when I join the two tables based on INV_NBR it shows "0" records.

Used below query: Select * from TableA  A inner join TableB B  ON A.INV_NBR =B.INV_NBR

---Result 0 records.

I tried using cast and trim but same result.

Can someone please help me with this issue.

Thanks in Advance.

6 REPLIES
Supporter

Re: Join not working between varchar fields

can you share the ddl's of the tables?

and the explain?

Supporter

Re: Join not working between varchar fields

and pleas show us the result of

select 'xxx' || INV_NBR || 'yyy' from TableA where INV_NBR = '14-2/324';
select 'xxx' || INV_NBR || 'yyy' from TableB where INV_NBR = '14-2/324';

Re: Join not working between varchar fields

Hi, 

Here is the DDL of two tables followed by result to the query set,

CREATE MULTISET TABLE TABLEA ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      Display_order_number BIGINT,

       prod_id INTEGER,

      brand VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_TYPE VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

       Barcode VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      Quantity INTEGER,

       Inv_nbr VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( Display_order_number );

CREATE MULTISET TABLE TableB ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      Display_order_number VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

       prod_id INTEGER,

      brand VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      PROD_TYPE VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

       Barcode VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      Quantity INTEGER,

       Tax_percent DECIMAL(5,2),

      Tax_type VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      Tax_amount DECIMAL(7,2),

      Total_Price DECIMAL(7,2),

      Discount DECIMAL(7,2),

      CUST_NAME VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

        CUST_email VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      REV_PICKUP_DATE DATE FORMAT 'YY/MM/DD',

      Inv_nbr VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( Display_order_number );

select 'xxx' || INV_NBR || 'yyy' from TableA where INV_NBR = '14-2/324';

select 'xxx' || INV_NBR || 'yyy' from TableB where INV_NBR = '14-2/324';

RESULT:

xxx14-2/324yyy

xxx14-2/324yyy

xxx14-2/324yyy

xxx14-2/324yyy

Thanks

Re: Join not working between varchar fields

The result is from tableA and from tableB, it returned 0 records.

Thanks,

Supporter

Re: Join not working between varchar fields

In this case your query is giving the correct result.

You need to check why tableB does not contain the value '14-2/324'...

Re: Join not working between varchar fields

Got it finally, TabelB is loaded form file and the file has a tab character at the end. So the value of INV_NBR was inserted with trailing tab.

The TRIM function only removes spaces and not the tab, so there was not match resulted.

After reloding the file it worked.

Thanks the query helped.