Documentation Programs:
There are four "core" documentation programs, to be executed in this order:
FREQS.SQL
COMPRESSION.SQL
VARLIST.SQL
CODEBOOK.SQL
Frequency Program ("FREQS.SQL")
----------------------------------------------------
Description:
The frequency program uses the manager question, jump, dcodes, and ccodes tables to run frequencies against the
matching post-processed data table. Three main tables are created: a table of summary statistics for each distinct
question name in the data table ("FREQ_TOTAL" table), a table of "counts" for each distinct question name ("FREQ_COUNT"
table), and a table of "counts" per jump for the matching manager question name. These tables are in turn used by the
compression program.
The "&n" symbol used below signifies a "substitution parameter" within the frequency program (therefore, if &2 =
survey data table, then every occurance of &2 in the program is substituted with the name of the survey data
table).
Tables used (program=FREQS.SQL):
-- &2 = SURVEY DATA TABLE
-- &3 = MANAGER QUESTION TABLE
-- &4 = MANAGER JUMP TABLE
-- &5 = MANAGER DCODES TABLE
-- &6 = MANAGER DCODENAME TABLE
-- &7 = MANAGER CCODES TABLE
-- &8 = MANAGER CCODENAME TABLE
Option Parameters (program=FREQ.SQL):
-- &13 = NOREPLACE_FREQS / REPLACE_FREQS (Do not/Do) delete frequencies before inserting new freqs.
Used when running a partial frequency.
-- &14 = DELETE_QNAMES / NODELETE_QNAMES (Do/Do not) delete existing qnames from the frequency qname table.
Used when running a partial frequency - set to NODELETE if you
have populated the frequency qname table with a subset of qnames.
-- &15 = NOSUBSET_DATANAMES / SUBSET_QNAMES (Do not/Do) use the qnames in the mgr question table to subset
the data table qnames. Used when the data table contains many
items which are NOT questions in the set of manager question
records used for the frequency.
-- &16 = NEW_EXTENTIONS / OLD_EXTENTIONS Are the extentions on the data table in the "old" format (like 'Q1_M')
or in the "new" format (like 'Q1~M')?
-- &17 = "ALPHAS" (OR "NOALPHAS") Are there alphas allowed in the manager records? (For example, the
NLSY79 79-93 was all numeric, this option would be NOALPHAS,
but for most newer Win Capi surveys, ALPHAS would be used).
-- &18 = NAME OF QNAME COLUMN IN DATA TABLE What is the name of the QNAME column in the data table? For most
newer surveys, it would be QNAME. For the older NLSY79 79-93
this option is REFERENCE.
Tables created (program=FREQS_SETUP.SQL):
-- &1 = FREQ_QNAME TABLE This table is a working table for the frequency program. It contains
the distinct qnames from the data. You can check on the progress
of the frequencies by checking the count of this table; as the freqs
are completed on a data question name, it's name is removed from
the table (in case a restart is needed).
-- &2 = FREQ_TOTAL TABLE This table is the total table for the frequency program. It contains
one entry for each distinct qname in the data table (or subsetted
data table). Information contained in this table are summary
statistics for that question name: min, max, total count, etc,
as well as the MGR_QNAME which links that question to a manager
record, and DATA_QNAME which is the qname from the data table.
-- &3 = FREQ_COUNT TABLE This table contains the actual counts for the question. The identifier
on this table is DATA_QNAME, which links to the data table and
the total table. A question has multiple entries in this table
depending on question type, dcode, ccode and added -1,-2,-3
categories.
-- &4 = FREQ_ERRORS TABLE This table contains any error or anomalies uncovered by the frequency
program during it's execution. This is for ALL questions listed in
the FREQ_QNAME table. The error report program lists a subset of
these error based on the printflag status in the manager records.
-- &5 = FREQ_JUMP TABLE This table is created by the frequency program to be used by compression
and the codebook program. While computing counts, separate counts
are kept based on any jumps a particular question has in the
manager JUMP table. This is because jumps do not always match
dcode or ccode categories.
FREQ_QNAME Oracle description:
SQLWKS> desc freq_qnames_1998
Column Name Null? Type
------------------------------ -------- ----
QNAME VARCHAR2(75) distinct qname from data table
FREQ_TOTAL Oracle description:
SQLWKS> desc freq_total_1998
Column Name Null? Type
------------------------------ -------- ----
DATA_QNAME VARCHAR2(75) question name in data table
MGR_QNAME VARCHAR2(75) question name in manager question table
VALID_TOTAL NUMBER(8) # of rows found with this data_qname with valid (>= 0 or <= -6)
ALL_TOTAL NUMBER(8) # of total rows found with this data_qname
ALL_MIN NUMBER(15) absolute mininum for this data_qname
ALL_MAX NUMBER(15) absolute maximum for this data_qname
VALID_MIN NUMBER(15) minimum for valid responses only (>= 0 or <= -6)
VALID_MAX NUMBER(15) maximum for valid responses only (>= 0 or <= -6)
SUMTOTAL NUMBER(15) sumtotal of valid responses only
ALPHAMAX NUMBER(10) maximum length if character response (else this field=0)
MARKALL_ORDER NUMBER(15) ordering number for markall items only
FREQ_COUNT Oracle description:
SQLWKS> desc freq_count_1998
Column Name Null? Type
------------------------------ -------- ----
DATA_QNAME VARCHAR2(75) question name in the data table
LOWVAL NUMBER(10) low value of range collecting counts for
HIGHVAL NUMBER(10) high value of range collecting counts for
COUNT NUMBER(8) # responses for this qname for the given range
FREQ_ERORS Oracle description:
SQLWKS> desc freq_errors_1998
Column Name Null? Type
------------------------------ -------- ----
TEXT VARCHAR2(50) text of error
DATA_QNAME VARCHAR2(75) data_qname relating to error
MGR_QNAME VARCHAR2(75) mgr_qname relating to error
VALUE NUMBER(12) a numeric field used for some types of errors (undocumented
dcode or ccode response, for example)
FREQ_JUMP Oracle description:
SQLWKS> desc freq_jump_1998
Column Name Null? Type
------------------------------ -------- ----
MGR_QNAME VARCHAR2(75) question name in the manager table
JUMPMIN NUMBER(10) mininum for this jump
JUMPMAX NUMBER(10) maximum for this jump
COUNT NUMBER(10) # responses which fit into this jump range
Sample rows from FREQ_TOTAL table:
SQLWKS> SELECT * FROM NEW_FREQ_TOTAL_1998 WHERE ROWNUM < 2000
2>
DATA_QNAME MGR_QNAME VALID_TOTAL ALL_TOTAL ALL_MIN ALL_MAX VALID_MIN VALID_MAX SUMTOTAL ALPHAMAX MARKALL_ORDER
------------------------------------- ----------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------
HHI_DOB.01 HHI_DOB 7163 7163 18800101 19970511 18800101 19970511 1.4008E+11 0
HHI_DOB.01~M HHI_DOB 7163 7163 1 12 1 12 41142 0
HHI_DOB.01~Y HHI_DOB 7163 7163 1880 1997 1880 1997 14007486 0
HHI_DOB.02 HHI_DOB 4691 4691 18800101 19970418 18800101 19970418 9.1617E+10 0
HHI_DOB.02~M HHI_DOB 4691 4691 1 12 1 12 27232 0
HHI_DOB.02~Y HHI_DOB 4691 4691 1880 1997 1880 1997 9161449 0
HHI_DOB.03 HHI_DOB 90 90 18800101 19780612 18800101 19780612 1758711187 0
HHI_DOB.03~M HHI_DOB 90 90 1 12 1 12 497 0
HHI_DOB.03~Y HHI_DOB 90 90 1880 1978 1880 1978 175866 0
HHI_DOB.04 HHI_DOB 8 8 19440403 19610717 19440403 19610717 156214577 0
HHI_DOB.04~M HHI_DOB 8 8 1 12 1 12 44 0
HHI_DOB.04~Y HHI_DOB 8 8 1944 1961 1944 1961 15621 0
HHI_DOB.05 HHI_DOB 2 2 19621211 19840831 19621211 19840831 39462042 0
HHI_DOB.05~M HHI_DOB 2 2 8 12 8 12 20 0
HHI_DOB.05~Y HHI_DOB 2 2 1962 1984 1962 1984 3946 0
HHI_ID.01 HHI_ID 8319 8319 1 1 1 1 8319 0
HHI_ID.02 HHI_ID 7744 7744 2 2 2 2 15488 0
HHI_ID.03 HHI_ID 6117 6117 3 3 3 3 18351 0
HHI_ID.04 HHI_ID 3552 3552 4 4 4 4 14208 0
HHI_ID.05 HHI_ID 1652 1652 5 5 5 5 8260 0
HHI_ID.06 HHI_ID 726 726 6 6 6 6 4356 0
HHI_ID.07 HHI_ID 345 345 7 7 7 7 2415 0
HHI_ID.08 HHI_ID 153 153 8 8 8 8 1224 0
HHI_ID.09 HHI_ID 70 70 9 9 9 9 630 0
HHI_ID.10 HHI_ID 39 39 10 10 10 10 390 0
HHI_ID.11 HHI_ID 15 15 11 11 11 11 165 0
HHI_ID.12 HHI_ID 8 8 12 12 12 12 96 0
HHI_ID.13 HHI_ID 5 5 13 13 13 13 65 0
HHI_ID.14 HHI_ID 4 4 14 14 14 14 56 0
HHI_NAME.01 HHI_NAME 8319 8319 0 30
HHI_NAME.02 HHI_NAME 7744 7744 0 30
HHI_NAME.03 HHI_NAME 6117 6117 0 29
HHI_NAME.04 HHI_NAME 3552 3552 0 29
HHI_NAME.05 HHI_NAME 1652 1652 0 30
HHI_NAME.06 HHI_NAME 726 726 0 26
HHI_NAME.07 HHI_NAME 345 345 0 26
HHI_NAME.08 HHI_NAME 153 153 0 24
HHI_NAME.09 HHI_NAME 70 70 0 20
HHI_NAME.10 HHI_NAME 39 39 0 19
HHI_NAME.11 HHI_NAME 15 15 0 18
HHI_NAME.12 HHI_NAME 8 8 0 13
HHI_NAME.13 HHI_NAME 5 5 0 16
HHI_NAME.14 HHI_NAME 4 4 0 20
HIU-1 HIU-1 7601 7601 1 2 1 2 9487 0
HIU-2 HIU-2 5623 5623 0 999999 0 999999 153063546 0
HIU-3 HIU-3 7587 7587 1 3 1 3 12915 0
HIU-4 HIU-4 3914 3914 0 999999 0 999999 118393208 0
HIU-5 HIU-5 7570 7570 1 2 1 2 12425 0
HIU-6 HIU-6 2648 2648 0 600600 0 600600 24009466 0
HIU-7 HIU-7 7760 7760 19981101 19999204 19981101 19999204 1.5508E+11 0
HIU-7~D HIU-7 7760 7760 1 31 1 31 115839 0
HIU-7~M HIU-7 7760 7760 1 92 1 92 59256 0
HIU-7~Y HIU-7 7771 7771 1998 1999 1998 1999 15529615 0
LAST-QUESTION LAST-QUESTION 8384 8384 1 1 1 1 8384 0
LASTPATCHAPPLIED LASTPATCHAPPLIED 8386 8386 0 2 0 2 10398 0
NEWFERT_BDATE.01 NEWFERT_BDATE 81 81 19801208 19990326 19801208 19990326 1617661526 0
NEWFERT_BDATE.01~D NEWFERT_BDATE 81 81 2 29 2 29 1326 0
NEWFERT_BDATE.01~M NEWFERT_BDATE 81 81 1 12 1 12 602 0
NEWFERT_BDATE.01~Y NEWFERT_BDATE 81 81 1980 1999 1980 1999 161760 0
NEWFERT_BDATE.02 NEWFERT_BDATE 8 8 19801208 19980721 19801208 19980721 159435436 0
NEWFERT_BDATE.02~D NEWFERT_BDATE 8 8 1 29 1 29 136 0
NEWFERT_BDATE.02~M NEWFERT_BDATE 8 8 2 12 2 12 53 0
NEWFERT_BDATE.02~Y NEWFERT_BDATE 8 8 1980 1998 1980 1998 15943 0
NORCID NORCID 8386 8386 1000231 8600012 1000231 8600012 1.3379E+10 0
SYMBOL!R2!FIRSTGRADE.01.01 SYMBOL!R2!FIRSTGRADE 8228 8231 -2 95 0 95 70097 0
SYMBOL!R2!FIRSTGRADE.01.02 SYMBOL!R2!FIRSTGRADE 58 58 7 14 7 14 604 0
SYMBOL!R2!FIRSTGRADE.01.03 SYMBOL!R2!FIRSTGRADE 1 2 -2 9 9 9 9 0
SYMBOL!R2!FIRSTGRADE.01.04 SYMBOL!R2!FIRSTGRADE 1 1 10 10 10 10 10 0
SYMBOL!R2!FIRSTGRADE.01.05 SYMBOL!R2!FIRSTGRADE 0 1 -1 -1 0 0
SYMBOL!R2!FIRSTGRADE.01.06 SYMBOL!R2!FIRSTGRADE 1 1 10 10 10 10 10 0
SYMBOL!R2!FIRSTGRADE.01.07 SYMBOL!R2!FIRSTGRADE 1 1 9 9 9 9 9 0
SYMBOL!R2!FIRSTGRADE.01.08 SYMBOL!R2!FIRSTGRADE 1 1 11 11 11 11 11 0
SYMBOL!R2!FIRSTGRADE.02.01 SYMBOL!R2!FIRSTGRADE 4005 4008 -2 14 1 14 36218 0
SYMBOL!R2!FIRSTGRADE.02.02 SYMBOL!R2!FIRSTGRADE 33 33 7 12 7 12 306 0
SYMBOL!R2!FIRSTGRADE.02.03 SYMBOL!R2!FIRSTGRADE 1 1 10 10 10 10 10 0
SYMBOL!R2!FIRSTGRADE.03.01 SYMBOL!R2!FIRSTGRADE 602 602 5 14 5 14 5575 0
SYMBOL!R2!FIRSTGRADE.03.02 SYMBOL!R2!FIRSTGRADE 10 10 8 12 8 12 99 0
SYMBOL!R2!FIRSTGRADE.04.01 SYMBOL!R2!FIRSTGRADE 86 86 6 12 6 12 804 0
SYMBOL!R2!FIRSTGRADE.04.02 SYMBOL!R2!FIRSTGRADE 2 2 10 12 10 12 22 0
SYMBOL!R2!FIRSTGRADE.05.01 SYMBOL!R2!FIRSTGRADE 15 15 7 12 7 12 142 0
SYMBOL!R2!FIRSTGRADE.06.01 SYMBOL!R2!FIRSTGRADE 2 2 10 10 10 10 20 0
SYMBOL!R2!FIRSTGRADEFINAL SYMBOL!R2!FIRSTGRADEFINAL 8378 8382 -2 95 0 95 78122 0
SYMBOL!R2!GED SYMBOL!R2!GED 8386 8386 0 0 0 0 0 0
YAST-050 YAST-050 8386 8386 0 1 0 1 1835 0
YAST-100 YAST-100 1835 1835 34 34 34 34 62390 0
YAST-1000 YAST-1000 32 32 1 1 1 1 32 0
YAST-1200 YAST-1200 26 32 -2 2 1 2 32 0
YAST-1220 YAST-1220 0 6 -2 -2 0 0
YAST-1240~000001 YAST-1240~000001 3 6 -2 90000 0 90000 95000 0
YAST-1240~000002 YAST-1240~000002 3 6 -2 100000 0 100000 115000 0
YAST-2610 YAST-2610 2 2 0 1 0 1 1 0
YAST-2615 YAST-2615 1 1 1 1 1 1 1 0
YAST-2617~M YAST-2617 0 1 -2 -2 0 0
YAST-2617~Y YAST-2617 0 1 -2 -2 0 0
Sample rows from FREQ_COUNT table:
SQLWKS> select * from new_freq_count_1998 where data_qname in (
2> 'HHI_DOB.01',...
DATA_QNAME LOWVAL HIGHVAL COUNT
---------------------------------- ---------- ---------- ----------
HHI_DOB.01 -3 -3 0
HHI_DOB.01 -2 -2 0
HHI_DOB.01 -1 -1 0
HHI_DOB.01~M -3 -3 0
HHI_DOB.01~M -2 -2 0
HHI_DOB.01~M -1 -1 0
HHI_DOB.01~M 3 3 542
HHI_DOB.01~M 2 2 500
HHI_DOB.01~M 1 1 1573
HHI_DOB.01~M 4 4 419
HHI_DOB.01~M 6 6 479
HHI_DOB.01~M 8 8 545
HHI_DOB.01~M 7 7 529
HHI_DOB.01~M 12 12 524
HHI_DOB.01~M 11 11 490
HHI_DOB.01~M 10 10 538
HHI_DOB.01~M 9 9 538
HHI_DOB.01~M 5 5 486
HHI_DOB.01~Y -3 -3 0
HHI_DOB.01~Y -2 -2 0
HHI_DOB.01~Y -1 -1 0
HHI_DOB.02 -3 -3 0
HHI_DOB.02 -2 -2 0
HHI_DOB.02 -1 -1 0
HHI_DOB.02~M -3 -3 0
HHI_DOB.02~M -2 -2 0
HHI_DOB.02~M -1 -1 0
HHI_DOB.02~M 2 2 265
HHI_DOB.02~M 5 5 323
HHI_DOB.02~M 7 7 350
HHI_DOB.02~M 9 9 361
HHI_DOB.02~M 11 11 349
HHI_DOB.02~M 12 12 313
HHI_DOB.02~M 10 10 344
HHI_DOB.02~M 8 8 361
HHI_DOB.02~M 4 4 341
HHI_DOB.02~M 3 3 331
HHI_DOB.02~M 1 1 1002
HHI_DOB.02~M 6 6 351
HHI_DOB.02~Y -3 -3 0
HHI_DOB.02~Y -2 -2 0
HHI_DOB.02~Y -1 -1 0
HHI_DOB.03 -3 -3 0
HHI_DOB.03 -2 -2 0
HHI_DOB.03 -1 -1 0
HHI_DOB.03~M -3 -3 0
HHI_DOB.03~M -2 -2 0
HHI_DOB.03~M -1 -1 0
HHI_DOB.03~M 3 3 8
HHI_DOB.03~M 2 2 4
HHI_DOB.03~M 1 1 23
HHI_DOB.03~M 4 4 4
HHI_DOB.03~M 6 6 10
HHI_DOB.03~M 8 8 10
HHI_DOB.03~M 7 7 3
HHI_DOB.03~M 12 12 2
HHI_DOB.03~M 11 11 10
HHI_DOB.03~M 10 10 7
HHI_DOB.03~M 9 9 4
HHI_DOB.03~M 5 5 5
HHI_DOB.03~Y -3 -3 0
HHI_DOB.03~Y -2 -2 0
HHI_DOB.03~Y -1 -1 0
HHI_DOB.04 -3 -3 0
HHI_DOB.04 -2 -2 0
HHI_DOB.04 -1 -1 0
HHI_DOB.04~M -3 -3 0
HHI_DOB.04~M -2 -2 0
HHI_DOB.04~M -1 -1 0
HHI_DOB.04~M 2 2 0
HHI_DOB.04~M 5 5 0
HHI_DOB.04~M 7 7 2
HHI_DOB.04~M 9 9 0
HHI_DOB.04~M 11 11 1
HHI_DOB.04~M 12 12 1
HHI_DOB.04~M 10 10 0
HHI_DOB.04~M 8 8 0
HHI_DOB.04~M 4 4 1
HHI_DOB.04~M 3 3 0
HHI_DOB.04~M 1 1 3
HHI_DOB.04~M 6 6 0
HHI_DOB.04~Y -3 -3 0
HHI_DOB.04~Y -2 -2 0
HHI_DOB.04~Y -1 -1 0
HHI_DOB.05 -3 -3 0
HHI_DOB.05 -2 -2 0
HHI_DOB.05 -1 -1 0
HHI_DOB.05~M -3 -3 0
HHI_DOB.05~M -2 -2 0
HHI_DOB.05~M 1 1 0
HHI_DOB.05~M 2 2 0
HHI_DOB.05~M -1 -1 0
HHI_DOB.05~M 3 3 0
HHI_DOB.05~M 5 5 0
HHI_DOB.05~M 7 7 0
HHI_DOB.05~M 9 9 0
HHI_DOB.05~M 8 8 1
HHI_DOB.05~M 12 12 1
HHI_DOB.05~M 11 11 0
HHI_DOB.05~M 10 10 0
HHI_DOB.05~M 6 6 0
HHI_DOB.05~M 4 4 0
HHI_DOB.05~Y -3 -3 0
HHI_DOB.05~Y -2 -2 0
HHI_DOB.05~Y -1 -1 0
HHI_ID.01 -3 -3 0
HHI_ID.01 -2 -2 0
HHI_ID.01 0 0 0
HHI_ID.01 -1 -1 0
HHI_ID.01 3 3 0
HHI_ID.01 5 5 0
HHI_ID.01 7 7 0
HHI_ID.01 9 9 0
HHI_ID.01 11 11 0
HHI_ID.01 10 10 0
HHI_ID.01 20 99 0
HHI_ID.01 19 19 0
HHI_ID.01 18 18 0
HHI_ID.01 17 17 0
HHI_ID.01 16 16 0
HHI_ID.01 15 15 0
HHI_ID.01 14 14 0
HHI_ID.01 13 13 0
HHI_ID.01 12 12 0
HHI_ID.01 8 8 0
HHI_ID.01 6 6 0
HHI_ID.01 4 4 0
HHI_ID.01 2 2 0
HHI_ID.01 1 1 8319
HHI_ID.02 -3 -3 0
HHI_ID.02 -2 -2 0
HHI_ID.02 0 0 0
HHI_ID.02 -1 -1 0
HHI_ID.02 3 3 0
HHI_ID.02 5 5 0
HHI_ID.02 7 7 0
HHI_ID.02 9 9 0
HHI_ID.02 11 11 0
HHI_ID.02 10 10 0
HHI_ID.02 20 99 0
HHI_ID.02 19 19 0
HHI_ID.02 18 18 0
HHI_ID.02 17 17 0
HHI_ID.02 16 16 0
HHI_ID.02 15 15 0
HHI_ID.02 14 14 0
HHI_ID.02 13 13 0
HHI_ID.02 12 12 0
HHI_ID.02 8 8 0
HHI_ID.02 6 6 0
HHI_ID.02 4 4 0
HHI_ID.02 2 2 7744
HHI_ID.02 1 1 0
HIU-1 -3 -3 0
HIU-1 -2 -2 0
HIU-1 1 1 5715
HIU-1 -1 -1 0
HIU-1 3 3 0
HIU-1 2 2 1886
HIU-2 -3 -3 0
HIU-2 -2 -2 0
HIU-2 -1 -1 0
HIU-2 1 4999 603
HIU-2 15000 19999 680
HIU-2 25000 29999 473
HIU-2 40000 49999 456
HIU-2 60000 69999 181
HIU-2 80000 89999 59
HIU-2 70000 79999 98
HIU-2 150000 99999999 41
HIU-2 100000 149999 63
HIU-2 90000 99999 22
HIU-2 50000 59999 307
HIU-2 30000 39999 743
HIU-2 10000 14999 684
HIU-2 5000 9999 497
HIU-2 0 0 170
HIU-2 20000 24999 546
HIU-3 -3 -3 0
HIU-3 -2 -2 0
HIU-3 1 1 4001
HIU-3 -1 -1 0
HIU-3 3 3 1742
HIU-3 2 2 1844
HIU-4 -3 -3 0
HIU-4 -2 -2 0
HIU-4 -1 -1 0
HIU-4 1 4999 314
HIU-4 15000 19999 373
HIU-4 25000 29999 352
HIU-4 40000 49999 399
HIU-4 60000 69999 119
HIU-4 80000 89999 50
HIU-4 70000 79999 98
HIU-4 150000 99999999 42
HIU-4 100000 149999 58
HIU-4 90000 99999 33
HIU-4 50000 59999 235
HIU-4 30000 39999 578
HIU-4 10000 14999 412
HIU-4 5000 9999 286
HIU-4 0 0 178
HIU-4 20000 24999 387
HIU-5 -3 -3 0
HIU-5 -2 -2 0
HIU-5 1 1 2715
HIU-5 -1 -1 0
HIU-5 3 3 0
HIU-5 2 2 4855
HIU-6 -3 -3 0
HIU-6 -2 -2 0
HIU-6 -1 -1 0
HIU-6 1 4999 1244
HIU-6 15000 19999 92
HIU-6 25000 29999 29
HIU-6 40000 49999 26
HIU-6 60000 69999 10
HIU-6 80000 89999 6
HIU-6 70000 79999 6
HIU-6 150000 99999999 16
HIU-6 100000 149999 11
HIU-6 90000 99999 1
HIU-6 50000 59999 23
HIU-6 30000 39999 36
HIU-6 10000 14999 279
HIU-6 5000 9999 659
HIU-6 0 0 132
HIU-6 20000 24999 78
HIU-7 -3 -3 0
HIU-7 -2 -2 0
HIU-7 -1 -1 0
HIU-7~D -3 -3 0
HIU-7~D -2 -2 0
HIU-7~D -1 -1 0
HIU-7~M -3 -3 0
HIU-7~M -2 -2 0
HIU-7~M 1 1 1437
HIU-7~M -1 -1 0
HIU-7~M 2 2 804
HIU-7~M 4 4 323
HIU-7~M 6 6 0
HIU-7~M 8 8 0
HIU-7~M 10 10 9
HIU-7~M 9 9 0
HIU-7~M 12 12 1931
HIU-7~M 11 11 2677
HIU-7~M 7 7 0
HIU-7~M 5 5 45
HIU-7~M 3 3 528
HIU-7~Y -3 -3 0
HIU-7~Y -2 -2 0
HIU-7~Y -1 -1 0
LAST-QUESTION -3 -3 0
LAST-QUESTION -2 -2 0
LAST-QUESTION -1 -1 0
LASTPATCHAPPLIED -3 -3 0
LASTPATCHAPPLIED -2 -2 0
LASTPATCHAPPLIED -1 -1 0
LASTPATCHAPPLIED 1 1 6186
LASTPATCHAPPLIED 2 2 2106
LASTPATCHAPPLIED 0 0 94
NEWFERT_BDATE.01 -3 -3 0
NEWFERT_BDATE.01 -2 -2 0
NEWFERT_BDATE.01 -1 -1 0
NEWFERT_BDATE.01~D -3 -3 0
NEWFERT_BDATE.01~D -2 -2 0
NEWFERT_BDATE.01~D -1 -1 0
NEWFERT_BDATE.01~M -3 -3 0
NEWFERT_BDATE.01~M -2 -2 0
NEWFERT_BDATE.01~M 1 1 9
NEWFERT_BDATE.01~M -1 -1 0
NEWFERT_BDATE.01~M 3 3 7
NEWFERT_BDATE.01~M 5 5 3
NEWFERT_BDATE.01~M 7 7 5
NEWFERT_BDATE.01~M 9 9 8
NEWFERT_BDATE.01~M 11 11 8
NEWFERT_BDATE.01~M 10 10 12
NEWFERT_BDATE.01~M 12 12 11
NEWFERT_BDATE.01~M 8 8 9
NEWFERT_BDATE.01~M 6 6 4
NEWFERT_BDATE.01~M 4 4 2
NEWFERT_BDATE.01~M 2 2 3
NEWFERT_BDATE.01~Y -3 -3 0
NEWFERT_BDATE.01~Y -2 -2 0
NEWFERT_BDATE.01~Y -1 -1 0
NEWFERT_BDATE.02 -3 -3 0
NEWFERT_BDATE.02 -2 -2 0
NEWFERT_BDATE.02 -1 -1 0
NEWFERT_BDATE.02~D -3 -3 0
NEWFERT_BDATE.02~D -2 -2 0
NEWFERT_BDATE.02~D -1 -1 0
NEWFERT_BDATE.02~M -3 -3 0
NEWFERT_BDATE.02~M -2 -2 0
NEWFERT_BDATE.02~M 1 1 0
NEWFERT_BDATE.02~M 2 2 2
NEWFERT_BDATE.02~M -1 -1 0
NEWFERT_BDATE.02~M 3 3 1
NEWFERT_BDATE.02~M 5 5 0
NEWFERT_BDATE.02~M 7 7 1
NEWFERT_BDATE.02~M 9 9 0
NEWFERT_BDATE.02~M 8 8 0
NEWFERT_BDATE.02~M 12 12 2
NEWFERT_BDATE.02~M 11 11 1
NEWFERT_BDATE.02~M 10 10 0
NEWFERT_BDATE.02~M 6 6 0
NEWFERT_BDATE.02~M 4 4 1
NEWFERT_BDATE.02~Y -3 -3 0
NEWFERT_BDATE.02~Y -2 -2 0
NEWFERT_BDATE.02~Y -1 -1 0
NORCID -3 -3 0
NORCID -2 -2 0
NORCID 0 0 0
NORCID -1 -1 0
NORCID 1200000 1299999 954
NORCID 1400000 1499999 975
NORCID 1600000 1699999 894
NORCID 1800000 1899999 580
NORCID 2000000 4999999 92
NORCID 1900000 1999999 383
NORCID 5000000 9999999 175
NORCID 1700000 1799999 692
NORCID 1500000 1599999 1079
NORCID 1300000 1399999 855
NORCID 1100000 1199999 894
NORCID 1000000 1099999 813
SYMBOL!R2!FIRSTGRADE.01.01 -3 -3 0
SYMBOL!R2!FIRSTGRADE.01.01 -2 -2 3
SYMBOL!R2!FIRSTGRADE.01.01 0 0 62
SYMBOL!R2!FIRSTGRADE.01.01 -1 -1 0
SYMBOL!R2!FIRSTGRADE.01.01 3 3 0
SYMBOL!R2!FIRSTGRADE.01.01 5 5 65
SYMBOL!R2!FIRSTGRADE.01.01 7 7 1655
SYMBOL!R2!FIRSTGRADE.01.01 9 9 1738
SYMBOL!R2!FIRSTGRADE.01.01 11 11 806
SYMBOL!R2!FIRSTGRADE.01.01 10 10 1487
SYMBOL!R2!FIRSTGRADE.01.01 20 20 0
SYMBOL!R2!FIRSTGRADE.01.01 19 19 0
SYMBOL!R2!FIRSTGRADE.01.01 18 18 0
SYMBOL!R2!FIRSTGRADE.01.01 17 17 0
SYMBOL!R2!FIRSTGRADE.01.01 16 16 0
SYMBOL!R2!FIRSTGRADE.01.01 15 15 0
SYMBOL!R2!FIRSTGRADE.01.01 14 14 0
SYMBOL!R2!FIRSTGRADE.01.01 13 13 0
SYMBOL!R2!FIRSTGRADE.01.01 95 95 3
SYMBOL!R2!FIRSTGRADE.01.01 12 12 108
SYMBOL!R2!FIRSTGRADE.01.01 8 8 1706
SYMBOL!R2!FIRSTGRADE.01.01 6 6 594
SYMBOL!R2!FIRSTGRADE.01.01 4 4 4
SYMBOL!R2!FIRSTGRADE.01.01 2 2 0
SYMBOL!R2!FIRSTGRADE.01.01 1 1 0
SYMBOL!R2!FIRSTGRADE.01.02 -3 -3 0
SYMBOL!R2!FIRSTGRADE.01.02 -2 -2 0
SYMBOL!R2!FIRSTGRADE.01.02 0 0 0
SYMBOL!R2!FIRSTGRADE.01.02 -1 -1 0
SYMBOL!R2!FIRSTGRADE.01.02 3 3 0
SYMBOL!R2!FIRSTGRADE.01.02 5 5 0
SYMBOL!R2!FIRSTGRADE.01.02 7 7 3
SYMBOL!R2!FIRSTGRADE.01.02 9 9 2
SYMBOL!R2!FIRSTGRADE.01.02 11 11 21
SYMBOL!R2!FIRSTGRADE.01.02 10 10 9
SYMBOL!R2!FIRSTGRADE.01.02 20 20 0
SYMBOL!R2!FIRSTGRADE.01.02 19 19 0
SYMBOL!R2!FIRSTGRADE.01.02 18 18 0
SYMBOL!R2!FIRSTGRADE.01.02 17 17 0
SYMBOL!R2!FIRSTGRADE.01.02 16 16 0
SYMBOL!R2!FIRSTGRADE.01.02 15 15 0
SYMBOL!R2!FIRSTGRADE.01.02 14 14 2
SYMBOL!R2!FIRSTGRADE.01.02 13 13 0
SYMBOL!R2!FIRSTGRADE.01.02 95 95 0
SYMBOL!R2!FIRSTGRADE.01.02 12 12 12
SYMBOL!R2!FIRSTGRADE.01.02 8 8 9
SYMBOL!R2!FIRSTGRADE.01.02 6 6 0
SYMBOL!R2!FIRSTGRADE.01.02 4 4 0
SYMBOL!R2!FIRSTGRADE.01.02 2 2 0
SYMBOL!R2!FIRSTGRADE.01.02 1 1 0
SYMBOL!R2!FIRSTGRADE.01.03 -3 -3 0
SYMBOL!R2!FIRSTGRADE.01.03 -2 -2 1
SYMBOL!R2!FIRSTGRADE.01.03 0 0 0
SYMBOL!R2!FIRSTGRADE.01.03 -1 -1 0
SYMBOL!R2!FIRSTGRADE.01.03 3 3 0
SYMBOL!R2!FIRSTGRADE.01.03 5 5 0
SYMBOL!R2!FIRSTGRADE.01.03 7 7 0
SYMBOL!R2!FIRSTGRADE.01.03 9 9 1
SYMBOL!R2!FIRSTGRADE.01.03 11 11 0
SYMBOL!R2!FIRSTGRADE.01.03 10 10 0
SYMBOL!R2!FIRSTGRADE.01.03 20 20 0
SYMBOL!R2!FIRSTGRADE.01.03 19 19 0
SYMBOL!R2!FIRSTGRADE.01.03 18 18 0
SYMBOL!R2!FIRSTGRADE.01.03 17 17 0
SYMBOL!R2!FIRSTGRADE.01.03 16 16 0
SYMBOL!R2!FIRSTGRADE.01.03 15 15 0
SYMBOL!R2!FIRSTGRADE.01.03 14 14 0
SYMBOL!R2!FIRSTGRADE.01.03 13 13 0
SYMBOL!R2!FIRSTGRADE.01.03 95 95 0
SYMBOL!R2!FIRSTGRADE.01.03 12 12 0
SYMBOL!R2!FIRSTGRADE.01.03 8 8 0
SYMBOL!R2!FIRSTGRADE.01.03 6 6 0
SYMBOL!R2!FIRSTGRADE.01.03 4 4 0
SYMBOL!R2!FIRSTGRADE.01.03 2 2 0
SYMBOL!R2!FIRSTGRADE.01.03 1 1 0
SYMBOL!R2!FIRSTGRADEFINAL -3 -3 0
SYMBOL!R2!FIRSTGRADEFINAL -2 -2 3
SYMBOL!R2!FIRSTGRADEFINAL -1 -1 1
SYMBOL!R2!FIRSTGRADEFINAL 2 2 0
SYMBOL!R2!FIRSTGRADEFINAL 3 3 0
SYMBOL!R2!FIRSTGRADEFINAL 1 1 0
SYMBOL!R2!FIRSTGRADEFINAL 0 0 176
SYMBOL!R2!FIRSTGRADEFINAL 4 4 1
SYMBOL!R2!FIRSTGRADEFINAL 6 6 95
SYMBOL!R2!FIRSTGRADEFINAL 5 5 10
SYMBOL!R2!FIRSTGRADEFINAL 15 15 0
SYMBOL!R2!FIRSTGRADEFINAL 14 14 5
SYMBOL!R2!FIRSTGRADEFINAL 13 13 0
SYMBOL!R2!FIRSTGRADEFINAL 12 12 248
SYMBOL!R2!FIRSTGRADEFINAL 11 11 1921
SYMBOL!R2!FIRSTGRADEFINAL 10 10 1734
SYMBOL!R2!FIRSTGRADEFINAL 9 9 3061
SYMBOL!R2!FIRSTGRADEFINAL 8 8 455
SYMBOL!R2!FIRSTGRADEFINAL 95 95 1
SYMBOL!R2!FIRSTGRADEFINAL 20 20 0
SYMBOL!R2!FIRSTGRADEFINAL 19 19 0
SYMBOL!R2!FIRSTGRADEFINAL 18 18 0
SYMBOL!R2!FIRSTGRADEFINAL 17 17 0
SYMBOL!R2!FIRSTGRADEFINAL 16 16 0
SYMBOL!R2!FIRSTGRADEFINAL 7 7 671
SYMBOL!R2!GED -3 -3 0
SYMBOL!R2!GED -2 -2 0
SYMBOL!R2!GED 0 0 8386
SYMBOL!R2!GED -1 -1 0
SYMBOL!R2!GED 1 1 0
YAST-050 -3 -3 0
YAST-050 -2 -2 0
YAST-050 0 0 6551
YAST-050 1 1 1835
YAST-050 -1 -1 0
YAST-100 -3 -3 0
YAST-100 -2 -2 0
YAST-100 -1 -1 0
YAST-1000 -3 -3 0
YAST-1000 -2 -2 0
YAST-1000 -1 -1 0
YAST-1200 -3 -3 0
YAST-1200 -2 -2 5
YAST-1200 1 1 20
YAST-1200 -1 -1 1
YAST-1200 2 2 6
YAST-1220 -3 -3 0
YAST-1220 -2 -2 6
YAST-1220 0 0 0
YAST-1220 -1 -1 0
YAST-1220 10000 14999 0
YAST-1220 20000 24999 0
YAST-1220 30000 39999 0
YAST-1220 50000 59999 0
YAST-1220 70000 79999 0
YAST-1220 60000 69999 0
YAST-1220 150000 99999999 0
YAST-1220 100000 149999 0
YAST-1220 90000 99999 0
YAST-1220 80000 89999 0
YAST-1220 40000 49999 0
YAST-1220 25000 29999 0
YAST-1220 15000 19999 0
YAST-1220 5000 9999 0
YAST-1220 1 4999 0
YAST-1240~000001 -3 -3 0
YAST-1240~000001 -2 -2 3
YAST-1240~000001 0 0 1
YAST-1240~000001 -1 -1 0
YAST-1240~000001 10000 14999 0
YAST-1240~000001 20000 24999 0
YAST-1240~000001 30000 39999 0
YAST-1240~000001 50000 59999 0
YAST-1240~000001 70000 79999 0
YAST-1240~000001 60000 69999 0
YAST-1240~000001 150000 999999999 0
YAST-1240~000001 100000 149999 0
YAST-1240~000001 90000 99999 1
YAST-1240~000001 80000 89999 0
YAST-1240~000001 40000 49999 0
YAST-1240~000001 25000 29999 0
YAST-1240~000001 15000 19999 0
YAST-1240~000001 5000 9999 1
YAST-1240~000001 1 4999 0
YAST-1240~000002 -3 -3 0
YAST-1240~000002 -2 -2 3
YAST-1240~000002 0 0 1
YAST-1240~000002 1 4999 0
YAST-1240~000002 -1 -1 0
YAST-1240~000002 5000 9999 0
YAST-1240~000002 15000 19999 1
YAST-1240~000002 25000 29999 0
YAST-1240~000002 40000 49999 0
YAST-1240~000002 30000 39999 0
YAST-1240~000002 150000 999999999 0
YAST-1240~000002 100000 149999 1
YAST-1240~000002 90000 99999 0
YAST-1240~000002 80000 89999 0
YAST-1240~000002 70000 79999 0
YAST-1240~000002 60000 69999 0
YAST-1240~000002 50000 59999 0
YAST-1240~000002 20000 24999 0
YAST-1240~000002 10000 14999 0
YAST-2610 -3 -3 0
YAST-2610 -2 -2 0
YAST-2610 -1 -1 0
YAST-2610 1 1 1
YAST-2610 0 0 1
YAST-2615 -3 -3 0
YAST-2615 -2 -2 0
YAST-2615 1 1 1
YAST-2615 -1 -1 0
YAST-2615 3 3 0
YAST-2615 2 2 0
YAST-2617~M -3 -3 0
YAST-2617~M -2 -2 1
YAST-2617~M 1 1 0
YAST-2617~M -1 -1 0
YAST-2617~M 2 2 0
YAST-2617~M 4 4 0
YAST-2617~M 6 6 0
YAST-2617~M 8 8 0
YAST-2617~M 10 10 0
YAST-2617~M 9 9 0
YAST-2617~M 12 12 0
YAST-2617~M 11 11 0
YAST-2617~M 7 7 0
YAST-2617~M 5 5 0
YAST-2617~M 3 3 0
YAST-2617~Y -3 -3 0
YAST-2617~Y -2 -2 1
YAST-2617~Y -1 -1 0
Frequency batch file (to be run under SQL Plus) AS OF 09/29/1999:
-- The next three lines run a small cleanup program which "fixes" any bad dcodes or ccodes
SPOOL D:\DOC_PROGS\FREQS\BADCODES_NLSY97_rnd1.LOG;
START D:\DOC_PROGS\FREQS\FIX_DCODES.SQL com_DCODES_1997 com_DCODENAME_1997 com_CCODES_1997 com_CCODENAME_1997;
SPOOL OFF;
-- setup vars
-- &1 = FREQ_QNAME TABLE
-- &2 = FREQ_TOTAL TABLE
-- &3 = FREQ_COUNT TABLE
-- &4 = FREQ_ERRORS TABLE
-- &5 = FREQ_JUMP TABLE (for data with no dcode or ccode and jumps)
-- freq vars
-- &1 = FREQ_QNAMES TABLE
-- &2 = DATA TABLE
-- &3 = QUESTION TABLE
-- &4 = JUMP TABLE
-- &5 = DCODES
-- &6 = DCODENAME
-- &7 = CCODES TABLE
-- &8 = CCODENAME TABLE
-- &9 = FREQ_TOTAL TABLE
-- &10 = FREQ_COUNT TABLE
-- &11 = FREQ_ERRORS TABLE
-- &12 = FREQ_JUMP TABLE
-- &13 = NOREPLACE_FREQS
-- &14 = DELETE_QNAMES
-- &15 = NOSUBSET_DATANAMES
-- &16 = EXTENTIONS;
-- &17 = "ALPHAS" (OR "NOALPHAS")
-- &18 = NAME OF QNAME COLUMN IN DATA TABLE
SPOOL D:\DOC_PROGS\FREQS\FREQS_NLSY97_rnd1.LOG;
SET TIME ON;
START D:\DOC_PROGS\FREQS\FREQS_SETUP.SQL com_FREQ_QNAMES_1997 com_FREQ_TOTAL_1997 com_FREQ_COUNT_1997 com_FREQ_ERRORS_1997 com_FREQ_JUMP_1997;
DROP INDEX FREQ_TOTAL_1997_DQNAME_KEY;
DROP INDEX FREQ_TOTAL_1997_MQNAME_KEY;
DROP INDEX FREQ_COUNT_1997_DQNAME_KEY;
START D:\DOC_PROGS\FREQS\FREQS.SQL COM_FREQ_QNAMES_1997 SYSTEM.YTH97 COM_QUESTION_1997 COM_JUMP_1997 com_DCODES_1997 com_DCODENAME_1997 com_CCODES_1997 com_CCODENAME_1997 COM_FREQ_TOTAL_1997 COM_FREQ_COUNT_1997 COM_FREQ_ERRORS_1997 COM_FREQ_JUMP_1997 NOREPLACE_FREQS DELETE_QNAMES NOSUBSET_DATANAMES OLD_EXTENTIONS ALPHAS QNAME;
START D:\DOC_PROGS\FREQS\FREQS.SQL COM_FREQ_QNAMES_1997 SYSTEM.YTH_FLIP COM_QUESTION_1997 COM_JUMP_1997 com_DCODES_1997 com_DCODENAME_1997 com_CCODES_1997 com_CCODENAME_1997 COM_FREQ_TOTAL_1997 COM_FREQ_COUNT_1997 COM_FREQ_ERRORS_1997 COM_FREQ_JUMP_1997 NOREPLACE_FREQS DELETE_QNAMES NOSUBSET_DATANAMES OLD_EXTENTIONS ALPHAS QNAME;
START D:\DOC_PROGS\FREQS\FREQS.SQL COM_FREQ_QNAMES_1997 SYSTEM.CREATED_YTH97 COM_QUESTION_1997 COM_JUMP_1997 com_DCODES_1997 com_DCODENAME_1997 com_CCODES_1997 com_CCODENAME_1997 COM_FREQ_TOTAL_1997 COM_FREQ_COUNT_1997 COM_FREQ_ERRORS_1997 COM_FREQ_JUMP_1997 NOREPLACE_FREQS DELETE_QNAMES NOSUBSET_DATANAMES OLD_EXTENTIONS ALPHAS QNAME;
CREATE INDEX FREQ_TOTAL_1997_DQNAME_KEY ON COM_FREQ_TOTAL_1997 (DATA_QNAME) TABLESPACE DOCS_INDEX;
CREATE INDEX FREQ_TOTAL_1997_MQNAME_KEY ON COM_FREQ_TOTAL_1997 (MGR_QNAME) TABLESPACE DOCS_INDEX;
CREATE INDEX FREQ_COUNT_1997_DQNAME_KEY ON COM_FREQ_COUNT_1997 (DATA_QNAME) TABLESPACE DOCS_INDEX;
CREATE INDEX FREQ_JUMP_1997_MQNAME_KEY ON COM_FREQ_JUMP_1997 (MGR_QNAME) TABLESPACE DOCS_INDEX;
SPOOL OFF;
SPOOL D:\DOC_PROGS\FREQS\FREQS_NLSY97_rnd1.ERRLOG;
start D:\DOC_PROGS\FREQS\freqs_CHECK.sql COM_question_1997 COM_freq_TOTAL_1997 COM_freq_errors_1997 COM_freq_COUNT_1997 you_interviews;
SPOOL OFF;
Frequency SETUP program AS OF 09/29/1999:
-- setup vars
-- &1 = FREQ_QNAME TABLE
-- &2 = FREQ_TRACK TABLE
-- &3 = FREQ_COUNTER TABLE
-- &4 = FREQ_ERRORS TABLE
-- &5 = FREQ_JUMP TABLE
DROP TABLE &1;
CREATE TABLE &1
(QNAME VARCHAR2 (75));
DROP TABLE &2;
CREATE TABLE &2
(DATA_QNAME VARCHAR2 (75),
MGR_QNAME VARCHAR2 (75),
VALID_TOTAL NUMBER (8),
ALL_TOTAL NUMBER (8),
ALL_MIN NUMBER (15),
ALL_MAX NUMBER (15),
VALID_MIN NUMBER (15),
VALID_MAX NUMBER (15),
SUMTOTAL NUMBER (15),
ALPHAMAX number (10),
MARKALL_ORDER NUMBER (15));
DROP TABLE &3;
CREATE TABLE &3
(DATA_QNAME VARCHAR2 (75),
LOWVAL NUMBER (10),
HIGHVAL NUMBER (10),
COUNT NUMBER (8));
drop table &4;
CREATE TABLE &4
(text varchar2 (50),
DATA_qname varchar2 (75),
MGR_QNAME VARCHAR2 (75),
VALUE number (12))
tablespace TEMP;
DROP TABLE &5;
CREATE TABLE &5
(MGR_QNAME VARCHAR2 (75),
JUMPMIN NUMBER (10),
JUMPMAX NUMBER (10),
COUNT NUMBER (10));
Frequency Program AS OF 09/29/1999:
-- freq vars
-- &1 = FREQ_QNAMES TABLE
-- &2 = DATA TABLE
-- &3 = QUESTION TABLE
-- &4 = JUMP TABLE
-- &5 = DCODES
-- &6 = DCODENAME
-- &7 = CCODES TABLE
-- &8 = CCODENAME TABLE
-- &9 = FREQ_TRACK TABLE
-- &10 = FREQ_COUNTER TABLE
-- &11 = FREQ_ERRORS TABLE
-- &12 = FREQ_JUMP TABLE
-- &13 = NOREPLACE_FREQS
-- &14 = DELETE_QNAMES
-- &15 = NOSUBSET_DATANAMES
-- &16 = NEW_EXTENTIONS;
-- &17 = "ALPHAS" (OR "NOALPHAS")
-- &18 = NAME OF QNAME COLUMN IN TABLE
SET ECHO ON;
-- do some cleanups on the manager records
UPDATE &3 SET QNAME=RTRIM (LTRIM (QNAME));
UPDATE &3 SET QDEFNEXT=RTRIM (LTRIM (QDEFNEXT));
UPDATE &4 SET QJMPGOTO=RTRIM (LTRIM (QJMPGOTO));
DELETE FROM &4 WHERE QJMPMAX IS NULL;
DELETE FROM &4 WHERE QJMPMIN IS NULL;
DELETE FROM &4 WHERE QJMPGOTO IS NULL;
UPDATE &4 SET QJMPMAX=QJMPMAX*10 WHERE INSTR (QJMPMAX,'.') > 0 AND QNUM IN (SELECT QNUM FROM &3 WHERE QDATADECLEN = 1);
UPDATE &4 SET QJMPMAX=QJMPMAX*100 WHERE INSTR (QJMPMAX,'.') > 0 AND QNUM IN (SELECT QNUM FROM &3 WHERE QDATADECLEN = 2);
UPDATE &4 SET QJMPMIN=QJMPMIN*10 WHERE INSTR (QJMPMIN,'.') > 0 AND QNUM IN (SELECT QNUM FROM &3 WHERE QDATADECLEN = 1);
UPDATE &4 SET QJMPMIN=QJMPMIN*100 WHERE INSTR (QJMPMIN,'.') > 0 AND QNUM IN (SELECT QNUM FROM &3 WHERE QDATADECLEN = 2);
COMMIT;
DECLARE
VAR_DISTINCT_COUNT NUMBER (10);
VAR_QNAME VARCHAR2 (75);
VAR_TRUNCATE_FLAG VARCHAR2 (25) := '&14';
VAR_QNAME_FLAG VARCHAR2 (25) := '&15';
CURSOR QNAME_CURSOR IS
SELECT QNAME FROM &3;
BEGIN
IF (VAR_TRUNCATE_FLAG = 'DELETE_QNAMES') THEN
DELETE FROM &1;
COMMIT;
END IF;
SELECT COUNT(*) INTO VAR_DISTINCT_COUNT FROM &1;
IF (VAR_DISTINCT_COUNT = 0) THEN
IF (VAR_QNAME_FLAG = 'SUBSET_DATANAMES') THEN
OPEN QNAME_CURSOR;
LOOP
FETCH QNAME_CURSOR INTO VAR_QNAME;
EXIT WHEN QNAME_CURSOR%NOTFOUND;
VAR_QNAME := VAR_QNAME || '%';
INSERT INTO &1 (SELECT DISTINCT (&18) FROM &2 WHERE &18 LIKE VAR_QNAME);
END LOOP;
CLOSE QNAME_CURSOR;
ELSE
INSERT INTO &1 (SELECT DISTINCT (&18) FROM &2);
END IF;
END IF;
END;
/
DROP INDEX FREQ_QNAME_INDEX_KEY;
CREATE INDEX FREQ_QNAME_INDEX_KEY ON &1 (QNAME);
DECLARE
VAR_QNAME VARCHAR2 (75);
VAR_RESPONSE NUMBER (15);
VAR_COUNT NUMBER (15);
VAR_STRIPPEDQNAME VARCHAR2 (75);
VAR_FOUND NUMBER (2);
VAR_TOTAL NUMBER (12);
VAR_ALL_TOTAL NUMBER (12);
VAR_SUMTOTAL NUMBER (15);
VAR_MIN NUMBER (12);
VAR_MAX NUMBER (12);
VAR_ALL_MIN NUMBER (12);
VAR_ALL_MAX NUMBER (12);
VAR_ALPHAMAX NUMBER (10);
VAR_QTYPE NUMBER (2);
VAR_QMARKTYPE NUMBER (2);
VAR_DATATYPE NUMBER (2);
VAR_DCODE VARCHAR2 (25);
VAR_CCODE VARCHAR2 (25);
VAR_DCODEVALUE NUMBER (12);
VAR_CCODEHIGHVALUE NUMBER (12);
VAR_CCODELOWVALUE NUMBER (12);
VAR_DCODENUM NUMBER (8);
VAR_CCODENUM NUMBER (8);
VAR_DCODESEQ NUMBER (12);
VAR_JUMPMIN NUMBER (10);
VAR_JUMPMAX NUMBER (10);
VAR_LOOP VARCHAR2 (20);
VAR_LOOP1 NUMBER (2);
VAR_LOOP2 NUMBER (2);
VAR_LOOP3 NUMBER (2);
VAR_LOOP4 NUMBER (2);
VAR_DCODEROWS NUMBER (5) := 0;
VAR_CCODEROWS NUMBER (5) := 0;
VAR_JUMPROWS NUMBER (5) := 0;
VAR_DISTINCT_COUNT NUMBER (8) := 0;
VAR_ERROR_COUNT NUMBER (8) := 0;
VAR_CHECK_COUNT NUMBER (2) := 0;
VAR_FIRST_FOUND NUMBER (2) := 0;
VAR_REPLACE_FLAG VARCHAR2 (25) := '&13';
VAR_TRUNCATE_FLAG VARCHAR2 (25) := '&14';
VAR_QNAME_FLAG VARCHAR2 (25) := '&15';
VAR_EXTTYPE_FLAG VARCHAR2 (25) := '&16';
VAR_ALPHA_FLAG VARCHAR2 (25) := '&17';
VAR_I NUMBER (5);
/* END VARS */
TYPE INTEGER_TABLETYPE IS TABLE OF NUMBER (12)
INDEX BY BINARY_INTEGER;
DCODE_VALUE INTEGER_TABLETYPE;
DCODE_COUNT INTEGER_TABLETYPE;
CCODE_LOWVALUE INTEGER_TABLETYPE;
CCODE_HIGHVALUE INTEGER_TABLETYPE;
CCODE_COUNT INTEGER_TABLETYPE;
JUMP_MINVALUE INTEGER_TABLETYPE;
JUMP_MAXVALUE INTEGER_TABLETYPE;
JUMP_COUNT INTEGER_TABLETYPE;
VAR_INDEX BINARY_INTEGER := 0;
CURSOR DISTINCT_CURSOR IS
SELECT DISTINCT (QNAME) FROM &1;
CURSOR DATA_CURSOR (VAR_QNAME VARCHAR2) IS
SELECT RESPONSE FROM &2
WHERE &18 = VAR_QNAME;
CURSOR QNAME_CURSOR IS
SELECT QNAME FROM &3;
CURSOR QUESTION_CURSOR (VAR_STRIPPEDQNAME VARCHAR2) IS
SELECT QDOCTYPE, QDOCDATATYPE, QDOCDCODE, QCONTCODE, QMARKTYPE
FROM &3
WHERE QNAME=VAR_STRIPPEDQNAME;
CURSOR JUMP_CURSOR (VAR_STRIPPEDQNAME VARCHAR2) IS
SELECT QJMPMIN, QJMPMAX
FROM &4
WHERE QNUM=(SELECT QNUM FROM &3 WHERE QNAME=VAR_STRIPPEDQNAME);
CURSOR DCODE_CURSOR (VAR_DCODE VARCHAR2) IS
SELECT DCODEVALUE FROM &5
WHERE DCODENUM=(SELECT DCODENUM FROM &6 WHERE DCODENAME = VAR_DCODE) AND
(LANGUAGE <= 1)
ORDER BY DCODEVALUE;
CURSOR CCODE_CURSOR (VAR_CCODE VARCHAR2) IS
SELECT CCODELOWVAL, CCODEHIGHVAL FROM &7
WHERE CCODENUM=(SELECT CCODENUM FROM &8 WHERE CCODENAME = VAR_CCODE) AND
(LANGUAGE <= 1)
ORDER BY CCODELOWVAL;
BEGIN
/* start main block of frequency program */
DCODE_VALUE(-3) := -3;
DCODE_VALUE(-2) := -2;
DCODE_VALUE(-1) := -1;
CCODE_HIGHVALUE(-3) := -3;
CCODE_HIGHVALUE(-2) := -2;
CCODE_HIGHVALUE(-1) := -1;
CCODE_LOWVALUE(-3) := -3;
CCODE_LOWVALUE(-2) := -2;
CCODE_LOWVALUE(-1) := -1;
open DISTINCT_CURSOR;
LOOP
FETCH DISTINCT_CURSOR INTO VAR_QNAME;
EXIT WHEN DISTINCT_CURSOR%NOTFOUND;
VAR_TOTAL:= 0;
VAR_ALL_TOTAL:= 0;
VAR_SUMTOTAL := 0;
VAR_MIN := 9999999999;
VAR_MAX := -1;
VAR_ALL_MIN := 9999999999;
VAR_ALL_MAX := -999999999;
VAR_ALPHAMAX := 0;
VAR_QTYPE := 0;
VAR_QMARKTYPE := 0;
VAR_DATATYPE := 0;
VAR_DCODE := '';
VAR_CCODE := '';
VAR_DCODEROWS := -1;
VAR_CCODEROWS := -1;
VAR_JUMPROWS := -1;
DCODE_COUNT(-3) := 0;
DCODE_COUNT(-2) := 0;
DCODE_COUNT(-1) := 0;
CCODE_COUNT(-3) := 0;
CCODE_COUNT(-2) := 0;
CCODE_COUNT(-1) := 0;
VAR_DCODESEQ := NULL;
VAR_STRIPPEDQNAME := rtrim (VAR_QNAME);
VAR_QNAME := rtrim (VAR_QNAME);
VAR_FIRST_FOUND := 0;
OPEN QUESTION_CURSOR (VAR_QNAME);
FETCH QUESTION_CURSOR INTO VAR_QTYPE, VAR_DATATYPE, VAR_DCODE, VAR_CCODE, VAR_QMARKTYPE;
IF (QUESTION_CURSOR%FOUND) THEN
VAR_FIRST_FOUND := 1;
END IF;
IF (QUESTION_CURSOR%FOUND) OR
((QUESTION_CURSOR%NOTFOUND) AND (SUBSTR (VAR_QNAME, -2, 2) <> '_C') AND (SUBSTR (VAR_QNAME, -2, 2) <> '_V') AND
(SUBSTR (VAR_QNAME, -2, 2) <> '_O') AND (SUBSTR (VAR_QNAME, -2, 2) <> '~C') AND
(SUBSTR (VAR_QNAME, -2, 2) <> '~V') AND (SUBSTR (VAR_QNAME, -2, 2) <> '~O') AND
(SUBSTR (VAR_QNAME, -2, 2) <> '~R')) THEN
IF (QUESTION_CURSOR%NOTFOUND) THEN
IF (VAR_EXTTYPE_FLAG = 'NEW_EXTENTIONS') THEN
IF (INSTR (VAR_STRIPPEDQNAME, '~') > 0) THEN
VAR_STRIPPEDQNAME := SUBSTR (VAR_STRIPPEDQNAME, 1, INSTR (VAR_STRIPPEDQNAME,'~') - 1);
END IF;
CLOSE QUESTION_CURSOR;
OPEN QUESTION_CURSOR (VAR_STRIPPEDQNAME);
FETCH QUESTION_CURSOR INTO VAR_QTYPE, VAR_DATATYPE, VAR_DCODE, VAR_CCODE, VAR_QMARKTYPE;
IF (QUESTION_CURSOR%NOTFOUND) AND (INSTR (VAR_STRIPPEDQNAME, '.') > 0) THEN
VAR_STRIPPEDQNAME := SUBSTR (VAR_STRIPPEDQNAME, 1, INSTR (VAR_STRIPPEDQNAME,'.') - 1);
CLOSE QUESTION_CURSOR;
OPEN QUESTION_CURSOR (VAR_STRIPPEDQNAME);
FETCH QUESTION_CURSOR INTO VAR_QTYPE, VAR_DATATYPE, VAR_DCODE, VAR_CCODE, VAR_QMARKTYPE;
END IF;
ELSE
IF (SUBSTR (VAR_STRIPPEDQNAME, -4, 2) = '_0') THEN
VAR_STRIPPEDQNAME := SUBSTR (VAR_STRIPPEDQNAME,1, INSTR (VAR_STRIPPEDQNAME, '_0') - 1);
CLOSE QUESTION_CURSOR;
OPEN QUESTION_CURSOR (VAR_STRIPPEDQNAME);
FETCH QUESTION_CURSOR INTO VAR_QTYPE, VAR_DATATYPE, VAR_DCODE, VAR_CCODE, VAR_QMARKTYPE;
END IF;
IF (QUESTION_CURSOR%NOTFOUND) AND (SUBSTR (VAR_STRIPPEDQNAME, -2, 1) = '_') THEN
VAR_STRIPPEDQNAME := SUBSTR (VAR_STRIPPEDQNAME, 1, LENGTH(VAR_STRIPPEDQNAME)-2);
CLOSE QUESTION_CURSOR;
OPEN QUESTION_CURSOR (VAR_STRIPPEDQNAME);
FETCH QUESTION_CURSOR INTO VAR_QTYPE, VAR_DATATYPE, VAR_DCODE, VAR_CCODE, VAR_QMARKTYPE;
END IF;
IF (QUESTION_CURSOR%NOTFOUND) AND (INSTR (VAR_STRIPPEDQNAME,'.') > 0) THEN
VAR_STRIPPEDQNAME := SUBSTR (VAR_STRIPPEDQNAME,1, INSTR (VAR_STRIPPEDQNAME,'.') - 1);
CLOSE QUESTION_CURSOR;
OPEN QUESTION_CURSOR (VAR_STRIPPEDQNAME);
FETCH QUESTION_CURSOR INTO VAR_QTYPE, VAR_DATATYPE, VAR_DCODE, VAR_CCODE, VAR_QMARKTYPE;
END IF;
END IF;
END IF;
IF QUESTION_CURSOR%NOTFOUND THEN
CLOSE QUESTION_CURSOR;
INSERT INTO &11 (TEXT,DATA_QNAME,MGR_QNAME) VALUES ('MANAGER REC NOT FOUND',VAR_QNAME,VAR_STRIPPEDQNAME);
ELSE
CLOSE QUESTION_CURSOR;
IF (VAR_QTYPE = 5) AND (VAR_DATATYPE <> 7) THEN
INSERT INTO &11 (TEXT,DATA_QNAME,MGR_QNAME) VALUES ('QTYPE IS TEXT ENTRY, DATA TYPE IS NOT CHAR',VAR_QNAME,VAR_STRIPPEDQNAME);
VAR_DATATYPE := 7;
END IF;
IF (VAR_QTYPE = 1) AND (VAR_QMARKTYPE = 3) AND (VAR_DATATYPE = 7) THEN
VAR_DATATYPE := 1;
END IF;
IF ((SUBSTR (VAR_QNAME,-2,2) = '_D') OR (SUBSTR (VAR_QNAME,-2,2) = '~D')) AND
(VAR_DATATYPE <> 5) AND (VAR_FIRST_FOUND = 0) THEN
NULL;
ELSIF (VAR_DATATYPE = 7) AND ((VAR_QTYPE = 1) OR (VAR_QTYPE = 2) OR (VAR_QTYPE = 3) OR
(VAR_QTYPE = 9) OR (VAR_QTYPE = 12)) THEN
INSERT INTO &11 (TEXT, DATA_QNAME, MGR_QNAME) VALUES ('QTYPE CONFLICTS WITH QDATATYPE OF CHAR',VAR_QNAME,VAR_STRIPPEDQNAME);
ELSIF (VAR_DATATYPE = 7) THEN
NULL;
ELSIF (VAR_QMARKTYPE = 3) THEN
IF ((VAR_EXTTYPE_FLAG <> 'NEW_EXTENTIONS') AND (INSTR (VAR_QNAME, '_0') = 0)) OR
((VAR_EXTTYPE_FLAG = 'NEW_EXTENTIONS') AND (INSTR (VAR_QNAME, '~0') = 0)) THEN
INSERT INTO &11 (TEXT,DATA_QNAME,MGR_QNAME) VALUES ('QNAME SHOULD NOT BE MARKALL',VAR_QNAME,VAR_STRIPPEDQNAME);
VAR_QMARKTYPE := 0;
ELSE
IF (VAR_EXTTYPE_FLAG = 'NEW_EXTENTIONS') THEN
VAR_DCODEVALUE := TO_NUMBER (SUBSTR (VAR_QNAME, INSTR (VAR_QNAME, '~0') + 1, LENGTH (VAR_QNAME)));
IF (VAR_DCODE IS NOT NULL) THEN
SELECT COUNT(*) INTO VAR_COUNT
FROM &5
WHERE DCODENUM=(SELECT DCODENUM FROM &6 WHERE DCODENAME = VAR_DCODE) AND
((LANGUAGE IS NULL) OR (LANGUAGE <= 1)) AND
DCODEVALUE = VAR_DCODEVALUE;
IF (VAR_COUNT = 0) THEN
INSERT INTO &11 (TEXT,DATA_QNAME,MGR_QNAME) VALUES ('MARKALL Q EXT HAS NO DCODE MATCH',VAR_QNAME,VAR_STRIPPEDQNAME);
ELSE
SELECT min(DCODESEQ) INTO VAR_DCODESEQ
FROM &5
WHERE DCODENUM=(SELECT DCODENUM FROM &6 WHERE DCODENAME = VAR_DCODE) AND
((LANGUAGE IS NULL) OR (LANGUAGE <= 1)) AND
DCODEVALUE = VAR_DCODEVALUE;
END IF;
ELSE
INSERT INTO &11 (TEXT,DATA_QNAME,MGR_QNAME) VALUES ('MARKALL Q HAS NO DCODE',VAR_QNAME,VAR_STRIPPEDQNAME);
END IF;
ELSE
VAR_DCODESEQ := TO_NUMBER (SUBSTR (VAR_QNAME, INSTR (VAR_QNAME, '_0') + 1, LENGTH (VAR_QNAME)));
IF (VAR_DCODE IS NOT NULL) THEN
SELECT COUNT(*) INTO VAR_COUNT
FROM &5
WHERE DCODENUM=(SELECT DCODENUM FROM &6 WHERE DCODENAME = VAR_DCODE) AND
((LANGUAGE IS NULL) OR (LANGUAGE <= 1)) AND
DCODESEQ = VAR_DCODESEQ;
IF (VAR_COUNT = 0) THEN
INSERT INTO &11 (TEXT,DATA_QNAME,MGR_QNAME) VALUES ('MARKALL Q EXT HAS NO DCODE MATCH',VAR_QNAME,VAR_STRIPPEDQNAME);
ELSE
SELECT DCODEVALUE INTO VAR_DCODEVALUE
FROM &5
WHERE DCODENUM=(SELECT DCODENUM FROM &6 WHERE DCODENAME = VAR_DCODE) AND
((LANGUAGE IS NULL) OR (LANGUAGE <= 1)) AND
DCODESEQ = VAR_DCODESEQ;
END IF;
ELSE
INSERT INTO &11 (TEXT,DATA_QNAME,MGR_QNAME) VALUES ('MARKALL Q HAS NO DCODE',VAR_QNAME,VAR_STRIPPEDQNAME);
END IF;
END IF;
FOR VAR_I IN 0..1 LOOP
DCODE_VALUE(VAR_I) := VAR_I;
DCODE_COUNT(VAR_I) := 0;
END LOOP;
VAR_DCODEROWS := 1;
IF (INSTR (VAR_QNAME,'.') > 0) THEN
IF (VAR_EXTTYPE_FLAG = 'NEW_EXTENTIONS') THEN
VAR_LOOP := RTRIM (SUBSTR (VAR_QNAME,INSTR (VAR_QNAME,'.'), INSTR (VAR_QNAME,'~') - INSTR (VAR_QNAME,'.')));
ELSE
VAR_LOOP := RTRIM (SUBSTR (VAR_QNAME,INSTR (VAR_QNAME,'.'), LENGTH (VAR_QNAME)));
VAR_LOOP := RTRIM (SUBSTR (VAR_LOOP,1,INSTR (VAR_LOOP,'_')));
END IF;
VAR_LOOP1 := 1; VAR_LOOP2 := 1; VAR_LOOP3 := 1; VAR_LOOP4 := 1;
IF (LENGTH (VAR_LOOP) > 9) THEN
VAR_LOOP4 := TO_NUMBER (SUBSTR (VAR_LOOP,11,2));
END IF;
IF (LENGTH (VAR_LOOP) > 6) THEN
VAR_LOOP3 := TO_NUMBER (SUBSTR (VAR_LOOP,8,2));
END IF;
IF (LENGTH (VAR_LOOP) > 3) THEN
VAR_LOOP2 := TO_NUMBER (SUBSTR (VAR_LOOP,5,2));
END IF;
VAR_LOOP1 := TO_NUMBER (SUBSTR (VAR_LOOP,2,2));
VAR_DCODESEQ := (VAR_LOOP1 * 10000000000) + (VAR_LOOP2 * 100000000) + (VAR_LOOP3 * 1000000) +
(VAR_LOOP4 * 10000) + (100 + VAR_DCODESEQ);
END IF;
END IF;
ELSIF ((VAR_DATATYPE = 5) OR (VAR_DATATYPE = 9)) AND
((SUBSTR (VAR_QNAME,-2,2) = '_M') OR (SUBSTR (VAR_QNAME,-2,2) = '~M')) AND
(VAR_FIRST_FOUND = 0) THEN
FOR VAR_I IN 0..11 LOOP
DCODE_VALUE(VAR_I) := VAR_I + 1;
DCODE_COUNT(VAR_I) := 0;
END LOOP;
VAR_DCODEROWS := 11;
ELSIF ((VAR_DATATYPE = 5) OR (VAR_DATATYPE = 9)) AND
((SUBSTR (VAR_QNAME,-2,2) = '_D') OR (SUBSTR (VAR_QNAME,-2,2) = '~D')) AND
(VAR_FIRST_FOUND = 0) THEN
IF (VAR_DATATYPE = 9) THEN
INSERT INTO &11 (TEXT,DATA_QNAME,MGR_QNAME) VALUES ('DATATYPE IS MY, SHOULD BE MDY',VAR_QNAME,VAR_STRIPPEDQNAME);
END IF;
ELSIF ((VAR_DATATYPE = 5) OR (VAR_DATATYPE = 9)) AND
((SUBSTR (VAR_QNAME,-2,2) = '_Y') OR (SUBSTR (VAR_QNAME,-2,2) = '~Y')) AND
(VAR_FIRST_FOUND = 0) THEN
NULL;
ELSIF ((VAR_DATATYPE = 5) OR (VAR_DATATYPE = 9)) THEN
NULL;
ELSIF ((VAR_DCODE IS NOT NULL) OR (VAR_CCODE IS NOT NULL)) THEN
IF ((VAR_DCODE IS NOT NULL) AND (VAR_CCODE IS NOT NULL)) THEN
IF (VAR_QTYPE <> 1) THEN
VAR_DCODE := NULL;
END IF;
END IF;
IF (VAR_DCODE IS NOT NULL) THEN
OPEN DCODE_CURSOR (VAR_DCODE);
VAR_INDEX := -1;
LOOP
FETCH DCODE_CURSOR INTO VAR_DCODEVALUE;
EXIT WHEN DCODE_CURSOR%NOTFOUND;
VAR_INDEX := VAR_INDEX + 1;
DCODE_VALUE(VAR_INDEX) := VAR_DCODEVALUE;
DCODE_COUNT(VAR_INDEX) := 0;
END LOOP;
VAR_DCODEROWS := VAR_INDEX;
CLOSE DCODE_CURSOR;
END IF;
IF ((VAR_CCODE IS NOT NULL) AND (VAR_DCODEROWS = -1)) THEN
OPEN CCODE_CURSOR (VAR_CCODE);
VAR_INDEX := -1;
LOOP
FETCH CCODE_CURSOR INTO VAR_CCODELOWVALUE, VAR_CCODEHIGHVALUE;
EXIT WHEN CCODE_CURSOR%NOTFOUND;
VAR_INDEX := VAR_INDEX + 1;
CCODE_LOWVALUE(VAR_INDEX) := VAR_CCODELOWVALUE;
CCODE_HIGHVALUE(VAR_INDEX) := VAR_CCODEHIGHVALUE;
CCODE_COUNT(VAR_INDEX) := 0;
END LOOP;
VAR_CCODEROWS := VAR_INDEX;
CLOSE CCODE_CURSOR;
END IF;
IF ((VAR_DCODEROWS = -1) AND (VAR_CCODEROWS = -1)) THEN
INSERT INTO &11 (TEXT,DATA_QNAME,MGR_QNAME) VALUES ('DCODE OR CCODE NOT FOUND',VAR_QNAME,VAR_STRIPPEDQNAME);
END IF;
END IF;
/* COLLECT JUMP INFORMATION TO DO COUNTS PER JUMP */
OPEN JUMP_CURSOR (VAR_STRIPPEDQNAME);
VAR_INDEX := -1;
LOOP
FETCH JUMP_CURSOR INTO VAR_JUMPMIN,VAR_JUMPMAX;
EXIT WHEN JUMP_CURSOR%NOTFOUND;
VAR_INDEX := VAR_INDEX + 1;
JUMP_MINVALUE(VAR_INDEX) := VAR_JUMPMIN;
JUMP_MAXVALUE(VAR_INDEX) := VAR_JUMPMAX;
JUMP_COUNT(VAR_INDEX) := 0;
END LOOP;
VAR_JUMPROWS := VAR_INDEX;
CLOSE JUMP_CURSOR;
/* START COLLECTING REAL COUNTS - SET UP AND INITIALIZATION HAVE BEEN COMPLETED */
IF ((SUBSTR (VAR_QNAME,-2,2) = '_D') OR (SUBSTR (VAR_QNAME,-2,2) = '~D')) AND
(VAR_DATATYPE <> 5) AND (VAR_FIRST_FOUND = 0) THEN
NULL;
ELSE
OPEN DATA_CURSOR (VAR_QNAME);
LOOP
FETCH DATA_CURSOR INTO VAR_RESPONSE;
EXIT WHEN DATA_CURSOR%NOTFOUND;
IF (VAR_DATATYPE = 7) THEN
NULL;
ELSIF (VAR_CCODEROWS >= 0) THEN
VAR_FOUND := 0;
FOR VAR_I IN -3..VAR_CCODEROWS LOOP
IF (VAR_RESPONSE >= CCODE_LOWVALUE(VAR_I)) AND (VAR_RESPONSE <= CCODE_HIGHVALUE(VAR_I)) THEN
CCODE_COUNT(VAR_I) := CCODE_COUNT(VAR_I) + 1;
VAR_FOUND := 1;
EXIT;
END IF;
END LOOP;
IF (VAR_FOUND = 0) THEN
IF (VAR_RESPONSE IS NULL) THEN
INSERT INTO &11 (DATA_QNAME,TEXT,MGR_QNAME) VALUES
(VAR_QNAME,'UNDOCUMENTED CCODE RESPONSE - NULL',VAR_STRIPPEDQNAME);
ELSE
INSERT INTO &11 (DATA_QNAME,TEXT,VALUE,MGR_QNAME) VALUES
(VAR_QNAME,'UNDOCUMENTED CCODE RESPONSE',VAR_RESPONSE,VAR_STRIPPEDQNAME);
END IF;
END IF;
ELSIF (VAR_DCODEROWS >= 0) THEN
VAR_FOUND := 0;
FOR VAR_I IN -3..VAR_DCODEROWS LOOP
IF (VAR_RESPONSE = DCODE_VALUE(VAR_I)) THEN
DCODE_COUNT(VAR_I) := DCODE_COUNT(VAR_I) + 1;
VAR_FOUND := 1;
EXIT;
ELSIF ((VAR_RESPONSE > 0) AND (VAR_QMARKTYPE = 3) AND (VAR_I > 0)) THEN
DCODE_COUNT(VAR_I) := DCODE_COUNT(VAR_I) + 1;
VAR_FOUND := 1;
END IF;
END LOOP;
IF (VAR_FOUND = 0) THEN
IF (VAR_RESPONSE IS NULL) THEN
INSERT INTO &11 (DATA_QNAME,TEXT,MGR_QNAME) VALUES (VAR_QNAME,'UNDOCUMENTED DCODE RESPONSE - NULL',VAR_STRIPPEDQNAME);
ELSE
INSERT INTO &11 (DATA_QNAME,TEXT,VALUE,MGR_QNAME) VALUES (VAR_QNAME,'UNDOCUMENTED DCODE RESPONSE',VAR_RESPONSE,VAR_STRIPPEDQNAME);
END IF;
END IF;
ELSE
FOR VAR_I IN -3..-1 LOOP
IF (VAR_RESPONSE = DCODE_VALUE(VAR_I)) THEN
DCODE_COUNT(VAR_I) := DCODE_COUNT(VAR_I) + 1;
EXIT;
END IF;
END LOOP;
END IF;
IF (VAR_JUMPROWS >= 0) THEN
FOR VAR_I IN 0..VAR_JUMPROWS LOOP
IF (VAR_RESPONSE >= JUMP_MINVALUE(VAR_I)) AND (VAR_RESPONSE <= JUMP_MAXVALUE(VAR_I)) THEN
JUMP_COUNT(VAR_I) := JUMP_COUNT(VAR_I) + 1;
END IF;
END LOOP;
END IF;
VAR_ALL_TOTAL := VAR_ALL_TOTAL + 1;
IF ((VAR_RESPONSE < VAR_ALL_MIN) OR (VAR_ALL_MIN = 9999999999)) THEN
VAR_ALL_MIN := VAR_RESPONSE;
END IF;
IF ((VAR_RESPONSE > VAR_ALL_MAX) OR (VAR_ALL_MAX = -999999999)) THEN
VAR_ALL_MAX := VAR_RESPONSE;
END IF;
IF (VAR_DATATYPE = 7) THEN
VAR_TOTAL := VAR_TOTAL + 1;
ELSIF ((VAR_RESPONSE >= 0) OR (VAR_RESPONSE <= -6)) THEN
VAR_TOTAL := VAR_TOTAL + 1;
VAR_SUMTOTAL := VAR_SUMTOTAL + VAR_RESPONSE;
IF ((VAR_RESPONSE < VAR_MIN) OR (VAR_MIN = 9999999999)) THEN
VAR_MIN := VAR_RESPONSE;
END IF;
IF ((VAR_RESPONSE > VAR_MAX) OR (VAR_MAX = -1)) THEN
VAR_MAX := VAR_RESPONSE;
END IF;
END IF;
END LOOP;
CLOSE DATA_CURSOR;
/* INSERT RECORDS INTO FREQ TABLES */
/* IF THIS FREQUENCY WAS A PARTIAL, DELETE OLD FREQUENCIES */
IF (VAR_REPLACE_FLAG = 'REPLACE_FREQS') THEN
DELETE FROM &9 WHERE DATA_QNAME=VAR_QNAME;
DELETE FROM &10 WHERE DATA_QNAME=VAR_QNAME;
END IF;
/* RESET MIN AND MAX IF NOT APPLICABLE */
IF (VAR_MIN = 9999999999) THEN
VAR_MIN := NULL;
END IF;
IF (VAR_MAX = -1) OR (VAR_ALL_MAX = -999999999) THEN
VAR_MAX := NULL;
END IF;
/* GET MAX LENGTH FROM DATA RECORD FOR ALPHAS ONLY */
IF (VAR_DATATYPE = 7) THEN
IF (VAR_ALPHA_FLAG = 'NOALPHAS') THEN
INSERT INTO &11 (DATA_QNAME,TEXT,MGR_QNAME) VALUES (VAR_QNAME,'DATA TYPE IS CHAR, NO CHARS ALLOWED',VAR_STRIPPEDQNAME);
ELSE
SELECT MAX(LENGTH(RESPONSE_A)) INTO VAR_ALPHAMAX
FROM &2 WHERE &18=VAR_QNAME;
END IF;
END IF;
/* START ADDING JUMP COUNT RECORDS TO TABLE */
IF (VAR_JUMPROWS >= 0) THEN
FOR VAR_I IN 0..VAR_JUMPROWS LOOP
INSERT INTO &12 (MGR_QNAME, JUMPMIN, JUMPMAX, COUNT) VALUES
(VAR_STRIPPEDQNAME, JUMP_MINVALUE(VAR_I), JUMP_MAXVALUE(VAR_I),
JUMP_COUNT(VAR_I));
END LOOP;
END IF;
/* START ADDING TOTAL RECORD TO TABLE */
INSERT INTO &9 (DATA_QNAME, MGR_QNAME, VALID_TOTAL,ALL_TOTAL, ALL_MIN, ALL_MAX, VALID_MIN, VALID_MAX, SUMTOTAL, ALPHAMAX, MARKALL_ORDER) VALUES
(VAR_QNAME, VAR_STRIPPEDQNAME, VAR_TOTAL, VAR_ALL_TOTAL, VAR_ALL_MIN, VAR_ALL_MAX, VAR_MIN, VAR_MAX, VAR_SUMTOTAL, VAR_ALPHAMAX, VAR_DCODESEQ);
/* START ADDING COUNT RECORDS TO TABLE */
IF (VAR_DATATYPE = 7) THEN
NULL;
ELSIF (VAR_DCODEROWS >= 0) THEN
FOR VAR_I IN -3..VAR_DCODEROWS LOOP
INSERT INTO &10 (DATA_QNAME, LOWVAL, HIGHVAL, COUNT) VALUES
(VAR_QNAME, DCODE_VALUE(VAR_I), DCODE_VALUE(VAR_I),
DCODE_COUNT(VAR_I));
END LOOP;
ELSIF (VAR_CCODEROWS >= 0) THEN
FOR VAR_I IN -3..VAR_CCODEROWS LOOP
INSERT INTO &10 (DATA_QNAME, LOWVAL, HIGHVAL, COUNT) VALUES
(VAR_QNAME, CCODE_LOWVALUE(VAR_I), CCODE_HIGHVALUE(VAR_I),
CCODE_COUNT(VAR_I));
END LOOP;
ELSE
FOR VAR_I IN -3..-1 LOOP
INSERT INTO &10 (DATA_QNAME, LOWVAL, HIGHVAL, COUNT) VALUES
(VAR_QNAME, DCODE_VALUE(VAR_I), DCODE_VALUE(VAR_I),
DCODE_COUNT(VAR_I));
END LOOP;
END IF;
COMMIT;
END IF;
END IF;
ELSE
CLOSE QUESTION_CURSOR;
END IF;
DELETE FROM &1 WHERE QNAME=VAR_QNAME;
COMMIT;
END LOOP;
CLOSE DISTINCT_CURSOR;
END;
/
DROP INDEX FREQ_QNAME_INDEX_KEY;
select count(*) from &11;
drop table temp_errors;
create table temp_errors tablespace TEMP
as (select * from &11);
drop table &11;
create table &11 as (select DISTINCT * from temp_errors);
drop table temp_errors;
select count(*) from &11;
Frequency Report Program AS OF 09/29/1999:
-- &1 = Question table
-- &2 = Freq total table
-- &3 = Freq error table
-- &4 = Freq count table
-- &5 = Interviews table
SET LINESIZE 200;
SET HEADING ON;
SET PAGESIZE 55;
column qname format a25;
column data_qname format a50;
column mgr_qname format a50;
column qkwictitle format a80;
SET ECHO ON;
-- YEARS INCLUDED IN THE MANAGER RECS
select qyear, count(*)
from &1
group by qyear;
-- VARS WHERE COUNT IS > NUMBER OF INTERVIEWS FOR GIVEN YEAR
select FT.DATA_qname, QKWICTITLE, ints, nonints, all_total
from &2 FT, &5 I, &1 Q
where FT.mgr_qname = Q.qname and
Q.qyear = I.year and
all_total > ints;
-- FREQ ERROR REPORT
select text,data_qname,value from &3
where MGR_qname in (select qname from &1 where qprint='T')
order by text,data_qname;
-- VARS WITH MISSING OR NON-VALID SECURITY VALUES
select qname, qvartype
from &1
where qvartype is null OR ((QVARTYPE <> 'B') AND (QVARTYPE <> 'V') AND (QVARTYPE <> 'G') AND (QVARTYPE <> 'Z') AND
(QVARTYPE <> 'S'));
-- QPRINT='T' QUESTIONS WITH NO DCODE OR CCODE AND
-- NOT CHARACTER DATATYPE.
-- THESE QUESTION WILL SHOW UP WITH NO FREQUENCIES IN
-- THE CODEBOOK.
-- (modified to not include dummy questions)
column qkwictitle FORMAT a80;
column qname FORMAT a40;
select QNAME, QKWICTITLE from &1
where QPRINT = 'T' AND QDOCDCODE IS NULL AND
QCONTCODE IS NULL AND QDOCDATATYPE <> 7 AND QDOCTYPE <> 3 AND QTYPE <> 7
order by qorder;
-- MISSING KWICTITLE REPORT FOR QPRINT='T' VARS
select QNAME from &1
where QPRINT = 'T' AND QKWICTITLE IS NULL
order by qorder;
-- MISSING VARLABEL REPORT FOR QPRINT='T' VARS
select QNAME from &1
where QPRINT = 'T' AND QVARLABEL IS NULL
order by qorder;
-- MISSING FREQ REPORT FOR QPRINT='T' VARS
-- (modified to not include dummy questions)
select QNAME from &1
where QPRINT = 'T' AND
QTYPE <> 7 AND
QNAME NOT IN (SELECT MGR_QNAME FROM &2)
order by qname;
-- DUPLICATE TRACK ENTRIES FOR QPRINT='T' VARS
select FT.DATA_QNAME, count(*)
from &1 Q, &2 FT
where Q.qname = FT.mgr_qname AND QPRINT='T'
having count(*) > 1
group by FT.DATA_QNAME;
-- LIST OF PRINTFLAG=ON VARIABLES WHICH ARE MARKED AS PRIVATE
select QNAME from &1
where QPRINT = 'T' AND QVARTYPE='V'
order by qname;
-- LIST OF VARIABLES WHICH HAVE A TOTAL COUNT OF '0', BUT ARE NOT
-- OF TYPE CHAR. THIS MAY MEAN A PROBLEM IN THE DATA.
-- (modified to check only for printflag ON questions)
select FT.DATA_QNAME, sum (valid_TOTAL + COUNT)
from &2 FT, &4 FC
where FT.DATA_qname = FC.DATA_qname and
FT.MGR_qname IN (select qname from &1 where qprint='T' AND QDOCDATATYPE <> 7)
having sum (valid_total + count) = 0
group by FT.DATA_qname;