RE: A really complex SQL statement

Here's one:

update mfs8sv1rp
SET
SLSAMT01 = case when substr( digits(
cast(SLSAMT01 as dec(13,2)) - cast(SLSAMT02 as dec(13,2))) ,1,11) =
'00000000000' then substr( digits( cast(SLSAMT01 as dec(13,2)) -
cast(SLSAMT02 as dec(13,2))),12,1)||'.'|| substr( digits(
cast(SLSAMT01 as dec(13,2)) - cast(SLSAMT02 as dec(13,2))),13,2)
when
substr( digits( cast(SLSAMT01 as dec(13,2)) - cast(SLSAMT02 as
dec(13,2))) ,1,10) = '0000000000' then substr( digits(
cast(SLSAMT01 as dec(13,2)) - cast(SLSAMT02 as
dec(13,2))),11,2)||'.'|| substr( digits( cast(SLSAMT01 as
dec(13,2)) - cast(SLSAMT02 as dec(13,2))),13,2)
else substr(digits( cast(SLSAMT01 as dec(13,2)) - cast(SLSAMT02 as

dec(13,2))),10,3) ||'.'|| substr( digits( cast(SLSAMT01 as
dec(13,2)) - cast(SLSAMT02 as dec(13,2))),13,2) end
, SLSAMT02 = ' '
, SLSUPAQ2 = ' '
WHERE timstmp in( select timstmp from mfschkhp
where merchantno in('H0000000163', 'H0000000164', 'H0000000165',
'H0000000166', 'H0000000167')) and SLSUPAQ2 = 'B6' and
substr(SLSAMT02,1,1) <> '-'



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Earl
Sent: Tuesday, June 02, 2009 1:16 PM
To: midrange-l@xxxxxxxxxxxx
Subject: A really complex SQL statement

Hey list,

I'm writing an article on securing SQL and I'm looking for a really,
really, long and complex SQL statement. Unfortunately my own SQl skills

are kind of primitive, so I don't know how to generate this statement
with the correct syntax.

So, has anyone got a copy of one that has multiple file names, multiple
SELECT and UPDATE or DELETE statements, etc etc. The goal is not
"un-readability", but rather complexity specifically as it relates to
multiple tables and verbs in one statement.

Any and all assistance is greatly appreciated,

aTdHvAaNnKcSe,

jte