Hey, I did not see Canadian there.  I see a discrimination case coming.

-----Original Message-----
[EMAIL PROTECTED]
Sent: Friday, December 07, 2001 5:20 PM
To: Multiple recipients of list ORACLE-L
2001, Number 333



Is that the best you can do?  ;)

I recall something from the list many years ago where a poster was
asking for help with a SQL statement that was unbelievably long.

If I recollect correctly, it was about a 64K single SQL statement.

It doesn't seem like maintaining something like that would
actually be possible.

Think of what the explain plan output would look like.

Jared




                    "Eric D.
                    Pierce"              To:     Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
                    <PierceED@csus       cc:
                    .edu>                Subject:     ugly sql contest entry
/ Re: ORACLE-L Digest -- Volume 2001,
                    Sent by:              Number 333
                    [EMAIL PROTECTED]
                    om


                    12/07/01 04:20
                    PM
                    Please respond
                    to ORACLE-L






  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).




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kimberly Smith
  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).

Reply via email to