Thanks, David. I can read C, but I don't know how to compile it. :( Just
not a language I have learned.
On Fri, Oct 14, 2016 at 10:22 AM, David Raymond <david.raym...@tomtom.com>
wrote:
> If you're using the CLI exclusively you can go into shell.c and get rid of
> the double spaces. In the copy I'm looking at that's bundled with the
> 3.14.2 amalgamation it looks like there're 5 lines to alter and you'll be
> good.
>
> They're in
> static int shell_callback(
> ...
> case MODE_Column: {
> ...
> Lines 1007, 1010, 1026 look like they deal with displaying the header line
> Lines 1050 and 1054 deal with each row.
>
> All of those lines are the end of a utf8_printf( call and have...
>
> i==nArg-1 ? rowSep : " ");
>
> I tried turning the 2 spaces there into "" recompiled it, and it seems to
> display the way you're wanting it to.
>
> Of course since I don't know C, by doing that I have no clue what else I
> might just have broken which relied on the old format, but hey :)
>
>
> -----Original Message-----
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Don V Nielsen
> Sent: Friday, October 14, 2016 7:39 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] .mode column .width .separator
>
> Thank you, Gunter. I will investigate virtual tables; it's a bit over my
> head at the moment.
>
> My final implementation was to route the select via .output, employ a CTE
> to prepare the data, use printf to setup the record format, and then select
> the data from the CTE. Again, I do not enjoy the redundancy of the two
> selects, but it gets the job done. A script will handle the statement in
> the future.
>
>
> with pre_process as (
> select
> ... columns blah blah ...
> case when piecerate in ('AF','RF') and version_id = '81' then '81' else
> segment end as segment,
> ... columns blah blah ...
> from address_txt
> )
> select
> printf(
>
> '%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-
> 10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-
> 50s%-50s%-25s%-2s%-8s%-3s%-6s%-9s',
> ... columns, blah blah ...
> )
> from pre_process
> ;
>
>
> Works like a champ.
> Have a good one.
>
> On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:
>
> > You can eliminate separators by using
> > .mode list
> > .sepa ""
> >
> > But then you need to format your values to the correct widths for the
> > record description. Text processing is not the primary domain of SQLite
> and
> > is best left to the presentation layer.
> >
> > Alternatively you may consider writing a virtual table module to do the
> > processing for you. Semantics could be similar to:
> >
> > CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '<fileformat>',
> > '<filename>', [<header_data>]); --> open filename, prepare to write in
> > specified format, write header
> > INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
> > DROP TABLE mainframe_file; -> close the file, write trailer record if
> > required
> >
> > -----Ursprüngliche Nachricht-----
> > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Don V Nielsen
> > Gesendet: Donnerstag, 13. Oktober 2016 20:51
> > An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> > sqlite.org>
> > Betreff: [sqlite] .mode column .width .separator
> >
> > I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
> > with mainframe data in a fixed format.
> >
> > I would like to use .mode column to create my output text file in a fixed
> > layout. I set all my column widths using .width. I then output my data
> to a
> > file. Unfortunately, there are two blanks separating each column, space I
> > don't want to be there. The .separator command does not provide any
> > mechanism for turning it off. Is there a way?
> >
> > I realize there is a printf function available. However, it appears that
> > output values must come from a table column, where as below, I could use
> > case statements in the sql select of the data.
> >
> > Any suggestions? I think I am overlooking a .separator option that says
> > "don't put spaces between output columns". I assume that John McKown has
> > faced this already, given his mainframe pedigree.
> >
> > Thanks for your time,
> > dvn
> >
> >
> >
> > Sample output:
> > "H 0NZOX0001687395 83501 5827 "...
> >
> > .mode column
> > .width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50
> 50
> > 25 2 8 003 006 009
> > .output vo_pwprep.txt
> > select
> > recid,
> > z_num,
> > zip,
> > zip4,
> > dpbc,
> > ckdig,
> > cart,
> > lot,
> > lot_order,
> > walk_seq,
> > walk_seq_bic,
> > case when piecerate in ('AF','RF') and version_id = '81' then '81' else
> > segment end as segment,
> > version_id,
> > message,
> > seed,
> > seed_id,
> > seed_key,
> > planet,
> > ocr_acr,
> > priority,
> > keycode,
> > custno,
> > name,
> > title,
> > firm_id,
> > addr1,
> > addr2,
> > city,
> > state,
> > seq_number,
> > srvc_type,
> > imb_mid,
> > imb_serial
> > from address_txt
> > ;
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___________________________________________
> > Gunter Hick
> > Software Engineer
> > Scientific Games International GmbH
> > FN 157284 a, HG Wien
> > Klitschgasse 2-4, A-1130 Vienna, Austria
> > Tel: +43 1 80100 0
> > E-Mail: h...@scigames.at
> >
> > This communication (including any attachments) is intended for the use of
> > the intended recipient(s) only and may contain information that is
> > confidential, privileged or legally protected. Any unauthorized use or
> > dissemination of this communication is strictly prohibited. If you have
> > received this communication in error, please immediately notify the
> sender
> > by return e-mail message and delete all copies of the original
> > communication. Thank you for your cooperation.
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
