Data cut off

[bdc101]

I am running a query in Navicat that is pulling data from a MySQL database. The query pulls all the data, but it is cutting off fields. For instance, an address that is "123 Anystreet" in the db will say "123 Anys". I ahve never seen this happen before. Does anyone know the cause of this? This query takes about 30 mins to run and pulls over 2000 records. I do not know if that has anything to do with the problem. Here is the query:

SELECT c.cust, c.coname,
if (c.mailaddr='0' OR c.mailaddr IS NULL OR c.mailaddr="","Not Avail.",c.mailaddr) as MailAddr,
if (c.City='0' OR c.City IS NULL OR c.City="","Not Avail.",c.City) as City,
if (c.StateCD='0' OR c.StateCD IS NULL OR c.StateCD="","Not Avail.",c.StateCD) as State,
if (c.ZipCode='0' OR c.ZipCode IS NULL or c.ZipCode="","Not Avail.",c.ZipCode) as ZipCode,
if (c.ShipAddr='0' OR c.ShipAddr IS NULL OR c.ShipAddr="","Not Avail.",c.ShipAddr) as ShipAddr,
if (c.ShipCity='0' OR c.ShipCity IS NULL OR c.ShipCity="","Not Avail.",c.ShipCity) as ShipCity,
if (c.ShipSt='0' OR c.ShipSt IS NULL OR c.ShipSt="","Not Avail.",c.ShipSt) as ShipSt,
if (c.ShipZip='0' OR c.ShipZip IS NULL OR c.ShipZip="","Not Avail.",c.ShipZip) as ShipZip,
if (c.phone='0' OR c.phone IS NULL OR c.phone="","Not Avail.",c.phone) as Phone,
if(c.fax='0' OR c.fax IS NULL OR c.fax="","Not Avail.",c.fax) as Fax,
if (c.flname='0' OR c.flname IS NULL OR c.flname="","Not Avail.",c.flname) as FLName,
if(c.title = '0' OR c.title IS NULL OR c.title="","Not Avail.",c.title) as Title,
if (c.flname2='0' OR c.flname2 IS NULL OR c.flname2="","Not Avail.",c.flname2) as FLName2,
if(c.title2='0' OR c.title2 IS NULL OR c.title2="","Not Avail.",c.title2) as Title2,
if (c.email='0' OR c.email IS NULL OR c.email="","Not Avail.",c.email) as Email,
(SELECT ifnull(sum(ifnull(s.netamt,0)),0) FROM quotes as s WHERE YEAR(s.QuotDate2) = '2003' AND s.cust=c.cust) as Quotes2003,
(SELECT ifnull(sum(ifnull(i.netamt,0)),0) FROM invoice as i WHERE YEAR(i.dtordrec2) = '2003' AND i.cust=c.cust) as Orders2003,
(SELECT ifnull(sum(ifnull(s.netamt,0)),0) FROM quotes as s WHERE YEAR(s.QuotDate2) = '2004' AND s.cust=c.cust) as Quotes2004,
(SELECT ifnull(sum(ifnull(i.netamt,0)),0) FROM invoice as i WHERE YEAR(i.dtordrec2) = '2004' AND i.cust=c.cust) as Orders2004,
(SELECT ifnull(sum(ifnull(q.totalAMT,0)-ifnull(q.taxAMT,0)-ifnull(q.shipAMT,0)),0) FROM quote as q WHERE YEAR(q.date)='2005' AND q.cust=c.cust) as Quotes2005,
(SELECT ifnull(sum(ifnull(i.netamt,0)),0) FROM invoice as i WHERE YEAR(i.dtordrec2) = '2005' AND i.cust=c.cust) as RBaseOrders2005,
(SELECT ifnull(sum(ifnull(o.totalAMT,0)-ifnull(o.taxAMT,0)-ifnull(o.shipAMT,0)),0) FROM orderdata as o WHERE YEAR(o.date)='2005' AND o.cust=c.cust) as NewOSOrders2005
FROM customer as c
WHERE c.StateCD = 'PA'
AND c.cust <> '38282'
AND c.cust <> '36034'
AND c.zipcode NOT LIKE ('165%')
AND c.zipcode <> '16401'
AND c.zipcode <> '16403'
AND c.zipcode <> '16407'
AND c.zipcode <> '16410'
AND c.zipcode <> '16411'
AND c.zipcode <> '16412'
AND c.zipcode <> '16413'
AND c.zipcode <> '16415'
AND c.zipcode <> '16417'
AND c.zipcode <> '16421'
AND c.zipcode <> '16423'
AND c.zipcode <> '16426'
AND c.zipcode <> '16427'
AND c.zipcode <> '16428'
AND c.zipcode <> '16430'
AND c.zipcode <> '16434'
AND c.zipcode <> '16435'
AND c.zipcode <> '16438'
AND c.zipcode <> '16441'
AND c.zipcode <> '16442'
AND c.zipcode <> '16443'
AND c.zipcode <> '16444'
AND c.zipcode <> '16475'
GROUP BY c.cust
ORDER BY c.cust

Any help would be greatly appreciated.

 

[dalchri]

Try CONCAT(FieldName) in place of the actual field. Although I did not have truncation issues, I have had type coersion issues using some MS technologies with MyODBC. I believe that the CAST function may also be of use to you for experimentation:

http://dev.mysql.com/doc/mysql/en/cast-functions.html