Making your sql statement look nice

From: Kees Roelen <k.roelen_at_directview.nl>
Date: Wed, 20 May 1998 14:22:43 +0200
Message-ID: <3562CB13.712D@directview.nl>

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 Received on Wed May 20 1998 - 07:22:43 CDT