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

Reply via email to