1 UPDATE
2 TRIO_STUDENT_MASTERF00S01part5 T
3 SET
4 (
5 T.T08_STUDENT_RACE_ETHNICITY
6 )
7 = (
8 SELECT
9 decode(
10 decode( X.STU_ETH_CODE,
11 'D', '0', /* Declined to State */
12 '8', '0', /* Other (Not Listed) (10/8/1999 ??) */
13 '9', '0', /* No Response */
14 '1', '1', /* American Indian/ Native American */
15 '2', '3', /* Black/ African American */
16 '3', '4', /* Chicano/ Mexican American */
17 '4', '4', /* Other Hispanic (Not Listed) */
18 'A', '4', /* Central American */
19 'B', '4', /* South American */
20 'P', '4', /* Puerto Rican */
21 'Q', '4', /* Cuban */
22 '5', '2', /* Other Asian */
23 'C', '2', /* Chinese */
24 'J', '2', /* Japanese */
25 'K', '2', /* Korean */
26 'L', '2', /* Laotian */
27 'M', '2', /* Cambodian */
28 'R', '2', /* Asian Indian */
29 'S', '2', /* Other Southeast Asian */
30 'T', '2', /* Thai */
31 'V', '2', /* Vietnamese */
32 '6', '6', /* Other Pacific Islander (new 10/8/1999) */
33 'F', '6', /* Filipino "" */
34 'G', '6', /* Guamanian "" */
35 'H', '6', /* Hawaiian "" */
36 'N', '6', /* Samoan "" */
37 '7', '5', /* White/ Caucasian */
38 decode( Z.STU_ETH_CODE,
39 'D', '0', /* Declined to State */
40 '8', '0', /* Other (Not Listed) (10/8/1999
??) */
41 '9', '0', /* No Response */
42 '1', '1', /* American Indian/ Native American */
43 '2', '3', /* Black/ African American */
44 '3', '4', /* Chicano/ Mexican American */
45 '4', '4', /* Other Hispanic (Not Listed) */
46 'A', '4', /* Central American */
47 'B', '4', /* South American */
48 'P', '4', /* Puerto Rican */
49 'Q', '4', /* Cuban */
50 '5', '2', /* Other Asian */
51 'C', '2', /* Chinese */
52 'J', '2', /* Japanese */
53 'K', '2', /* Korean */
54 'L', '2', /* Laotian */
55 'M', '2', /* Cambodian */
56 'R', '2', /* Asian Indian */
57 'S', '2', /* Other Southeast Asian */
58 'T', '2', /* Thai */
59 'V', '2', /* Vietnamese */
60 '6', '6', /* Other Pacific Islander (new
10/8/1999) */
61 'F', '6', /* Filipino "" */
62 'G', '6', /* Guamanian "" */
63 'H', '6', /* Hawaiian "" */
64 'N', '6', /* Samoan "" */
65 '7', '5', /* White/ Caucasian */
66 '*** no/bad data ***'
67 )
68 ),
69 '1', '1',
70 '2', '2',
71 '3', '3',
72 '4', '4',
73 '5', '5',
74 '6', '6',
75 '7', '7',
76 '0',
77 decode( t.student_ssn,
78 '[several deleted]', '5',
...
101 'x'
102 ),
103 '*'
104 )
105 FROM
106 TRIO_STUDENT_MASTERF00S01part5 T2,
107 SIS_CSUS_ALL_spring2001_eos1 X,
108 SIS_CSUS_ALL_fall_2000_eos1 Z
109 WHERE
110 T.STUDENT_SSN = T2.STUDENT_SSN
111 AND
112 T2.STUDENT_SSN = X.STU_ID (+)
113 AND
114 T2.STUDENT_SSN = Z.STU_ID (+)
115 )
116 WHERE
117 T.T08_STUDENT_RACE_ETHNICITY = '-'
118 AND
119 T.STUDENT_SSN IN
120 (
121 SELECT
122 T3.STUDENT_SSN
123 FROM
124 TRIO_STUDENT_MASTERF00S01part5 T3
125 WHERE
126 T3.T08_STUDENT_RACE_ETHNICITY = '-'
127* )
79 rows updated.
Commit complete.
COUNT(*) T
--------- -
4 0
6 1
9 2
28 3
29 4
124 5
3 6
1 7
---------
204
8 rows selected.
1 select
2 count(*),
3 T.T08_STUDENT_RACE_ETHNICITY
4 from
5 TRIO_STUDENT_MASTERF00S01part5 T
6 group by
7* T.T08_STUDENT_RACE_ETHNICITY
------------------------------------------------------------------------
On 7 Dec 2001, at 15:27, [EMAIL PROTECTED] wrote:
>
> I think ugly SQL is appropriate.
>
> We certainly see enough of it here anyway. :)
...
>> do we need to have a "ugliest SQL statement" contest?
>>
>> OT list?
>>
>> I have an entry ready
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Eric D. Pierce
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
