SuperTip: CREATING 3-UP LABELS WITH QUERY
                     by Vladimir Volokh, VESOFT
   Published by SUPERGROUP Magazine(English/French), May/June 1990.
                Revised by Eugene and Vladimir, 1991.


  For years we printed mailing labels using QUERY -- it was easy to do.
After a couple of hours a very long string of labels was done with no
problems.  The advantages of using good old QUERY are quite obvious --
it's simple and it's free.  However, it was not clear how to print not
ONE, but THREE labels across the page.  My solution to this problem is
presented in the following stream.

  Some readers might find several interesting things here:

* How to build an MPE file with FLIMIT which depends on number of
  entries in the database?

* How to redirect QUERY output to the disc file?

* How to build the database with one stand-alone data set, each record
  of which contains four string arrays?

* How to use QUERY to load data into the database?

* How to make the size of this base correspond to the size of
  the MPE file?

Any comments are welcome. (1995 note: And we've got some...)

::COMMENT -ABSTRACT:This stream will print labels 3 in a row. To do so
::COMMENT we build a data base with a record of the size of 3 labels,
::COMMENT then we create an ASCII file with the 'regular' labels,
::COMMENT load the ASCII file into this base and then use QUERY again.
::
::COMMENT Commands with :: are executed by STREAMX - which is a part
::COMMENT of VESOFT's SECURITY/3000 package - at submission time.
::COMMENT (without STREAMX it would be less flexible and reliable).
::
::COMMENT -NOTE: Please submit this job being logged-on as DB creator.
::
::COMMENT (history): Created by Vladimir Volokh, VESOFT;    Dec  1989
::COMMENT Printed by SUPERGROUP Magazine(Engl.&French); May/June 1990
::COMMENT Reprinted in VENEWS (VESOFT's newsletter)  4th Quarter 1990
::COMMENT Comments by Jerry Gailey in SUPERGROUP magazine Jan/Feb1991
::COMMENT More comments by Carl Pittman - SUPERGROUP    July/Aug 1991
::COMMENT (some of his suggestions are incorporated in this version)
::COMMENT Current version (3-UP) by Eugene & Vladimir Volokh;Oct 1991
::
::ECHO == First, let's check whether the original (CUST) base exists
::IF NOT FEXISTS("CUST") THEN
::  ECHO
::  ECHO == No CUST.{hpgroup}.{hpaccount} database ...
::  EXIT
::ENDIF
::listf CUST##,2
::ECHO OK. Now, let's check that you are the creator of this base...
::IF VEFINFO("CUST").CREATOR<>"![hpuser]" THEN
::  ECHO
::  ECHO ==&
::The creator of CUST Base is ![VEFINFO("CUST").creator],not ![HPUSER]
::  EXIT
::ENDIF
::ECHO Checking whether QUERY has a lockword and whether you know it..
::ASSIGN querywithlock = "?$LOCKWORD=QUERY.PUB.SYS$?"
::ECHO
::ECHO -Checking whether another LABELS job is running..
::ECHO -If yes, the stream will wait for 30 sec. and try again
::WHILE jscount("labels,{hpuser}.{hpaccount}")>0
::  ECHO - It's there -
::  SHOWJOB job=labels,{hpuser}.{hpaccount}
::  PAUSE 30
::ENDWHILE
::ECHO
::ECHO OK. Now, calculating the size of the LOAD file (to keep labels)
::ECHO     and building it (if OUT OF DISC SPACE we'll see it now...)
::PURGE load
::BUILD LOAD;REC=-32,,F,ASCII;NOCCTL;&
::DISC={(vefinfo("CUST01").dbsetentries+4)*7+1},32,32
::listf load,2
::ECHO
::ECHO Building a new (LABELS) base. Please wait...
::ECHO (again, we will see if anything goes wrong with this).
::RUN DBUTIL.PUB.SYS,PURGE;INPUT="LABELS";STDLIST=$NULL
::run dbschema.pub.sys;stdlist=$null;&
::INPUT="BEGIN DATA BASE LABELS; PASSWORDS: 1;";&
::INPUT="ITEMS:LABEL1,7X30;LABEL2,7X30;LABEL3,7X30;";&
::INPUT="SETS: NAME: LABELS,DETAIL(1/1);";&
::INPUT="ENTRY:LABEL1,  LABEL2,  LABEL3;";&
::INPUT="CAPACITY:{vefinfo("load").flimit/(3*7)};END."
::RUN DBUTIL.PUB.SYS,CREATE;input="LABELS";stdlist=$null
::LISTF LABELS@,2
::ECHO
::ECHO == The following is the item list for your info...
::  RUN {querywithlock}; input="B=CUST";input=";";input="5";&
::  input="FO CUST";input="E"
::
::PURGE FOUT
::BUILD FOUT;rec=-80,,f,ascii;nocctl;cir;disc=3
::FILE FOUT,old
::WHILE vefinfo("FOUT").fsearchstring("ENTRIES ")=0 DO
::  PRINT FOUT;start=-2;end=-2
::  ECHO Enter your QUERY selection for FIND (but without "FIND")
::  PROMPT STRING fnd="    FIND "
::  IF LEN(fnd)=0 THEN
::    EXIT
::  ENDIF
::  ECHO == Checking syntax of your QUERY command ...
::  RUN {querywithlock};input="B=CUST";input=";";input="5";&
::  input="S=CUST";input="F #LIMIT=1;ZIP>0";input='SU {fnd}';&
::  input="E"; stdlist=*FOUT
::ENDWHILE
::PURGE FOUT
::BUILD FOUT;rec=-80,,f,ascii;nocctl;cir;disc=3
::WHILE vefinfo("FOUT").fsearchstring("ENTRIES ")=0 DO
::  PRINT FOUT;start=-2;end=-2
::  ECHO Enter your QUERY selection for SUBSET (but without "SUBSET")
::  PROMPT STRING sub="  SUBSET "
::  ECHO The word ENTRIES gets us out of the loop if sub=RETURN>*FOUT
::  IF LEN(sub)<>0 THEN
::    ECHO == Checking syntax of your QUERY command ...
::    ASSIGN sub='SU '+sub
::    RUN {querywithlock};input="B=CUST";input=";";input="5";&
::    input="S=CUST";input="F #LIMIT=1;ZIP>0";input='{sub}';&
::    input="E"; stdlist=*FOUT
::  ENDIF
::ENDWHILE
::PURGE FOUT
::COMMENT -Building the first label (to save your selection)...
::ASSIGN fnd1=strchange(fnd,'"',"'")
::ASSIGN sub1=strchange(sub,'"',"'")
::ASSIGN line1=strwrite(fnd1[ 0:20]:20)
::ASSIGN line2=strwrite(fnd1[20:28]:28)
::ASSIGN line3=strwrite(fnd1[48:28]:28)
::ASSIGN line4=strwrite(sub1[ 2:18]:20)
::ASSIGN line5=strwrite(sub1[20:28]:28)
::ASSIGN line6=strwrite(sub1[48:28]:28)
::
!JOB LABELS,{hpuser}.{hpaccount},{hpgroup};OUTCLASS=,1;HIPRI;PRI=CS
!FILE QSLIST=LOAD,OLD;DEV=DISC
!FILE QSOUT=$NULL
!TELL {HPJOBNAME},{HPUSER}.{HPACCOUNT} = Creating ASCII file =
!RUN {querywithlock}
 DATA-BASE=CUST
 ;
 5
 SET=CUST
 ASSIGN LOCKOPTION=OFF
 F {fnd}
 {sub}
 OUT=LP
 COMMENT The first label will show your selection and the date
 REPORT; NOPAGE; LINES=0
 S1,COMPANY; S2,ZIP; S3,COUNTRY
 H1,"ADD LABELS",10
 H2,"""",1;H2,"   FIND: ",10;H2,"{line1}",31;H2,"""",32
 H3,"""",1;H3," {line2}",30;H3,"""",32
 H4,"""",1;H4," {line3}",30;H4,"""",32
 H5,"""",1;H5," SUBSET: ",10;H5," {line4}",31;H5,"""",32
 H6,"""",1;H6," {line5}",30;H6,"""",32
 H7,"""",1;H7," {line6}",30;H7,"""",32
 H8,"""",1;H8,"Selected on {hpdatef}",31;H8,"""",32
 D1,"""",1;D1,CONTACT,23;D1,"""",32
 D2,"""",1;D2,TITLE,23  ;D2,"""",32
 D3,"""",1;D3,COMPANY,31;D3,"""",32
 D4,"""",1;D4,ADDRESS1,25;D4,"""",32
 D5,"""",1;D5,ADDRESS2,11;D5,"""",32
 D6,"""",1;D6,CITY,21; D6,STATE,22; D6,ZIP,31;D6,"""",32
 D7,"""",1;D7,COUNTRY,21; D7,"""",32
 TF,"NUMBER OF LABELS",17,SPACE B3
 TF,COMPANY,25,COUNT,SPACE A3;END
 COMMENT = Building the trailer labels =
 F #limit=2;zip>0
 REPORT; NOPAGE; d4,"-",1,space B3,space A3; END
 OUT=TERM
:FILE QSLIST=LABEL3UP;DEV=LP;REC=-136
:TELL {hpuser}.{hpaccount} == Loading data into LABELS data base ==
 DATA-BASE=LABELS
 ;
 3
 XEQ LOAD
 COMMENT == Final step - printing labels ...
 F all label1
 OUT=LP
 REPORT; NOPAGE; LINES=0
 d1,label1(1),30;d1,label2(1),66;d1,label3(1),102
 d2,label1(2),30;d2,label2(2),66;d2,label3(2),102
 d3,label1(3),30;d3,label2(3),66;d3,label3(3),102
 d4,label1(4),30;d4,label2(4),66;d4,label3(4),102
 d5,label1(5),30;d5,label2(5),66;d5,label3(5),102
 d6,label1(6),30;d6,label2(6),66;d6,label3(6),102
 d7,label1(7),30;d7,label2(7),66;d7,label3(7),102
 d8," ",1
 d9," ",1;end
EXIT from QUERY
!PURGE LOAD
!RUN DBUTIL.PUB.SYS,PURGE
 LABELS
!SET STDLIST=DELETE
!EOJ

Go to Adager's index of technical papers