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