Re: Making your sql statement look nice

Hi John,

Since you shouldn't have to be going into these system generated queries too often why not use your editor.

For example, if you are on a UNIX box just use vi and:

        :%s/,/,^V^M/g (^V^M is Control-V followed by Control-M)

This will change every occurrence of coma to coma carriage return.

Regards

Jerry

Kees Roelen wrote:
>
> Hello,
>
> We have an application which creates it's own dynamical sql statements.
> These statements become very large (up to and over 32K) and all the
> complete sql
> statement is logged into a table as a LONG variable. This is done,
> because
> we want to be able to see what statement did / didn't execute and how
> long
> the execution took.
>
> The problem at hand is that the created sql statements are not really
> easily readable, because the complete sql is concattenated in one
> string.
>
> I wonder if there is any one out there who is having the same problems
> and
> made a procedure with a LONG or VARCHAR as input and returns a LONG with
> the sql statement with an improved readability (working with new_lines
> (CHR(10)).
>
> Example:
> THIS IS WHAT THE BAD VERSION LOOKS LIKE
> =======================================
> CREATE table S_OR_DEDUP_14266 UNRECOVERABLE AS SELECT I14264, I14264 AS
> DEDUP_COLUMN FROM (SELECT o0.object_id AS I14264 FROM T_object o0,
> T_relation r1, T_object o1, T_relation r2, T_object o2,T_relation
> r3,T_object o3,T_relation r4,T_object o4,T_relation r5,T_object o5 WHERE
> o0.actuality='A' AND o0.object_id=r1.from_object_id AND
> r1.relation_class_id=11010 AND r1.to_object_id=o1.object_id AND
> o1.actuality='A' AND r1.actuality='A' AND o1.object_id=r2.to_object_id
> AND r2.relation_class_id=12000 AND r2.from_object_id=o2.object_id AND
> o2.object_class_id in (120009) AND o2.actuality='A' AND r2.actuality='A'
> AND o2.object_id=r3.from_object_id AND r3.relation_class_id=12003 AND
> r3.to_object_id=o3.object_id AND o3.actuality='A' AND r3.actuality='A'
> AND o3.object_id=r4.to_object_id AND r4.relation_class_id=12002 AND
> r4.from_object_id=o4.object_id AND o4.object_class_id in (120003) AND
> o4.actuality='A' AND r4.actuality='A' AND o4.object_id=r5.to_object_id
> AND r5.relation_class_id=138546 AND r5.from_object_id=o5.object_id AND
> r5.actuality='A' AND o5.value = 'F' AND o5.object_class_id in (138546));
>
> THIS IS THE EASILY READABLE VERSION
> ====================================
> CREATE table S_OR_DEDUP_14266 UNRECOVERABLE AS
> SELECT I14264,
> I14264 AS DEDUP_COLUMN
> FROM (SELECT o0.object_id AS I14264
> FROM T_object o0,
> T_relation r1,
> T_object o1,
> T_relation r2,
> T_object o2,
> T_relation r3,
> T_object o3,
> T_relation r4,
> T_object o4,
> T_relation r5,
> T_object o5
> WHERE o0.actuality='A'
> AND o0.object_id=r1.from_object_id
> AND r1.relation_class_id=11010
> AND r1.to_object_id=o1.object_id
> AND o1.actuality='A'
> AND r1.actuality='A'
> AND o1.object_id=r2.to_object_id
> AND r2.relation_class_id=12000
> AND r2.from_object_id=o2.object_id
> AND o2.object_class_id in (120009)
> AND o2.actuality='A'
> AND r2.actuality='A'
> AND o2.object_id=r3.from_object_id
> AND r3.relation_class_id=12003
> AND r3.to_object_id=o3.object_id
> AND o3.actuality='A'
> AND r3.actuality='A'
> AND o3.object_id=r4.to_object_id
> AND r4.relation_class_id=12002
> AND r4.from_object_id=o4.object_id
> AND o4.object_class_id in (120003)
> AND o4.actuality='A'
> AND r4.actuality='A'
> AND o4.object_id=r5.to_object_id
> AND r5.relation_class_id=138546
> AND r5.from_object_id=o5.object_id
> AND r5.actuality='A'
> AND o5.value = 'F'
> AND o5.object_class_id in (138546)
> );
>
> Any help would be appreciated
>
> Greetings John

--

Jerry Gitomer         	ICT Group
jgitomer_at_ictgroup.com	Langhorne PA
jgitomer_at_yahoo.com 	Opinions are mine not those of ICT Group