need program make the query easier to read
From: <nova1427_at_gmail.com>
Date: Wed, 9 Apr 2008 00:03:39 -0700 (PDT)
Message-ID: <9a6532d4-9246-40b4-86eb-8f391c9e0bff@y21g2000hsf.googlegroups.com>
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
) 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
) 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 - 02:03:39 CDT
Date: Wed, 9 Apr 2008 00:03:39 -0700 (PDT)
Message-ID: <9a6532d4-9246-40b4-86eb-8f391c9e0bff@y21g2000hsf.googlegroups.com>
Dear all,
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_TWHERE 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 - 02:03:39 CDT