John W. Colby
jcolby at colbyconsulting.com
Fri Feb 7 15:56:01 CST 2003
I ran into something the other day that I thought I'd share. The situation: I get a database from an insurance company with billing information. The first shot at this, they sent the SSN as a long, which means that the leading zeros had already been stripped out by the time I got it. In order to repair the damage, I built up a query on top of the table, with a format function call to "format" back in the leading zeros if they were missing. Later on, I tried to do an outer join on this "formatted" field to a string field in my table. ALL of the records dropped out, even though most did exist in both tables. IOW, once run through the format, the resulting data did not match the real string data in my table - EVER. Pretty strange I thought. The programmer at the insurance company ended up formatting the data as a string and restoring the missing zeros before I ever got the data, so I was able to strip out the format statement, whereupon the atomic query started working. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Arthur Fuller Sent: Friday, February 07, 2003 4:43 PM To: accessd at databaseadvisors.com Cc: 'Peter Brawley' Subject: [AccessD] Missing SubQueries in MySQL 1. Que cancelled the book. Peter and I are posting chapters gradually to our website. See http://www.artfulsoftware.com 2. Download the current gamma, whose support for these constructs is... well, on the way :-) 3. Alternatively, use Access as the FE, MyODBC as the bridge and MySQL as the BE, wherever it resides and atop whatever platform. Save named queries in the standard Access way, building atomic then molecular queries.