1 / 16

Everyday Problem and a Suggested Solution

Everyday Problem and a Suggested Solution. Problem: Starting a New PQL Program We need another new PQL Retrieval We start writing the same 20 lines of code We add a few Variations Solution: Make a Menu Option or Button That writes the skeleton

misae
Download Presentation

Everyday Problem and a Suggested Solution

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Everyday Problem and a Suggested Solution • Problem: Starting a New PQL Program • We need another new PQL Retrieval • We start writing the same 20 lines of code • We add a few Variations • Solution: Make a Menu Option or Button • That writes the skeleton • That has the bells and whistles you conveniently forgot

  2. What do we want the button to do? • Create Starter PQL Code • That is Efficient • That has the tricky code already written • Commonly Used User Prompts • Date Booleans • Tabulate Structure • Give us some options • Filename or Member name • Output (Report) Filename • Prompt for Common Parameters • Post Processing Options (e.g. Print, Display)

  3. 13 Tricks at a Glance

  4. Generate PQL Program Automatically Edit PQL & make it SIR Default File Run Many Programs in one Procedure Prompt for Date Range (allow human formats) Abort Complex Procedure Midstream Disconnect & Reconnect Master for Speed Use "Template Code" for PQL Procedures Post Process PQL Output (only if PQL Runs OK) EDIT Output File Print Output File Display as Web Page Start other Software that uses PQL Output (e.g. SPSS) Do it all with a Customized Button The Baker's Dozen Tricks

  5. 1. Generate a PQL Program • Create a Template of the PQL (TRICKS.BASE_PQL) • Write a PQL Program, a VisualPQL Dialog (TRICKS.RUNPMT) that: • Prompts for Output File Name • Prompts for Other Customizations • Reads PQL Template • Inserts Customizations where Necessary • Writes all to the New File when Done

  6. 2. Automatically Edit Generated PQL & Make it the SIR Default Edit File • After the "END PROGRAM" of Generator Program, use the DBMS Any-Time Command: EDITFILE "file_name"PROGRAM. WRITE(NEW.PQL)"c This is my New PQL Program"END PROGRAMEDITFILE "NEW.PQL" • In the Generator Program, set Global FILENAME with name of PQL File. SIR uses this global when you invoke the File Editor.COMPUTE FNAME="NEW.PQL"COMPUTE N = GLOBALS("FILENAME",FNAME)

  7. 3. Put Many Programs and Procedures in a Single Command Set • All DBMS commands & command sets allowed • PQL • DBMS Anytime Commands • Utilities • Schema Definition • Compiler Controls (CIF) • Later Commands Use Results of Earlier Ones • Have to Make Sure all Possibilities are handled

  8. 4. Prompt for Dates & Date Ranges (allow human date formats and variations) • Single most frequently used booleans are to limit retrievals by date or date ranges. Create a standard way to do this (TRICKS.DATERANG) • Since Y2K we've formatted our dates with 4 digits for the year – makes for lots of typing • Users would prefer to enter strings like"1 1 2" for "Jan 1 2002" • A PQL program can interpret these, expand them and put them in globals for later use (TRICKS.DATEEDIT)

  9. 5. Abort Procedures Midstream • In Complex Procedures, user may request an abort • How to Stop things Midstream? • In First Program, set an "Abort" global • After Program, use Conditional Compilation ( CIF ) • FINISH command stops everythingPROGRAM. compute n = globals("ABORT","YES")END PROGRAMc this comment is very importantCIF EQ "<ABORT>" , "YES"FINISHCIF END

  10. 6. Disconnect & ReConnect Master to Speed Up Retrievals (NoUpdate) • Check if Master is Connected with SYSTEM(38) • Set a Global Variable to Remember Setting • DBMS Command CLEAR MASTER Disconnects PROGRAM | TRICKS.MSTCLEAR . COMPUTE MST_STAT = SYSTEM(38) . COMPUTE X=GLOBALN('MSTSTAT',MST_STAT) . IF(MST_STAT = 1)EXECUTE DBMS 'CLEAR MASTER' END PROGRAM • ReConnect with: PROGRAM | TRICKS.MSTSET . COMPUTE MST_STAT = <MSTSTAT> . IF(MST_STAT = 1)EXECUTE DBMS 'SET MASTER' END PROGRAM

  11. 7. Use Template Code for PQL Procedures • Figure out Tricky Procedures Once • Have the Program Generator Insert Needed Code • You modify the Template, e.g. change variables

  12. 8. Post Process PQL Output (only if PQL Program ran Successfully) • Anything you typically do after you run a PQL program is a candidate for Post Processing Options • Edit, Print, Modify, Use with other Software • But you Only want to Post Process if PQL was OK • TERROR system global has count of errors • Conditionally Compile the Post Processing Code

  13. 9. Edit the PQL Output File 10. Print the PQL Output File • Set Filenames in a Standard Global (e.g. REPFILE) • Use Global in Procedure Definitions • Use DBMS EDITFILE command to Edit • Use DBMS PRINT FILE command to Print GLOBAL REPFILE = GENDER.REP PROGRAM . PROCESS REC 1 . GET VARS GENDER . PERFORM PROCS . END REC FREQUENCIES INTEGER = GENDER(1,2) / FILENAME="<REPFILE>" END PROGRAM c EDITFILE "<REPFILE>" PRINT FILE "<REPFILE>"

  14. 11. Format & Display as a Web Page (HTML) • Use HTML Option on some Commands like TABULATE • Post Process Output and Add HTML commands (DVPROCF.DVTOOLS.MKHTMTX, DVPROCF.DVTOOLS.MKHTMTAB)

  15. 12. Start other Software Using Output • SIR Interface Procedures: SPSS, SAS, etc GLOBAL REPFILE = GENDER.REP PROGRAM . PROCESS REC EMPLOYEE . GET VARS ALL . PERFORM PROCS . END REC SPSS SAVE FILE FILENAME="<REPFILE>" / VARIABLES = ALL END PROGRAM c PROGRAM . DISPLAY YESNOBOX 'Start SPSS?' RESPONSE YN . IFNOT(YN=1)STOP . PQL ESCAPE ' "C:\Program Files\SPSS\SPSSWIN.EXE" <repfile>' . END PROGRAM

  16. 13. Pull it All Together with a Custom Tool Bar Button • Several Methods to Add a Button (or Menu Item) • Copy, Edit & ReCompile SYSPROC.SYSTEM.MAINMENU • Use DVMenu Dialog Tool • To Edit MainMenu • Declare Integer Variable as a Menu Item Alias • Assign a Value not Given to Other Menu Item Aliases • Define Button:TBARITEM MKPQL , "MKPQLPIC","PQL Starter",0 • Define what to do if item is selectedIF(ID=MKPQL)execute dbms "CALL TRICKS.PQLSTART" • Save, Run, Exit SIR, Start SIR again to see changes

More Related