Re: Making your sql statement look nice

From: John Vernes <vernes.j_at_consunet.nl>
Date: Wed, 20 May 1998 18:11:33 +0200
Message-ID: <6juvf8$61p$1@news.worldonline.nl>

This is currently not an option, because we are still in the devepment phase and have to do a lot of debugging on those VERY LARGE SQL statements!!

Besides that it would still be nice to have a function that would make your SQL statements look good

Again with regards John

---

Jerry Gitomer heeft geschreven in bericht <3562E3B4.33BF_at_ictgroup.com>... 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 Received on Wed May 20 1998 - 11:11:33 CDT