need program make the query easier to read Options

From: <nova1427_at_gmail.com>
Date: Wed, 9 Apr 2008 00:12:59 -0700 (PDT)
Message-ID: <60f8700e-f553-4acd-9c86-36ad7eecc28e_at_c65g2000hsa.googlegroups.com>

[Quoted] Dear all,

[Quoted] [Quoted] I want a program or tool that make the query easier to read (arrange)

e.g
this query needs redesign to understand it.

SELECT T2.NAME, T2.SEGMENT, T1.FLAGE,T1.RATING1,
T1.ARCH_DATE,               T1.RATING2,T2.CASH,
T2.NON_CASH_OS  FROM(SELECT T1.CIS,

CASE WHEN T1.RATING1 <= '7b' AND T2.RATING2 > '7b' AND T1.RATING1<>'10' AND T2.RATING2<>'10' THEN 1 WHEN T1.RATING1 > '7b' AND T2.RATING2 <= '7b' AND T1.RATING1<>'10' AND T2.RATING2<>'10' THEN 2 WHEN T2.RATING2 = '10' THEN 3
END FLAGE, T1.RATING1,T1.ARCH_DATE, T2.RATING2
FROM

(                                        SELECT

CIS, INT_RAT RATING1, ARCH_DATE   FROM ACLMARCH.RATINGS_T WHERE EOM_FLAG='Y' AND ARCH_DATE>
TRUNC(SYSDATE, 'YEAR')
) T1,( SELECT CIS, INT_RAT

RATING2                              FROM ACLMARCH.RATINGS_T

WHERE EOM_FLAG = 'N'
) T2WHERE T1.CIS=T2.CIS

AND T1.RATING1 <> T2.RATING2 AND T1.RATING1 NOT IN ('RETAIL', '0') AND T2.RATING2 NOT IN ('RETAIL', '0') ) T1,ACLMARCH.CUST_SUMMARY_T T2

WHERE                 T1.CIS=T2.CIS AND T1.FLAGE IS NOT NULL
AND        T2.EOM_FLAG=    'N'


-----------------------

the below after arrange

SELECT

                T2.NAME,
                T2.SEGMENT,
                T1.FLAGE,
                T1.RATING1,
                T1.ARCH_DATE,
                T1.RATING2,
                T2.CASH,
                T2.NON_CASH_OS
FROM(
        SELECT
                T1.CIS,
                CASE
                        WHEN T1.RATING1 <= '7b' AND T2.RATING2 > '7b'

AND T1.RATING1<>'10'
AND T2.RATING2<>'10' THEN 1

                        WHEN T1.RATING1 > '7b' AND T2.RATING2 <= '7b'

AND T1.RATING1<>'10'
AND T2.RATING2<>'10' THEN 2

                        WHEN T2.RATING2 = '10' THEN 3
                END FLAGE,
                T1.RATING1,
                T1.ARCH_DATE,
                T2.RATING2
        FROM
        (
                SELECT  CIS, INT_RAT RATING1, ARCH_DATE
                FROM ACLMARCH.RATINGS_T
                WHERE EOM_FLAG='Y'
                AND ARCH_DATE>TRUNC(SYSDATE, 'YEAR')
        ) T1,
        (
                SELECT CIS, INT_RAT RATING2
                FROM ACLMARCH.RATINGS_T
                WHERE EOM_FLAG='N'
        ) T2
        WHERE T1.CIS=T2.CIS
        AND T1.RATING1 <> T2.RATING2
        AND T1.RATING1 NOT IN ('RETAIL', '0')
        AND T2.RATING2 NOT IN ('RETAIL', '0')

) T1,

ACLMARCH.CUST_SUMMARY_T T2
WHERE T1.CIS=T2.CIS
AND T1.FLAGE IS NOT NULL
AND T2.EOM_FLAG='N' Received on Wed Apr 09 2008 - 09:12:59 CEST