Re: [PHP] Can't postgres join tables on varchar fields ? - Mailing list pgsql-novice

Gurudutt,

As I suspected, your problem appears to be one of poor case conversion.
I'm leaving your tabledefs in the e-mail for list purposes; see my
comments at the bottom.

> CREATE TABLE "act_collectiontab" (
>         "collcode" integer DEFAULT
> nextval('act_collectiontab_collcode_s'::text) NOT NULL,
>         "invcode" character varying(25) DEFAULT '' NOT NULL,
>         "custcode" integer DEFAULT '0' NOT NULL,
>         "cableamount" double precision,
>         "internetamount" double precision,
>         "modemrental" double precision,
>         "colldate" date DEFAULT '0001-01-01' NOT NULL,
>         "advancecable" double precision,
>         "advanceinternet" double precision,
>         "modepay" character(1) DEFAULT '' NOT NULL,
>         "chequeddissuedate" date,
>         "chequeddno" character varying(50),
>         "chequeddbankname" character varying(100),
>         "depositbankcode" integer,
>         "otherbankname" character varying(100),
>         "chequebounced" character(1),
>         "validated" character(1) DEFAULT '' NOT NULL,
>         "validateddate" date DEFAULT '0001-01-01' NOT NULL,
>         Constraint "act_collectiontab_pkey" Primary Key ("collcode")
> );

> CREATE TABLE "act_invoicetab" (
>         "invnumber" character varying(25) DEFAULT '' NOT NULL,
>         "custcode" integer DEFAULT '0' NOT NULL,
>         "cableamount" double precision DEFAULT '0',
>         "internetamount" double precision DEFAULT '0',
>         "modemrental" double precision DEFAULT '0',
>         "invgendate" date DEFAULT '0001-01-01' NOT NULL,
>         "invfromdate" date DEFAULT '0001-01-01' NOT NULL,
>         "invtodate" date DEFAULT '0001-01-01' NOT NULL,
>         "internetadditional" double precision DEFAULT '0',
>         "totalamount" double precision DEFAULT '0',
>         "prevcabledues" double precision DEFAULT '0',
>         "previnternetdues" double precision DEFAULT '0',
>         "cableadvadj" double precision DEFAULT '0',
>         "internetadvadj" double precision DEFAULT '0',
>         "notv" integer DEFAULT '0',
>         "cablesubamount" double precision DEFAULT '0',
>         "nocomp" integer DEFAULT '0',
>         "internetacccharges" double precision DEFAULT '0',
>         "incrcode" integer,
>         Constraint "act_invoicetab_pkey" Primary Key ("invnumber",
> "custcode")
> );

> COPY "act_invoicetab"  FROM stdin;
> 2001Aug1        1       110     0       0       2001-09-05
> 2001-08-01      2001-08-31      0       110     0       0       0
> 0       1       110     0       0       10001426
> 2001Aug10       10      160     0       0       2001-09-05
> 2001-08-01      2001-08-31      0       160     0       0       0
> 0       1       160     0       0       10001435
> 2001Aug100      100     160     0       0       2001-09-05
> 2001-08-01      2001-08-31      0       160     0       0       0
> 0       1       160     0       0       10001517
> 2001Aug101      101     160     0       0       2001-09-05
> 2001-08-01      2001-08-31      0       160     0       0       0
> 0       1       160     0       0       10001518
> 2001Aug102      102     160     0       0       2001-09-05
> 2001-08-01      2001-08-31      0       160     0       0       0
> 0       1       160     0       0       10001519
> 2001Aug103      103     160     0       0       2001-09-05

> COPY "act_collectiontab"  FROM stdin;
> 1       2001jun1034     1034    120     0       0       2001-08-01
> 0       0       C       0001-01-01                      1
> N       N       0001-01-01
> 2       2001jun1194     1194    120     0       0       2001-08-02
> 0       0       C       0001-01-01                      1
> N       N       0001-01-01
> 3       2001jun1189     1189    120     0       0       2001-08-02
> 0       0       C       0001-01-01                      1
> N       N       0001-01-01
> 4       2001jun1174     1174    120     0       0       2001-08-02
> 0       0       C       0001-01-01                      1
> N       N       0001-01-01
> 5       2001jun1175     1175    120     0       0       2001-08-02
> 0       0       C       0001-01-01                      1
> N       N       0001-01-01

So here's something I noticed immediately:
Invoicetab: 2001Aug110
Collectiontabl: 2001jun1175
The two tables are using a different case for the month name in the
invoice number.  As an example:

SELECT '2001Aug100' = '2001aug110'
---------
f

Also, the table columns you posted above do not quite match the query
you e-mailed earlier.

-Josh




______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco