| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> ugly sql contest entry / Re: ORACLE-L Digest -- Volume 2001, Number 333
1 UPDATE
2 TRIO_STUDENT_MASTERF00S01part5 T 3 SET 4 ( 5 T.T08_STUDENT_RACE_ETHNICITY 6 ) 7 = (
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',
77 decode( t.student_ssn, 78 '[several deleted]', '5',
...
101 'x' 102 ), 103 '*'
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 )
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, Jared.Still_at_radisys.com 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: PierceED_at_csus.edu 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: ListGuru_at_fatcity.com (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).Received on Fri Dec 07 2001 - 18:49:15 CST
 |
 |