copy table into file trim squeeze

#11

On Sep 1, 2:03*am, "Paul White" <shift7soluti... (AT) gmail (DOT) com> wrote:

Quote:

Hi Seldom.

Yes, it would be a nice feature. I think the problem is the c0 or char(0)
format specifiers. They are interpreted as char which means pad with spaces
to the length of the field. I use external tools like awk and sed to trimup
the extra spaces. Try this:

declare global temporary table tt as
select trim(c1) + ',' + trim(c2) + ',' + trim(c3) + ',' as col1
from t on commit preserve rows with norecovery
\p\g
copy session.tt(col1=c0nl) into 'b.dat'
\p\g

and then
cut -f1-3 -d, b.dat > c.dat

or
FOR /F "tokens=1,2,3 delims=," %i in (b.dat) do @echo %i,%j,%k>>c.dat

...

Well, c2 is integer, so a trim() on it won't work.
Not sure why you are using the ',' delimiter also on the last column
(and then removing it by OS commands).
Why not directly add the newline (0x0D0A on Windows, 0x0A on Unix)
instead
(and not using "nl" it in the copy format), e.g.:

declare global temporary table session.tt as
select trim(c1) + ',' + varchar(c2) + ',' + trim(c3) + X'0d0a' as col1
from t on commit preserve rows with norecovery;
copy session.tt(col1=text(0)) into 'b.dat' ;

Bodo.

#12

Well done Martin...a new documentation bug.

I've just confirmed that vchar(0) works as you've described in Ingres10, but the SQL Reference guide has no mention of it.

Do you want to raise a documentation bug?

Marty

-----Original Message-----
From: Martin Crouch [mailto:spamspamspam (AT) spaml (DOT) com]
Sent: 01 September 2011 10:15
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] copy table into file trim squeeze

Try this:

copy session.tt(col1=vchar(0)comma,
col2=vchar(0)comma,
col3=vchar(0)nl) into 'b.dat'
\p\g

The vchar specification trims white space off both char and vchar
fields. It's very useful.
I don't think it's documented however.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

#13

On Sep 1, 9:41*am, Ingres Forums <info-
ing... (AT) kettleriverconsulting (DOT) com> wrote:

Quote:

Hmm.

It's useful to know about tricks with *sql* and the -S and -v flags
(and \trim for that matter) because you don't always want the data for a
database, but I certainly would not recommend learning or using them
before mastering COPY...INTO. COPY...INTO is quite carefully designed to
prevent problems and you subvert it at your peril.

--
rhann
------------------------------------------------------------------------
rhann's Profile:http://community.ingres.com/forum/member.php?userid=131
View this thread:http://community.ingres.com/forum/sh...ad.php?t=13849

Guys

I just wanted to say that the text(0) in the copy out worked perfectly
for me.
It worked for both char and varchar.

Thanks.

#14

trim(c2)

caught me out.
It was supposed to read ..varchar(c2).. but I changed my example at the last
moment.

vchar() will do nicely thanks Martin. I can now stop relying on those
somewhat complicated external tools. (but I don't mind a bit of awk here and
there.

Paul

#15

Martin Crouch wrote:

Quote:

Try this:

copy session.tt(col1=vchar(0)comma,
col2=vchar(0)comma,
col3=vchar(0)nl) into 'b.dat'
\p\g

The vchar specification trims white space off both char and vchar
fields. It's very useful.
I don't think it's documented however.

Another easter egg, and potentially a useful one! But I'll make my
earlier quibble more explicit: trimming blanks from VARCHARs risks
violating referential integrity. Don't do it unless you *know* it's
100% safe.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

#16

vchar is identical to text in this context - it is a datatype alias for
text from quel. Easily confused with varchar but definitely the same as
text.
Regards,
Ian

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Crouch
Sent: 01 September 2011 10:15
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] copy table into file trim squeeze

Try this:

copy session.tt(col1=vchar(0)comma,
col2=vchar(0)comma,
col3=vchar(0)nl) into 'b.dat'
\p\g

The vchar specification trims white space off both char and vchar
fields. It's very useful.
I don't think it's documented however.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

copy table into file trim squeeze

comp.databases.ingres comp.databases.ingres

Re: copy table into file trim squeeze - 09-01-2011 , 04:33 AM

Re: [Info-Ingres] copy table into file trim squeeze - 09-01-2011 , 05:07 AM

Re: copy table into file trim squeeze - 09-01-2011 , 05:36 AM

Re: [Info-Ingres] copy table into file trim squeeze - 09-01-2011 , 05:56 AM

Re: copy table into file trim squeeze - 09-01-2011 , 06:09 AM

Re: [Info-Ingres] copy table into file trim squeeze - 09-01-2011 , 07:27 AM