Re: [PHP] Can't postgres join tables on varchar fields ?

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco