Archives of the TeradataForum

Message Posted: Tue, 11 Jul 2006 @ 14:59:28 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Join Index error

From:		David Clough

Hi Victor,

No luck to be honest. In the end I got around the problem by creating a Single Table Join Index (my personal favourite) on the NAD table, which was nearly as good.

Here's the table layouts, so if you've still got the energy to look at it then be my guest :

     CREATE MULTISET TABLE DEVNAD_T.NAD ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          DATABLOCKSIZE = 130560 BYTES, CHECKSUM = DEFAULT
          (
           COU_ID CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS 'DE ',
           NAD_ID CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           COM_ID CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           ACG_ID CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           NAM_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAM_INIT_NM CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAM_TITLE_NM CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_HOUSE_NR DECIMAL(5,0) COMPRESS 0. ,
           NAD_FLAT_NR CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '00000',
           NAD_ADDR_1_DS VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAD_ADDR_2_DS VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAD_ADDR_3_DS VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAD_STREET_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAD_CITY_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           PRV_ID CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_PCODE_CD CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_PBOX_CITY_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAD_PBOX_PCODE_CD CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '         ',
           COU_ID_LOCATED CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'DE ',
           NAD_FAX_1_ID CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_FAX_2_ID CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_TEL_1_ID CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_TEL_2_ID CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAD_VAT_ID CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           ACC_ID CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_ID_MAIN CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           NAD_DEVTG_COL_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_DEL_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_ALIAS_EXIST_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NFT_ID CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'M',
           NAD_CONV_PCODE_CD CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAD_ALIAS_SEQ_NR DECIMAL(2,0) COMPRESS 0. ,
           NAD_ANBS_TRNOVR_AM DECIMAL(13,2) COMPRESS 0.00 ,
           NAD_SALES_AGENT_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_LOYALTY_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_DEVTG_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_DEFMT_OWR_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_GRID_REF_CD CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '    ',
           NAD_TOT_EMPL_NR DECIMAL(5,0) COMPRESS 0. ,
           TOW_ID CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_DFLT_INV_AD_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'Y',
           NAD_VAT_EXMPT_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_TX VARCHAR(69) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAD_PBOX_NR CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_SUDO_PCODE_CD CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '         ',
           NAD_CMPY_PRVATE_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'C',
           NAD_TELX_ID CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_VAT_REGD_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS (' ','N','Y'),
           TOW_ID_PLACE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_PROSPECT_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_AGENCY_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_PROSP_BTCH_NR DECIMAL(3,0) COMPRESS 0. ,
           NAD_AWK_FREIGHT_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_TELE_MARKT_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_FRSTTM_TRDR_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_HAZARDOUS_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_NBK_BRANCH_ID CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '          ',
           NAD_NBK_ACCOUNT_NR CHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '               ',
           NAD_NBK_MORE_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_NHO_START_DT DATE FORMAT 'yyyy-mm-dd' COMPRESS ,
           NAD_NHO_END_DT DATE FORMAT 'yyyy-mm-dd' COMPRESS ,
           NAD_NHO_MORE_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_INSTR_EXIST_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_NEXT_CPF_NR DECIMAL(2,0) COMPRESS 2. ,
           LNG_ID CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'EN',
           DSB_ID CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '0',
           NRH_ID CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           RAD_ID CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,
           NAD_FRGHT_CASH_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_DUTIES_CASH_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NAD_ADD_DT DATE FORMAT 'yyyy-mm-dd',
           NAD_INV_ADDR_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_AGENT_ADDR_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_SLS_C_PCODE_CD CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC,
           NAD_DELIV_ADDR_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_CD_ADDR_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_COLL_ADDR_IN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
           NAD_EMS_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '',
           NAD_CLEARANCE_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
           NDE_ID CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('00','02','09'),
           BLK_ID CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '00')
     PRIMARY INDEX NAD_NUPI ( NAD_ID );

     CREATE MULTISET TABLE DEVNAD_T."ACCOUNT" ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          DATABLOCKSIZE = 130560 BYTES, CHECKSUM = DEFAULT
          (
           COU_ID CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           ACC_ID CHAR(9) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           COM_ID CHAR(2) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'WW',
           ACG_ID CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS '001',
           NAD_ID_MAIN CHAR(12) CHARACTER SET LATIN CASESPECIFIC,
           ACC_DEBTOR_STAT_CD CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS 'G',
           ACC_CR_LIMIT_AM DECIMAL(13,2) COMPRESS 0.00 ,
           ACC_CHECK_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS '',
           ACC_INTL_ACC_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS '',
           ACC_NAT_INTNL_CD CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS '',
           ACC_SELF_CLRNCE_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS ' ',
           ACC_HR_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS 'N',
           ACC_CR_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS 'N',
           ACC_FIRST_TIME_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS 'Y',
           ACC_ALT_CURR_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS '',
           ACC_INTL_MAIL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS '',
           ACC_IMP_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS 'N',
           ACC_GAM_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC COMPRESS 'N')
     PRIMARY INDEX ACCOUNT_NUPI ( COU_ID ,ACC_ID );

Runnin the following still gives the same error.

     CREATE JOIN INDEX DEVNAD_T.ACCOUNT_JI2,
         FALLBACK AS
         SELECT  a1.COU_ID, a1.ACC_ID, a1.ACG_ID, n1.NAD_ID
              ,  n1.NAM_NM, n1.COU_ID,n1.ACC_ID, n1.ACG_ID
         FROM      DEVNAD_T."ACCOUNT" a1 JOIN DEVNAD_T.NAD n1
         ON               (
              a1.COU_ID = n1.COU_ID AND
              a1.ACC_ID = n1.ACC_ID AND
              a1.ACG_ID = n1.ACG_ID
                          )
         PRIMARY INDEX (COU_ID, ACC_ID);

The only difference I can see between the columns is that ACC_ID is Compressed on one table but is not (cannot) compressed on the other. No, that's not it, I've just tried it without Compression.

I've got a feeling I'm doing something very silly.

Kind regards,

_______________________
Dave Clough
Database Designer
Express ICS

www.tnt.com


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference