Re: Making your sql statement look nice

PL/Formatter will format your code to a best-practice standard. It is currently in beta-testing with expected general availability late June. For more information about the product, go to http://www.revealnet.com

For more information about the beta-test program, contact Jason Kozma at jkozma_at_revealnet.com

Best wishes,

Cam White
RevealNet, Inc.

In article <3562CB13.712D_at_directview.nl>,   k.roelen_at_directview.nl 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
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed May 20 1998 - 12:27:22 CDT