Re: need program make the query easier to read Options

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 9 Apr 2008 12:56:15 +0200
Message-ID: <47fca0d8$0$14344$e4fe514c_at_news.xs4all.nl>

<nova1427_at_gmail.com> schreef in bericht news:60f8700e-f553-4acd-9c86-36ad7eecc28e_at_c65g2000hsa.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_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'
>
>

For your info: just pasted your query iin Oracle SQL Developer and pressed ctrl-B (for beautify) and got this (hopes the formatting keeps while posting this)

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'

So it works, for free (only 76M download...)

Shakespeare Received on Wed Apr 09 2008 - 12:56:15 CEST