Variable List Program ("VARLIST.SQL") ---------------------------------------------------- Description: The variable list program processes a list of "included" questions from the compression question table. Information from the manager question, record type, topics and sections tables is combined with the frequency total and count tables to produce a master list of questions and related information which will be used to create Investigator versions of the data for release. Some fields are manipulated or cleaned before being included in this table. The "&n" symbol used below signifies a "substitution parameter" within the varlist program. Tables used (program=VARLIST.SQL): -- &8 = COMPRESSION QNAME TABLE Compression table. -- &9 = QUESTION TABLE Manager question table. -- &10 = FREQ TOTAL TABLE Frequency "Total" table. -- &11 = FREQ COUNT TABLE Frequency "Count" table. -- &12 = RECORD TYPE TABLE Manager record type table (table of "areas of interest"). -- &13 = TOPICS TABLE Manager topics table (table of topics for manager question numbers). -- &14 = SECTION TABLE Manager sections table (table of section names). Option Parameters (program=VARLIST.SQL): -- &4 = NOUSE_NEW_RNUMS/USE_NEW_RNUMS Use existing reference numbers from the manager question table, or, disregard existing reference numbers and assign new ones. -- &5 = YEAR_ON_SASTITLES/NOYEAR_ON_SASTITLES Add the 4 digit year to the end of the short (sas) titles, or, leave off (some instruments already have a year on the sas titles). -- &6 = The letter prefix to use for the reference numbers (R,C, etc). -- &7 = The starting point for new reference numbers (1, 12000, etc). Tables created (program=VARLIST.SQL): -- &1 = VAR_LIST_ERRORS This table contains the error messages produced during the programs execution. -- &2 = VAR_LIST This table is the main output table of the varlist program. It contains one row for each data item which will appear in a "private" Investigator version. -- &3 = VAR_LOCATION This table is created for use by the program which is run to create Investigator versions. VARLIST Oracle description: SQLWKS> desc var_list_1998 Column Name Null? Type ------------------------------ -------- ---- MGR_QNAME VARCHAR2(75) The name of the manager question this item links to. DATA_QNAME VARCHAR2(75) The name in the data table this item links to. LOOP_QNAME VARCHAR2(75) The name in the codebook table this item links to. QYEAR VARCHAR2(4) The year from the manager question table. RNUM VARCHAR2(9) The reference number. SASNAME VARCHAR2(8) The sas name. SASTITLE VARCHAR2(40) The sas title. KWIC_TITLE VARCHAR2(100) The kwic title (80 char description). CDBKSTART NUMBER(10) The starting line number in the codebook (filled in by codebook program). CDBKLINES NUMBER(10) The number of lines for this item in the codebook (filled in by codebook program). RTNAME VARCHAR2(35) The area on interest. SECURITY VARCHAR2(1) The security (V=private, B=public, etc). MINIMUM NUMBER(10) The absolute minimum in the data. MAXIMUM NUMBER(10) The absolute maximum in the data. VALID_MINIMUM NUMBER(10) The "valid" minimum (disregarding -5,-4,-3,-2,-1 values). VALID_MAXIMUM NUMBER(10) The "valid" maximum (disregarding -5,-4,-3,-2,-1 values). VALIDN NUMBER(10) The count of total responses (column name is misleading). ALPHA NUMBER(4) The maximum length of character responses (0 if item is numeric). COUNTER NUMBER(10) Sequential counter used by codebook program. PAPER_QNAME VARCHAR2(30) Paper-and-pencil qname for non-capi rounds (null if not applicable). SURVEY_ORDER VARCHAR2(50) Sequential counter used to order questions in "survey" order instead of reference number order (which is the default). Can be used in an SQL dump of the codebook table. Sample rows from VAR_LIST table: SQLWKS> select * from var_list_1998 2> order by rnum 3> MGR_QNAME DATA_QNAME LOOP_QNAME QYEA RNUM SASNAME SASTITLE KWIC_TITLE CDBKSTART CDBKLINES RTNAME S MINIMUM MAXIMUM VALID_MINI VALID_MAXI VALIDN ALPHA COUNTER PAPER_QNAME SURVEY_ORDER ----------------------------- ------------------------------- ----------------------------- ---- --------- -------- ---------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------------------------------- - ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------- -------------------- YINTDATE YINTDATE~D YINTDATE 1998 R14900.00 R1490000 CHK CURR INT DATE BEG SURVEY 1998 CHK CURRENT INTERVIEW DATE TO BEGIN SURVEY 0 31 MACHINE CHECK B 1 31 1 31 8386 0 1 YINTDATE YINTDATE~M YINTDATE 1998 R14900.01 R1490001 CHK CURR INT DATE BEG SURVEY 1998 CHK CURRENT INTERVIEW DATE TO BEGIN SURVEY 0 31 MACHINE CHECK B 1 12 1 12 8386 0 2 YINTDATE YINTDATE~Y YINTDATE 1998 R14900.02 R1490002 CHK CURR INT DATE BEG SURVEY 1998 CHK CURRENT INTERVIEW DATE TO BEGIN SURVEY 0 31 MACHINE CHECK B 1994 1999 1994 1999 8386 0 3 YHHI-400 YHHI-400 YHHI-400 1998 R14901.00 R1490100 CNT HH MEMBS AT DLI 1998 COUNT OF HH MEMBERS AT DLI 31 37 HOUSEHOLD CHARACTERISTICS B 0 23 0 23 8386 0 4 YHHI-3500 YHHI-3500 YHHI-3500 1998 R14902.00 R1490200 VFY ADDRESS FROM DLI 1998 VERIFY ADDRESS FROM DLI 68 20 YOUTH HISTORY B 0 1 0 1 8386 0 5 YHHI-3600 YHHI-3600 YHHI-3600 1998 R14903.00 R1490300 # DIFF ADDRESS R LIVE SDLI 1998 # DIFFERENT ADDRESSES R LIVED AT SINCE DLI 88 35 YOUTH HISTORY B -2 15 0 15 2849 0 6 YHHI-3700 YHHI-3700 YHHI-3700 1998 R14904.00 R1490400 R MOVE STATE/CITY/CNTY SDLI 1998 HAS R MOVED FROM STATE, CITY OR COUNTY TO ANOTHER SINCE DLI? 123 18 YOUTH HISTORY B 0 1 0 1 2849 0 7 YHHI-4100 YHHI-4100.01~M YHHI-4100.01 1998 R14905.00 R1490500 MO/YR R MOVE SDLI L1 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 01 141 29 YOUTH HISTORY B -2 12 1 12 1087 0 8 YHHI-4100 YHHI-4100.01~Y YHHI-4100.01 1998 R14905.01 R1490501 MO/YR R MOVE SDLI L1 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 01 141 29 YOUTH HISTORY B -2 1999 1997 1999 1087 0 9 YHHI-4100 YHHI-4100.02~M YHHI-4100.02 1998 R14906.00 R1490600 MO/YR R MOVE SDLI L2 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 02 170 29 YOUTH HISTORY B -2 12 1 12 307 0 10 YHHI-4100 YHHI-4100.02~Y YHHI-4100.02 1998 R14906.01 R1490601 MO/YR R MOVE SDLI L2 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 02 170 29 YOUTH HISTORY B 1997 1999 1997 1999 307 0 11 YHHI-4100 YHHI-4100.03~M YHHI-4100.03 1998 R14907.00 R1490700 MO/YR R MOVE SDLI L3 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 03 199 29 YOUTH HISTORY B 1 12 1 12 71 0 12 YHHI-4100 YHHI-4100.03~Y YHHI-4100.03 1998 R14907.01 R1490701 MO/YR R MOVE SDLI L3 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 03 199 29 YOUTH HISTORY B 1997 1999 1997 1999 71 0 13 YHHI-4100 YHHI-4100.04~M YHHI-4100.04 1998 R14908.00 R1490800 MO/YR R MOVE SDLI L4 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 04 228 29 YOUTH HISTORY B 1 12 1 12 13 0 14 YHHI-4100 YHHI-4100.04~Y YHHI-4100.04 1998 R14908.01 R1490801 MO/YR R MOVE SDLI L4 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 04 228 29 YOUTH HISTORY B 1998 1999 1998 1999 13 0 15 YHHI-4100 YHHI-4100.05~M YHHI-4100.05 1998 R14909.00 R1490900 MO/YR R MOVE SDLI L5 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 05 257 29 YOUTH HISTORY B 1 11 1 11 2 0 16 YHHI-4100 YHHI-4100.05~Y YHHI-4100.05 1998 R14909.01 R1490901 MO/YR R MOVE SDLI L5 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 05 257 29 YOUTH HISTORY B 1998 1998 1998 1998 2 0 17 YHHI-4100 YHHI-4100.06~M YHHI-4100.06 1998 R14910.00 R1491000 MO/YR R MOVE SDLI L6 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 06 286 29 YOUTH HISTORY B 1 1 1 1 1 0 18 YHHI-4100 YHHI-4100.06~Y YHHI-4100.06 1998 R14910.01 R1491001 MO/YR R MOVE SDLI L6 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 06 286 29 YOUTH HISTORY B 1999 1999 1999 1999 1 0 19 YHHI-4100 YHHI-4100.07~M YHHI-4100.07 1998 R14911.00 R1491100 MO/YR R MOVE SDLI L7 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 07 315 29 YOUTH HISTORY B 3 3 3 3 1 0 20 YHHI-4100 YHHI-4100.07~Y YHHI-4100.07 1998 R14911.01 R1491101 MO/YR R MOVE SDLI L7 1998 WHAT MONTH/YEAR DID R MOVE SINCE DLI? MOVE 07 315 29 YOUTH HISTORY B 1999 1999 1999 1999 1 0 21 YHHI-4200 YHHI-4200.01 YHHI-4200.01 1998 R14912.00 R1491200 R MOVE OTH STATE/CITY/CTY L1 1998 R MOVE TO OTHER STATE, CITY, OR COUNTY? MOVE 01 344 18 YOUTH HISTORY B 0 1 0 1 1087 0 22 YHHI-4200 YHHI-4200.02 YHHI-4200.02 1998 R14913.00 R1491300 R MOVE OTH STATE/CITY/CTY L2 1998 R MOVE TO OTHER STATE, CITY, OR COUNTY? MOVE 02 362 18 YOUTH HISTORY B 0 1 0 1 307 0 23 YHHI-4200 YHHI-4200.03 YHHI-4200.03 1998 R14914.00 R1491400 R MOVE OTH STATE/CITY/CTY L3 1998 R MOVE TO OTHER STATE, CITY, OR COUNTY? MOVE 03 380 18 YOUTH HISTORY B 0 1 0 1 71 0 24 YHHI-4200 YHHI-4200.04 YHHI-4200.04 1998 R14915.00 R1491500 R MOVE OTH STATE/CITY/CTY L4 1998 R MOVE TO OTHER STATE, CITY, OR COUNTY? MOVE 04 398 18 YOUTH HISTORY B 0 1 0 1 13 0 25 YHHI-4200 YHHI-4200.05 YHHI-4200.05 1998 R14916.00 R1491600 R MOVE OTH STATE/CITY/CTY L5 1998 R MOVE TO OTHER STATE, CITY, OR COUNTY? MOVE 05 416 18 YOUTH HISTORY B 0 1 0 1 2 0 26 YHHI-4200 YHHI-4200.06 YHHI-4200.06 1998 R14917.00 R1491700 R MOVE OTH STATE/CITY/CTY L6 1998 R MOVE TO OTHER STATE, CITY, OR COUNTY? MOVE 06 434 18 YOUTH HISTORY B 1 1 1 1 1 0 27 YHHI-4200 YHHI-4200.07 YHHI-4200.07 1998 R14918.00 R1491800 R MOVE OTH STATE/CITY/CTY L7 1998 R MOVE TO OTHER STATE, CITY, OR COUNTY? MOVE 07 452 18 YOUTH HISTORY B 0 0 0 0 1 0 28 YHHI-4300-LOOP-END YHHI-4300-LOOP-END.01 YHHI-4300-LOOP-END.01 1998 R14919.00 R1491900 END CHNGE ADDRESS SDLI LP L1 1998 END CHANGE OF ADDRESS SINCE DLI LOOP 470 13 HOUSEHOLD CHARACTERISTICS B 0 1 0 1 1087 0 29 YHHI-4300-LOOP-END YHHI-4300-LOOP-END.02 YHHI-4300-LOOP-END.02 1998 R14920.00 R1492000 END CHNGE ADDRESS SDLI LP L2 1998 END CHANGE OF ADDRESS SINCE DLI LOOP 483 13 HOUSEHOLD CHARACTERISTICS B 0 1 0 1 307 0 30 YHHI-4300-LOOP-END YHHI-4300-LOOP-END.03 YHHI-4300-LOOP-END.03 1998 R14921.00 R1492100 END CHNGE ADDRESS SDLI LP L3 1998 END CHANGE OF ADDRESS SINCE DLI LOOP 496 13 HOUSEHOLD CHARACTERISTICS B 0 1 0 1 71 0 31 YHHI-4300-LOOP-END YHHI-4300-LOOP-END.04 YHHI-4300-LOOP-END.04 1998 R14922.00 R1492200 END CHNGE ADDRESS SDLI LP L4 1998 END CHANGE OF ADDRESS SINCE DLI LOOP 509 13 HOUSEHOLD CHARACTERISTICS B 0 1 0 1 13 0 32 YHHI-4300-LOOP-END YHHI-4300-LOOP-END.05 YHHI-4300-LOOP-END.05 1998 R14923.00 R1492300 END CHNGE ADDRESS SDLI LP L5 1998 END CHANGE OF ADDRESS SINCE DLI LOOP 522 13 HOUSEHOLD CHARACTERISTICS B 0 1 0 1 2 0 33 YHHI-4300-LOOP-END YHHI-4300-LOOP-END.06 YHHI-4300-LOOP-END.06 1998 R14924.00 R1492400 END CHNGE ADDRESS SDLI LP L6 1998 END CHANGE OF ADDRESS SINCE DLI LOOP 535 13 HOUSEHOLD CHARACTERISTICS B 1 1 1 1 1 0 34 YHHI-4300-LOOP-END YHHI-4300-LOOP-END.07 YHHI-4300-LOOP-END.07 1998 R14925.00 R1492500 END CHNGE ADDRESS SDLI LP L7 1998 END CHANGE OF ADDRESS SINCE DLI LOOP 548 13 HOUSEHOLD CHARACTERISTICS B 0 0 0 0 1 0 35 YHHI-4400 YHHI-4400 YHHI-4400 1998 R14926.00 R1492600 RS CURR DWELLING 1998 WHAT IS RS CURRENT TYPE OF DWELLING? 561 30 HOUSEHOLD CHARACTERISTICS B 1 14 1 14 8386 0 36 YHHI-4505 YHHI-4505 YHHI-4505 1998 R14927.00 R1492700 DURATION IN THIS DWELLING 1998 RS DURATION IN THIS DWELLING? 591 20 YOUTH HISTORY B 1 5 1 5 195 0 37 YHHI-4600 YHHI-4600 YHHI-4600 1998 R14928.00 R1492800 CHK 1+ PAR ON HH ROST 1998 CHK 1+ PARENT ON HH ROSTER 611 19 MACHINE CHECK B -3 1 0 1 8386 0 38 YHHI-5800 YHHI-5800.01 YHHI-5800.01 1998 R14929.00 R1492900 VFY PAR INFO AT DLI L1 1998 VERIFY PARENT INFORMATION AT DLI HH PAR 01 630 18 PARENT RETROSPECTIVE B -1 1 0 1 8025 0 39 YHHI-5800 YHHI-5800.02 YHHI-5800.02 1998 R14930.00 R1493000 VFY PAR INFO AT DLI L2 1998 VERIFY PARENT INFORMATION AT DLI HH PAR 02 648 18 PARENT RETROSPECTIVE B -2 1 0 1 5292 0 40 YHHI-5800 YHHI-5800.03 YHHI-5800.03 1998 R14931.00 R1493100 VFY PAR INFO AT DLI L3 1998 VERIFY PARENT INFORMATION AT DLI HH PAR 03 666 18 PARENT RETROSPECTIVE B 0 1 0 1 33 0 41 YHHI-5800 YHHI-5800.04 YHHI-5800.04 1998 R14932.00 R1493200 VFY PAR INFO AT DLI L4 1998 VERIFY PARENT INFORMATION AT DLI HH PAR 04 684 18 PARENT RETROSPECTIVE B 0 0 0 0 1 0 42 YHHI-7100 YHHI-7100.01 YHHI-7100.01 1998 R14933.00 R1493300 CHK DLI PAR INFO CRCT L1 1998 CHK DLI PARENT 01 INFORMATION CORRECT 702 18 MACHINE CHECK B 0 1 0 1 8025 0 43 YHHI-7100 YHHI-7100.02 YHHI-7100.02 1998 R14934.00 R1493400 CHK DLI PAR INFO CRCT L2 1998 CHK DLI PARENT 02 INFORMATION CORRECT 720 18 MACHINE CHECK B 0 1 0 1 5292 0 44 YHHI-7100 YHHI-7100.03 YHHI-7100.03 1998 R14935.00 R1493500 CHK DLI PAR INFO CRCT L3 1998 CHK DLI PARENT 03 INFORMATION CORRECT 738 18 MACHINE CHECK B 0 1 0 1 33 0 45 YHHI-7100 YHHI-7100.04 YHHI-7100.04 1998 R14936.00 R1493600 CHK DLI PAR INFO CRCT L4 1998 CHK DLI PARENT 04 INFORMATION CORRECT 756 18 MACHINE CHECK B 0 0 0 0 1 0 46 YHHI-7200 YHHI-7200.01~000002 YHHI-7200.01 1998 R14937.00 R1493700 WHAT PAR INFO INCRCT FROM DLI L1 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 01 774 24 PARENT RETROSPECTIVE B -1 1 0 1 145 0 47 YHHI-7200 YHHI-7200.01~000003 YHHI-7200.01 1998 R14937.01 R1493701 WHAT PAR INFO INCRCT FROM DLI L1 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 01 774 24 PARENT RETROSPECTIVE B -1 1 0 1 145 0 48 YHHI-7200 YHHI-7200.01~000004 YHHI-7200.01 1998 R14937.02 R1493702 WHAT PAR INFO INCRCT FROM DLI L1 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 01 774 24 PARENT RETROSPECTIVE B -1 1 0 1 145 0 49 YHHI-7200 YHHI-7200.01~000005 YHHI-7200.01 1998 R14937.03 R1493703 WHAT PAR INFO INCRCT FROM DLI L1 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 01 774 24 PARENT RETROSPECTIVE B -1 0 0 0 145 0 50 YHHI-7200 YHHI-7200.02~000002 YHHI-7200.02 1998 R14938.00 R1493800 WHAT PAR INFO INCRCT FROM DLI L2 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 02 798 24 PARENT RETROSPECTIVE B -2 1 0 1 125 0 51 YHHI-7200 YHHI-7200.02~000003 YHHI-7200.02 1998 R14938.01 R1493801 WHAT PAR INFO INCRCT FROM DLI L2 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 02 798 24 PARENT RETROSPECTIVE B -2 1 0 1 125 0 52 YHHI-7200 YHHI-7200.02~000004 YHHI-7200.02 1998 R14938.02 R1493802 WHAT PAR INFO INCRCT FROM DLI L2 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 02 798 24 PARENT RETROSPECTIVE B -2 1 0 1 125 0 53 YHHI-7200 YHHI-7200.02~000005 YHHI-7200.02 1998 R14938.03 R1493803 WHAT PAR INFO INCRCT FROM DLI L2 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 02 798 24 PARENT RETROSPECTIVE B -2 1 0 1 125 0 54 YHHI-7200 YHHI-7200.03~000002 YHHI-7200.03 1998 R14939.00 R1493900 WHAT PAR INFO INCRCT FROM DLI L3 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 03 822 24 PARENT RETROSPECTIVE B 0 1 0 1 9 0 55 YHHI-7200 YHHI-7200.03~000003 YHHI-7200.03 1998 R14939.01 R1493901 WHAT PAR INFO INCRCT FROM DLI L3 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 03 822 24 PARENT RETROSPECTIVE B 0 1 0 1 9 0 56 YHHI-7200 YHHI-7200.03~000004 YHHI-7200.03 1998 R14939.02 R1493902 WHAT PAR INFO INCRCT FROM DLI L3 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 03 822 24 PARENT RETROSPECTIVE B 0 0 0 0 9 0 57 YHHI-7200 YHHI-7200.03~000005 YHHI-7200.03 1998 R14939.03 R1493903 WHAT PAR INFO INCRCT FROM DLI L3 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 03 822 24 PARENT RETROSPECTIVE B 0 0 0 0 9 0 58 YHHI-7200 YHHI-7200.04~000002 YHHI-7200.04 1998 R14940.00 R1494000 WHAT PAR INFO INCRCT FROM DLI L4 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 04 846 24 PARENT RETROSPECTIVE B 1 1 1 1 1 0 59 YHHI-7200 YHHI-7200.04~000003 YHHI-7200.04 1998 R14940.01 R1494001 WHAT PAR INFO INCRCT FROM DLI L4 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 04 846 24 PARENT RETROSPECTIVE B 1 1 1 1 1 0 60 YHHI-7200 YHHI-7200.04~000004 YHHI-7200.04 1998 R14940.02 R1494002 WHAT PAR INFO INCRCT FROM DLI L4 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 04 846 24 PARENT RETROSPECTIVE B 0 0 0 0 1 0 61 YHHI-7200 YHHI-7200.04~000005 YHHI-7200.04 1998 R14940.03 R1494003 WHAT PAR INFO INCRCT FROM DLI L4 1998 WHAT PARENT INFO IS INCORRECT FROM DLI? PAR 04 846 24 PARENT RETROSPECTIVE B 0 0 0 0 1 0 62 YHHI-8400 YHHI-8400.01 YHHI-8400.01 1998 R14941.00 R1494100 CHK DLI PAR NAME IS INCRCT L1 1998 CHK DLI PARENT 01 NAME IS INCORRECT 870 18 MACHINE CHECK B 0 1 0 1 145 0 63 YHHI-8400 YHHI-8400.02 YHHI-8400.02 1998 R14942.00 R1494200 CHK DLI PAR NAME IS INCRCT L2 1998 CHK DLI PARENT 02 NAME IS INCORRECT 888 18 MACHINE CHECK B 0 1 0 1 125 0 64 YHHI-8400 YHHI-8400.03 YHHI-8400.03 1998 R14943.00 R1494300 CHK DLI PAR NAME IS INCRCT L3 1998 CHK DLI PARENT 03 NAME IS INCORRECT 906 18 MACHINE CHECK B 0 1 0 1 9 0 65 YHHI-8400 YHHI-8400.04 YHHI-8400.04 1998 R14944.00 R1494400 CHK DLI PAR NAME IS INCRCT L4 1998 CHK DLI PARENT 04 NAME IS INCORRECT 924 18 MACHINE CHECK B 1 1 1 1 1 0 66 YHHI-8500 YHHI-8500.01 YHHI-8500.01 1998 R14945.00 R1494500 WHAT IS DLI PAR NAME? L1 1998 WHAT IS DLI PARENT 01 NAME? 942 11 PARENT RETROSPECTIVE V -777 -777 -777 -777 100 30 67 YHHI-8500 YHHI-8500.02 YHHI-8500.02 1998 R14946.00 R1494600 WHAT IS DLI PAR NAME? L2 1998 WHAT IS DLI PARENT 02 NAME? 953 11 PARENT RETROSPECTIVE V -777 -777 -777 -777 45 28 68 YHHI-8500 YHHI-8500.03 YHHI-8500.03 1998 R14947.00 R1494700 WHAT IS DLI PAR NAME? L3 1998 WHAT IS DLI PARENT 03 NAME? 964 11 PARENT RETROSPECTIVE V -777 -777 -777 -777 6 20 69 YHHI-8500 YHHI-8500.04 YHHI-8500.04 1998 R14948.00 R1494800 WHAT IS DLI PAR NAME? L4 1998 WHAT IS DLI PARENT 04 NAME? 975 11 PARENT RETROSPECTIVE V -777 -777 -777 -777 1 11 70 YHHI-8600 YHHI-8600.01 YHHI-8600.01 1998 R14949.00 R1494900 CHK DLI PAR RELSHP TO R INCRCT L1 1998 CHK DLI PARENT 01 RELATIONSHIP TO R IS INCORRECT 986 19 MACHINE CHECK B 0 1 0 1 145 0 71 YHHI-8600 YHHI-8600.02 YHHI-8600.02 1998 R14950.00 R1495000 CHK DLI PAR RELSHP TO R INCRCT L2 1998 CHK DLI PARENT 02 RELATIONSHIP TO R IS INCORRECT 1005 19 MACHINE CHECK B 0 1 0 1 125 0 72 YHHI-8600 YHHI-8600.03 YHHI-8600.03 1998 R14951.00 R1495100 CHK DLI PAR RELSHP TO R INCRCT L3 1998 CHK DLI PARENT 03 RELATIONSHIP TO R IS INCORRECT 1024 19 MACHINE CHECK B 0 1 0 1 9 0 73 YHHI-8600 YHHI-8600.04 YHHI-8600.04 1998 R14952.00 R1495200 CHK DLI PAR RELSHP TO R INCRCT L4 1998 CHK DLI PARENT 04 RELATIONSHIP TO R IS INCORRECT 1043 19 MACHINE CHECK B 1 1 1 1 1 0 74 YHHI-8700 YHHI-8700.01 YHHI-8700.01 1998 R14953.00 R1495300 WHAT CRCT RELSHP TO PAR DLI L1 1998 WHAT WAS RS CORRECT RELATIONSHIP TO PARENT 01 AT DLI? 1062 90 MACHINE CHECK B 3 72 3 72 30 0 75 YHHI-8700 YHHI-8700.02 YHHI-8700.02 1998 R14954.00 R1495400 WHAT CRCT RELSHP TO PAR DLI L2 1998 WHAT WAS RS CORRECT RELATIONSHIP TO PARENT 02 AT DLI? 1152 90 MACHINE CHECK B 3 89 3 89 57 0 76 YHHI-8700 YHHI-8700.03 YHHI-8700.03 1998 R14955.00 R1495500 WHAT CRCT RELSHP TO PAR DLI L3 1998 WHAT WAS RS CORRECT RELATIONSHIP TO PARENT 03 AT DLI? 1242 90 MACHINE CHECK B 4 14 4 14 6 0 77 YHHI-8700 YHHI-8700.04 YHHI-8700.04 1998 R14956.00 R1495600 WHAT CRCT RELSHP TO PAR DLI L4 1998 WHAT WAS RS CORRECT RELATIONSHIP TO PARENT 04 AT DLI? 1332 90 MACHINE CHECK B 4 4 4 4 1 0 78 Varlist batch file (to be run under SQL Plus) AS OF 11/02/1999: -- &1 = VAR_LIST_ERRORS -- &2 = VAR_LIST -- &3 = VAR_LOCATION -- &4 = VAR_RNUM_FLAG 'USE_NEW_RNUMS' -- &5 = VAR_ADD_YEAR 'YEAR_ON_SASTITLES' -- &6 = RNUM PREFIX -- &7 = VAR_STARTING_RNUM -- &8 = COMPRESSION QNAME TABLE -- &9 = QUESTION TABLE NAME -- &10 = FREQ TRACK TABLE NAME -- &11 = FREQ COUNTER TABLE NAME -- &12 = RECORD TYPE TABLE -- &13 = TOPICS TABLE -- &14 = SECTION TABLE SET TIME ON; SPOOL D:\DOC_PROGS\VARLIST\VAR_NLSY97_RND2.LOG; START D:\DOC_PROGS\VARLIST\VARLIST.SQL VAR_LIST_ERRORS_1998 VAR_LIST_1998 VAR_LOCATION_1998 USE_NEW_RNUMS YEAR_ON_SASTITLES R 14900 CMP_QNAME_1998 QUESTION_1998 FREQ_TOTAL_1998 FREQ_COUNT_1998 RECORDTYPE_1998 TOPICS_1998 SECTIONS_1998; SPOOL D:\DOC_PROGS\VARLIST\VAR_NLSY97_RND2.ERRLOG; START D:\DOC_PROGS\VARLIST\VARLIST_CHECK.SQL VAR_LIST_ERRORS_1998 VAR_LIST_1998 VAR_LOCATION_1998 USE_NEW_RNUMS YEAR_ON_SASTITLES R 14900 CMP_QNAME_1998 QUESTION_1998 FREQ_TOTAL_1998 FREQ_COUNT_1998 RECORDTYPE_1998 TOPICS_1998 SECTIONS_1998; SPOOL OFF; -- UPDATE THE SURVEY_ORDER FIELD SO THE CODEBOOK CAN BE DUMPED IN THAT ORDER INSTEAD OF RNUM ORDER SPOOL D:\DOC_PROGS\VARLIST\SURVEYORDER_NLSY97_RND2.LOG; START D:\DOC_PROGS\VARLIST\FIX_SURVEYORDER.SQL VAR_LIST_1998 CMP_QNAME_1998; SPOOL OFF; Varlist Program AS OF 11/02/1999: set echo on; -- &1 = VAR_LIST_ERRORS -- &2 = VAR_LIST -- &3 = VAR_LOCATION -- &4 = VAR_RNUM_FLAG 'USE_NEW_RNUMS' -- &5 = VAR_ADD_YEAR 'YEAR_ON_SASTITLES' -- &6 = RNUM PREFIX -- &7 = STARTING RNUM -- &8 = COMPRESSION QNAME TABLE -- &9 = QUESTION TABLE -- &10 = FREQ TOTAL TABLE -- &11 = FREQ COUNT TABLE -- &12 = RECORD TYPE TABLE -- &13 = TOPICS TABLE -- &14 = SECTION TABLE /* re-create output tables */ drop table &1; CREATE TABLE &1 (QNAME VARCHAR2 (75), TEXT VARCHAR2 (100), VALUE NUMBER (10)) pctused 90 pctfree 1 storage (initial 1M next 500K pctincrease 0) TABLESPACE DOCS_MAIN; TRUNCATE TABLE &1; drop table &2; create table &2 (MGR_QNAME VARCHAR2(75), DATA_QNAME VARCHAR2(75), LOOP_QNAME VARCHAR2 (75), QYEAR VARCHAR2(4), RNUM VARCHAR2(9), SASNAME VARCHAR2(8), SASTITLE VARCHAR2(40), KWIC_TITLE VARCHAR2(100), CDBKSTART NUMBER(10), CDBKLINES NUMBER(10), RTNAME VARCHAR2(35), SECURITY VARCHAR2(1), MINIMUM NUMBER(10), MAXIMUM NUMBER(10), VALID_MINIMUM NUMBER(10), VALID_MAXIMUM NUMBER(10), VALIDN NUMBER(10), ALPHA NUMBER(4), COUNTER NUMBER (10), PAPER_QNAME VARCHAR2 (30), SURVEY_ORDER VARCHAR2 (50)) pctused 90 pctfree 1 storage (initial 3M next 500K pctincrease 0) TABLESPACE DOCS_MAIN; TRUNCATE TABLE &2; drop table &3; create table &3 ( MGR_QNAME VARCHAR2 (75), DATA_QNAME VARCHAR2(75), LOOP_QNAME VARCHAR2 (75), ASCWIDTH NUMBER (4), PRIVATE_ROW NUMBER(10), PRIVATE_COL NUMBER(10), PRIVATE_BYTES NUMBER(10), PUBLIC_ROW NUMBER(10), PUBLIC_COL NUMBER(10), PUBLIC_BYTES NUMBER(10)) pctused 60 pctfree 30 storage (initial 1M next 500K pctincrease 0); TRUNCATE TABLE &3; /* start main program body */ DECLARE VAR_LIST_TABLE VARCHAR2 (35) := '&2'; VAR_LOCATION_TABLE VARCHAR2 (35) := '&3'; VAR_QNAME VARCHAR2 (75); VAR_LOOP_QNAME VARCHAR2 (75); VAR_PAPER_QNAME VARCHAR2 (30); VAR_QNUM NUMBER (10); VAR_DATAQNAME VARCHAR2 (75); VAR_ALL_MIN NUMBER (15); VAR_ALL_MAX NUMBER (15); VAR_VALID_MIN NUMBER (15); VAR_VALID_MAX NUMBER (15); VAR_ALPHAMAX NUMBER (5); VAR_TOTAL NUMBER (15); VAR_QTYPE NUMBER (5); VAR_QDATATYPE NUMBER (5); VAR_QMARKTYPE NUMBER (5); VAR_QYEAR NUMBER (5); VAR_NEWRNUM VARCHAR2 (9); VAR_MGR_QRNUM VARCHAR2 (9); VAR_WORKING_RNUM VARCHAR2 (15); VAR_SASNAME VARCHAR2 (25); VAR_SASTITLE VARCHAR2 (2000); MGR_SASTITLE VARCHAR2 (2000); VAR_SECURITY VARCHAR2 (2); VAR_QKWICTITLE VARCHAR2 (160); MGR_QKWICTITLE VARCHAR2 (160); VAR_TOPIC VARCHAR2 (35); VAR_LOOP VARCHAR2 (30); VAR_LAST_LOOP VARCHAR2 (30); VAR_SECTION NUMBER (10); VAR_SECTIONNAME VARCHAR2 (80); VAR_FREQCOUNT NUMBER (4); VAR_TEMP_LOOP VARCHAR2 (15); VAR_SUBSTRING VARCHAR2 (20); VAR_COUNT_EXTS NUMBER (15); VAR_COUNT_NEWRNUMS NUMBER (10); VAR_COUNT_LOOPS NUMBER (3); VAR_REC_COUNTER NUMBER (10); INDX NUMBER (10); VAR_COUNT NUMBER (5); VAR_BAD_DATE NUMBER (1); VAR_NEW_FLAG NUMBER (1); CURSOR_NAME INTEGER; CURSOR_ERROR INTEGER; CHAR_LINEFEED CHAR (1) := CHR (10); CHAR_CARRETURN CHAR (1) := CHR (13); VAR_STARTING_RNUM NUMBER (15) := &7; VAR_RNUMS_FLAG VARCHAR2 (20) := '&4'; VAR_ADD_YEAR VARCHAR2 (20) := '&5'; VAR_RNUM_PREFIX CHAR (1) := '&6'; CURSOR VAR_LIST_CURSOR IS SELECT COMP.QNAME FROM &8 COMP WHERE STATUS > 0 ORDER BY QORDER; CURSOR FREQ_TOTAL_CURSOR (VAR_QNAME VARCHAR2) IS SELECT DATA_QNAME, ALL_MIN, ALL_MAX, VALID_MIN, VALID_MAX, ALL_TOTAL, ALPHAMAX FROM &10 WHERE MGR_QNAME = VAR_QNAME ORDER BY MARKALL_ORDER, DATA_QNAME; CURSOR MGR_CURSOR (VAR_QNAME VARCHAR2) IS SELECT QDOCTYPE, QDOCDATATYPE, QMARKTYPE, QYEAR, QRNUM, QVARNAME, QVARLABEL, QVARTYPE, QKWICTITLE, QNUM, QSECTION FROM &9 WHERE QNAME = VAR_QNAME; CURSOR TOPIC_CURSOR (VAR_QNUM NUMBER) IS SELECT RECTYPENAME FROM &12 WHERE RECTYPENUM IN (SELECT TOPIC FROM &13 WHERE QNUM = VAR_QNUM); CURSOR SECTION_CURSOR (VAR_SECTION NUMBER) IS SELECT SECTIONNAME FROM &14 WHERE SECTIONID = VAR_SECTION; CURSOR SIR_CURSOR (VAR_MGR_QRNUM VARCHAR2) IS SELECT QUESTION_NAME FROM SIRTAB_VBLDEFVBLLOC WHERE REPLACE (REFERENCE_NUMBER,' ','0') = VAR_MGR_QRNUM; BEGIN VAR_REC_COUNTER := 0; VAR_COUNT_NEWRNUMS := 0; /* loop through compression qnames where status > 0 */ open VAR_LIST_CURSOR; LOOP FETCH VAR_LIST_CURSOR INTO VAR_QNAME; EXIT WHEN VAR_LIST_CURSOR%NOTFOUND; /* pick up information from the manager question table */ OPEN MGR_CURSOR (VAR_QNAME); FETCH MGR_CURSOR INTO VAR_QTYPE, VAR_QDATATYPE, VAR_QMARKTYPE, VAR_QYEAR, VAR_MGR_QRNUM, VAR_SASNAME, MGR_SASTITLE, VAR_SECURITY, MGR_QKWICTITLE, VAR_QNUM, VAR_SECTION; IF (MGR_CURSOR%NOTFOUND) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'MANAGER REC NOT FOUND'); ELSE /* check if qname = rnum. If so, check for old paper-and-pencil qname */ IF (REPLACE (VAR_MGR_QRNUM,'.') = VAR_QNAME) THEN OPEN SIR_CURSOR (VAR_MGR_QRNUM); FETCH SIR_CURSOR INTO VAR_PAPER_QNAME; IF (VAR_PAPER_QNAME IS NULL) THEN VAR_PAPER_QNAME := '*Created'; END IF; CLOSE SIR_CURSOR; END IF; VAR_MGR_QRNUM := REPLACE (REPLACE (VAR_MGR_QRNUM,' ','0'),'.00','.'); IF (VAR_MGR_QRNUM IS NOT NULL) AND (INSTR (VAR_MGR_QRNUM,'.') = 0) THEN VAR_MGR_QRNUM := VAR_MGR_QRNUM || '.'; END IF; IF (VAR_SECURITY IS NULL) THEN VAR_SECURITY := 'B'; END IF; /* do some cleanups on the kwic title. */ MGR_QKWICTITLE := REPLACE (MGR_QKWICTITLE,CHAR_LINEFEED); MGR_QKWICTITLE := REPLACE (MGR_QKWICTITLE,CHAR_CARRETURN); IF (MGR_QKWICTITLE IS NOT NULL) THEN FOR INDX IN 1..LENGTH (MGR_QKWICTITLE) LOOP IF (ASCII (SUBSTR (MGR_QKWICTITLE,INDX,1)) < 32) THEN MGR_QKWICTITLE := SUBSTR (MGR_QKWICTITLE,1,INDX - 1) || SUBSTR (MGR_QKWICTITLE,INDX + 1, LENGTH (MGR_QKWICTITLE)); END IF; END LOOP; MGR_QKWICTITLE := LTRIM (RTRIM (MGR_QKWICTITLE)); END IF; /* do some cleanups on the sas title. */ MGR_SASTITLE := REPLACE (MGR_SASTITLE,CHAR_LINEFEED); MGR_SASTITLE := REPLACE (MGR_SASTITLE,CHAR_CARRETURN); IF (MGR_SASTITLE IS NOT NULL) THEN FOR INDX IN 1..LENGTH (MGR_SASTITLE) LOOP IF (ASCII (SUBSTR (MGR_SASTITLE,INDX,1)) < 32) THEN MGR_SASTITLE := SUBSTR (MGR_SASTITLE,1,INDX - 1) || SUBSTR (MGR_SASTITLE,INDX + 1, LENGTH (MGR_SASTITLE)); END IF; END LOOP; MGR_SASTITLE := LTRIM (RTRIM (MGR_SASTITLE)); END IF; /* fetch the main topic for this question number. If no topic is found, use the section name, if possible. */ OPEN TOPIC_CURSOR (VAR_QNUM); FETCH TOPIC_CURSOR INTO VAR_TOPIC; IF (TOPIC_CURSOR%NOTFOUND) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'TOPIC NOT FOUND'); OPEN SECTION_CURSOR (VAR_SECTION); FETCH SECTION_CURSOR INTO VAR_SECTIONNAME; IF (SECTION_CURSOR%NOTFOUND) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'SECTION NOT FOUND'); ELSE IF (LENGTH (VAR_SECTIONNAME) > 35) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'SECTION TRUNCATED'); END IF; VAR_TOPIC := UPPER (VAR_SECTIONNAME); END IF; CLOSE SECTION_CURSOR; ELSE IF (LENGTH (VAR_TOPIC) > 35) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'TOPIC TRUNCATED'); END IF; END IF; CLOSE TOPIC_CURSOR; /* fetch the frequency total record for this item */ OPEN FREQ_TOTAL_CURSOR (VAR_QNAME); FETCH FREQ_TOTAL_CURSOR INTO VAR_DATAQNAME, VAR_ALL_MIN, VAR_ALL_MAX, VAR_VALID_MIN, VAR_VALID_MAX, VAR_TOTAL, VAR_ALPHAMAX; IF (FREQ_TOTAL_CURSOR%NOTFOUND) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'FREQ TOTAL REC NOT FOUND'); VAR_ALL_MIN := NULL; VAR_ALL_MAX := NULL; VAR_VALID_MIN := NULL; VAR_VALID_MAX := NULL; VAR_TOTAL := -777; VAR_ALPHAMAX := 0; END IF; /* BUILD BASE RNUM FOR THE MGR_QNAME */ IF (VAR_RNUMS_FLAG = 'USE_NEW_RNUMS') OR ((VAR_RNUMS_FLAG = 'NOUSE_NEW_RNUMS') AND (VAR_MGR_QRNUM IS NULL)) THEN VAR_MGR_QRNUM := REPLACE (VAR_RNUM_PREFIX || TO_CHAR (VAR_STARTING_RNUM,'09999') || '.',' '); VAR_STARTING_RNUM := VAR_STARTING_RNUM + 1; VAR_COUNT_NEWRNUMS := VAR_COUNT_NEWRNUMS + 1; VAR_NEW_FLAG := 1; ELSE VAR_MGR_QRNUM := REPLACE (VAR_RNUM_PREFIX || SUBSTR (VAR_MGR_QRNUM,2,8),' '); VAR_NEW_FLAG := 0; END IF; /* INITIALIZE BEFORE LOOP */ VAR_LAST_LOOP := ' '; VAR_COUNT_EXTS := 0; VAR_COUNT_LOOPS := 0; /* START FREQ TOTAL LOOP */ LOOP VAR_SASTITLE := MGR_SASTITLE; VAR_QKWICTITLE := MGR_QKWICTITLE; VAR_LOOP := '#'; IF ((VAR_QNAME <> VAR_DATAQNAME) AND (INSTR (VAR_DATAQNAME,'.') > 0)) THEN VAR_LOOP := SUBSTR (VAR_DATAQNAME, INSTR(VAR_DATAQNAME,'.'), LENGTH (VAR_DATAQNAME)); IF (INSTR (VAR_LOOP,'~') > 0) THEN VAR_LOOP := SUBSTR (VAR_LOOP,1,INSTR (VAR_LOOP,'~') - 1); ELSIF (INSTR (VAR_LOOP,'_') > 0) THEN VAR_LOOP := SUBSTR (VAR_LOOP,1,INSTR (VAR_LOOP,'_') - 1); END IF; END IF; IF (VAR_LOOP <> '#') THEN /* DO SUBS IN SAS TITLE */ IF (INSTR (VAR_SASTITLE,'%S') > 0) THEN VAR_TEMP_LOOP := VAR_LOOP; LOOP VAR_SUBSTRING := SUBSTR (VAR_TEMP_LOOP,2,2); VAR_SASTITLE := SUBSTR (VAR_SASTITLE,1, INSTR (VAR_SASTITLE,'%S') - 1) || VAR_SUBSTRING || SUBSTR (VAR_SASTITLE, INSTR (VAR_SASTITLE,'%S') + 2, LENGTH (VAR_SASTITLE)); IF (SUBSTR (VAR_TEMP_LOOP,4,1) = '.') THEN VAR_TEMP_LOOP := SUBSTR (VAR_TEMP_LOOP,4,LENGTH (VAR_TEMP_LOOP)); ELSE EXIT; END IF; END LOOP; ELSE VAR_SUBSTRING := ' L' || SUBSTR (VAR_LOOP,2,LENGTH (VAR_LOOP)); VAR_SUBSTRING := REPLACE (VAR_SUBSTRING,'.',','); VAR_SUBSTRING := REPLACE (VAR_SUBSTRING,'L0','L'); VAR_SUBSTRING := REPLACE (VAR_SUBSTRING,',0',','); VAR_SASTITLE := RTRIM (VAR_SASTITLE) || VAR_SUBSTRING; END IF; /* DO SUBS IN KWIC TITLE */ IF (INSTR (VAR_QKWICTITLE,'%S') > 0) THEN VAR_TEMP_LOOP := VAR_LOOP; LOOP VAR_SUBSTRING := SUBSTR (VAR_TEMP_LOOP,2,2); VAR_QKWICTITLE := SUBSTR (VAR_QKWICTITLE,1, INSTR (VAR_QKWICTITLE,'%S') - 1) || VAR_SUBSTRING || SUBSTR (VAR_QKWICTITLE, INSTR (VAR_QKWICTITLE,'%S') + 2, LENGTH (VAR_QKWICTITLE)); IF (SUBSTR (VAR_TEMP_LOOP,4,1) = '.') THEN VAR_TEMP_LOOP := SUBSTR (VAR_TEMP_LOOP,4,LENGTH (VAR_TEMP_LOOP)); ELSE EXIT; END IF; END LOOP; END IF; END IF; IF (VAR_QTYPE = 14) THEN VAR_QKWICTITLE := VAR_QKWICTITLE || ' ITEM#' || SUBSTR (VAR_DATAQNAME,-2,2); VAR_SASTITLE := VAR_SASTITLE || ' #' || SUBSTR (VAR_DATAQNAME,-2,2); END IF; IF (INSTR (VAR_QKWICTITLE,'%S') > 0) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'UNRESOLVED KWIC SUBST.'); END IF; VAR_QKWICTITLE := RTRIM (LTRIM (VAR_QKWICTITLE)); IF (INSTR (VAR_SASTITLE,'%S') > 0) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'UNRESOLVED SASTITLE SUBST.'); END IF; IF (VAR_ADD_YEAR = 'YEAR_ON_SASTITLES') THEN VAR_SASTITLE := RTRIM (VAR_SASTITLE) || ' ' || VAR_QYEAR; END IF; VAR_SASTITLE := RTRIM (LTRIM (VAR_SASTITLE)); IF (LENGTH (VAR_SASTITLE) > 40) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'SAS TITLE > 40:' || VAR_SASTITLE); VAR_SASTITLE := RTRIM (SUBSTR (VAR_SASTITLE,1,40)); END IF; IF (LENGTH (VAR_QKWICTITLE) > 80) THEN INSERT INTO &1 (QNAME,TEXT) VALUES (VAR_QNAME,'KWIC TITLE LONGER THAN 80'); VAR_QKWICTITLE := RTRIM (SUBSTR (VAR_QKWICTITLE,1,80)); END IF; /* START BUILDING ROWS FOR VAR_LIST TABLE. IF COMPOSITE DATE, DO NOT WRITE A ROW... */ VAR_BAD_DATE := 0; IF ((VAR_QDATATYPE = 5) OR (VAR_QDATATYPE = 9)) AND (SUBSTR (VAR_DATAQNAME,-2,1) <> '~') AND (SUBSTR (VAR_DATAQNAME,-2,1) <> '_') THEN SELECT COUNT(*) INTO VAR_COUNT FROM &10 WHERE DATA_QNAME LIKE VAR_DATAQNAME || '%'; IF (VAR_COUNT > 1) THEN VAR_BAD_DATE := 1; ELSE VAR_BAD_DATE := 2; END IF; END IF; IF (VAR_BAD_DATE <> 1) THEN IF (VAR_LAST_LOOP = ' ') OR (VAR_LAST_LOOP <> VAR_LOOP) THEN VAR_COUNT_LOOPS := VAR_COUNT_LOOPS + 1; VAR_COUNT_EXTS := 0; END IF; VAR_LAST_LOOP := VAR_LOOP; IF (VAR_LOOP <> '#') THEN VAR_NEWRNUM := REPLACE (SUBSTR (VAR_MGR_QRNUM,1,1) || TO_CHAR (TO_NUMBER (SUBSTR (VAR_MGR_QRNUM,2,5)) + VAR_COUNT_LOOPS - 1,'09999') || '.',' '); ELSE VAR_NEWRNUM := VAR_MGR_QRNUM; END IF; /* DO MARK ALL QUESTIONS */ IF (VAR_QMARKTYPE = 3) THEN VAR_WORKING_RNUM := VAR_NEWRNUM || TO_CHAR (VAR_COUNT_EXTS,'09'); VAR_NEWRNUM := SUBSTR (REPLACE (VAR_WORKING_RNUM,' '),1,9); VAR_SASNAME := REPLACE (VAR_NEWRNUM,'.'); VAR_LOOP_QNAME := VAR_QNAME || REPLACE(VAR_LOOP,'#'); VAR_REC_COUNTER := VAR_REC_COUNTER + 1; INSERT INTO &2 (MGR_QNAME, DATA_QNAME,LOOP_QNAME,QYEAR,RNUM,SASNAME,SASTITLE,KWIC_TITLE,RTNAME,SECURITY, MINIMUM,MAXIMUM,VALID_MINIMUM, VALID_MAXIMUM,VALIDN,ALPHA,COUNTER, PAPER_QNAME) VALUES (VAR_QNAME, VAR_DATAQNAME, VAR_LOOP_QNAME,VAR_QYEAR, VAR_NEWRNUM, VAR_SASNAME, VAR_SASTITLE, VAR_QKWICTITLE, VAR_TOPIC, VAR_SECURITY, VAR_ALL_MIN, VAR_ALL_MAX, VAR_VALID_MIN, VAR_VALID_MAX, VAR_TOTAL, 0, VAR_REC_COUNTER, VAR_PAPER_QNAME); VAR_COUNT_EXTS := VAR_COUNT_EXTS + 1; /* DO ALL OTHER QUESTION TYPES */ ELSE IF (VAR_BAD_DATE = 2) OR ((VAR_QDATATYPE = 5) AND ((SUBSTR (VAR_DATAQNAME,-2,2) = '~D') OR (SUBSTR (VAR_DATAQNAME,-2,2) = '_D'))) OR ((VAR_QDATATYPE = 9) AND ((SUBSTR (VAR_DATAQNAME,-2,2) = '~M') OR (SUBSTR (VAR_DATAQNAME,-2,2) = '_M'))) THEN VAR_WORKING_RNUM := VAR_NEWRNUM || '00'; ELSIF ((VAR_QDATATYPE = 5) AND ((SUBSTR (VAR_DATAQNAME,-2,2) = '~M') OR (SUBSTR (VAR_DATAQNAME,-2,2) = '_M'))) OR ((VAR_QDATATYPE = 9) AND ((SUBSTR (VAR_DATAQNAME,-2,2) = '~Y') OR (SUBSTR (VAR_DATAQNAME,-2,2) = '_Y'))) THEN VAR_WORKING_RNUM := VAR_NEWRNUM || '01'; ELSIF ((VAR_QDATATYPE = 5) OR (VAR_QDATATYPE = 9)) THEN VAR_WORKING_RNUM := VAR_NEWRNUM || '02'; ELSIF (VAR_QTYPE = 14) THEN VAR_WORKING_RNUM := VAR_NEWRNUM || SUBSTR (VAR_DATAQNAME,-2,2); ELSE VAR_WORKING_RNUM := VAR_NEWRNUM || '00'; END IF; VAR_NEWRNUM := SUBSTR (REPLACE (VAR_WORKING_RNUM,' '),1,9); IF (VAR_QDATATYPE = 7) THEN VAR_ALL_MIN := -777; VAR_ALL_MAX := -777; VAR_VALID_MIN := -777; VAR_VALID_MAX := -777; ELSE IF (VAR_ALL_MAX IS NULL) THEN VAR_ALL_MAX := -777; END IF; IF (VAR_ALL_MIN IS NULL) THEN VAR_ALL_MIN := -777; END IF; IF (VAR_VALID_MAX IS NULL) THEN VAR_VALID_MAX := -777; END IF; IF (VAR_VALID_MIN IS NULL) THEN VAR_VALID_MIN := -777; END IF; END IF; VAR_SASNAME := REPLACE (VAR_NEWRNUM,'.'); IF (VAR_QTYPE = 14) AND (VAR_QNAME <> VAR_DATAQNAME) THEN VAR_LOOP_QNAME := VAR_DATAQNAME; ELSE VAR_LOOP_QNAME := VAR_QNAME || REPLACE(VAR_LOOP,'#'); END IF; VAR_REC_COUNTER := VAR_REC_COUNTER + 1; INSERT INTO &2 (MGR_QNAME, DATA_QNAME,LOOP_QNAME,QYEAR,RNUM,SASNAME,SASTITLE,KWIC_TITLE,RTNAME,SECURITY, MINIMUM,MAXIMUM,VALID_MINIMUM, VALID_MAXIMUM,VALIDN,ALPHA,COUNTER, PAPER_QNAME) VALUES (VAR_QNAME, VAR_DATAQNAME, VAR_LOOP_QNAME, VAR_QYEAR, VAR_NEWRNUM, VAR_SASNAME, VAR_SASTITLE, VAR_QKWICTITLE, VAR_TOPIC, VAR_SECURITY, VAR_ALL_MIN, VAR_ALL_MAX, VAR_VALID_MIN, VAR_VALID_MAX, VAR_TOTAL, VAR_ALPHAMAX, VAR_REC_COUNTER, VAR_PAPER_QNAME); END IF; END IF; FETCH FREQ_TOTAL_CURSOR INTO VAR_DATAQNAME, VAR_ALL_MIN, VAR_ALL_MAX, VAR_VALID_MIN, VAR_VALID_MAX, VAR_TOTAL, VAR_ALPHAMAX; EXIT WHEN FREQ_TOTAL_CURSOR%NOTFOUND; END LOOP; /* END OF FREQ TOTAL LOOP */ IF (VAR_COUNT_LOOPS > 0) AND (VAR_NEW_FLAG = 1) THEN VAR_STARTING_RNUM := VAR_STARTING_RNUM + VAR_COUNT_LOOPS - 1; END IF; CLOSE FREQ_TOTAL_CURSOR; CLOSE MGR_CURSOR; COMMIT; END IF; /* END IF MGR REC FOUND */ END LOOP; CLOSE VAR_LIST_CURSOR; INSERT INTO &1 (TEXT,VALUE) VALUES ('NEW RNUMS COUNT:',VAR_COUNT_NEWRNUMS); COMMIT; /* create indexes on the var_list and var_location tables */ CURSOR_NAME := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (CURSOR_NAME, 'CREATE INDEX ' || VAR_LIST_TABLE || '_MKEY' || ' ON &2 (MGR_QNAME)',DBMS_SQL.NATIVE); CURSOR_ERROR := DBMS_SQL.EXECUTE (CURSOR_NAME); DBMS_SQL.CLOSE_CURSOR (CURSOR_NAME); CURSOR_NAME := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (CURSOR_NAME, 'CREATE INDEX ' || VAR_LIST_TABLE || '_DKEY' || ' ON &2 (DATA_QNAME)',DBMS_SQL.NATIVE); CURSOR_ERROR := DBMS_SQL.EXECUTE (CURSOR_NAME); DBMS_SQL.CLOSE_CURSOR (CURSOR_NAME); CURSOR_NAME := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (CURSOR_NAME, 'CREATE INDEX ' || VAR_LIST_TABLE || '_RKEY' || ' ON &2 (RNUM)',DBMS_SQL.NATIVE); CURSOR_ERROR := DBMS_SQL.EXECUTE (CURSOR_NAME); DBMS_SQL.CLOSE_CURSOR (CURSOR_NAME); CURSOR_NAME := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (CURSOR_NAME, 'CREATE INDEX ' || VAR_LOCATION_TABLE || '_MKEY' || ' ON &3 (MGR_QNAME)',DBMS_SQL.NATIVE); CURSOR_ERROR := DBMS_SQL.EXECUTE (CURSOR_NAME); DBMS_SQL.CLOSE_CURSOR (CURSOR_NAME); CURSOR_NAME := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (CURSOR_NAME, 'CREATE INDEX ' || VAR_LOCATION_TABLE || '_DKEY' || ' ON &3 (DATA_QNAME)',DBMS_SQL.NATIVE); CURSOR_ERROR := DBMS_SQL.EXECUTE (CURSOR_NAME); DBMS_SQL.CLOSE_CURSOR (CURSOR_NAME); END; / /* create LOCATION table for use by cd creator program */ insert into &3 (mgr_qname, data_qname) (select mgr_qname,data_qname from &2); commit; Varlist Report Program AS OF 11/02/1999: -- &1 = VAR_LIST_ERRORS -- &2 = VAR_LIST -- &3 = VAR_LOCATION -- &4 = VAR_RNUM_FLAG 'USE_OLD_RNUMS' -- &5 = VAR_ADD_YEAR 'YEAR_ON_SASTITLES' -- &6 = RNUM PREFIX -- &7 = STARTING RNUM -- &8 = COMPRESSION QNAME TABLE -- &9 = QUESTION TABLE -- &10 = FREQ TOTAL TABLE -- &11 = FREQ COUNT TABLE -- &12 = RECORD TYPE TABLE -- &13 = TOPICS TABLE -- &14 = SECTION TABLE CLEAR COLUMNS; SET LINESIZE 150; SET PAGESIZE 55; SET ECHO ON; SET HEADING ON; COLUMN QNAME FORMAT A50; COLUMN TEXT FORMAT A70; -- COUNT OF BRAND NEW RNUMS ASSIGNED -- SELECT TEXT,VALUE FROM &1 WHERE QNAME IS NULL; -- LIST OF QUESTIONS INCLUDED BY COMPRESSION BUT NOT APPEARING IN THE VAR_LIST... -- SELECT QNAME FROM &8 WHERE STATUS > 0 AND QNAME NOT IN (SELECT DISTINCT (MGR_QNAME) FROM &2); -- CHECK FOR DUPLICATE RNUMS... -- SELECT COUNT(*) FROM &2; SELECT COUNT(DISTINCT(RNUM)) FROM &2; -- LIST OF "ERRORS" FROM THE DESCRIPT GENERATION... -- SELECT DISTINCT * FROM &1 WHERE QNAME IS NOT NULL ORDER BY TEXT,QNAME;