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