Re: Very strange SQL problem

KPonn wrote: > > Hi, > > We have a table with 4 char columns. The data file has the following format: > 22|John,Doe|333|Ab123456<tab>^M where <tab> is a tab (!!!) and a carriage > return. > > I used "load from ...." command from dbaccess to load into tableA. When I do a > select * from tableA, only first 3 columns show up. when I do select * from> tableA where column4 matches "Ab*" , I get nothing . What's going on here ? For > a data file, does it matter if we have tab at the end or not. If it is a > problem, why wouldn't informix complain instead of loading. We are using ODS > 7.13 UC2 on solaris 2.4. > > Thanks > > Keith My manual (7.1) states that "Two consecutive delimiters define a null field. ... you can place a delimiter immediately before the new-line character that marks the end of each data row. If you omit this delimiter, an error results whenever the last field of a data row is empty.". The manual doesn't state that you need to prefix embedded newlines with a backslash. I don't know what it does with embedded carriage returns (your ^M). A good way to find out what dbload accepts is to do an unload with all the weirdos in and see what you get. These two work together in dbexport and dbimport so they must be compatible. A lot of this confusion would be eliminated if Informix described the delimiter as what it is: a field terminator. It certainly is not a separator (wouldn't be on the end). Delimiter is just vague. Maybe we could even be given the choice of terminator or separator? Also, wouldn't it be nice if dbload could accept comma separated and quoted files as spewed out by many PC programs? -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/