On Apr 9, 10:56=A0am, "Shakespeare" <what...@[EMAIL PROTECTED]
> wrote:
> <nova1...@[EMAIL PROTECTED]
> schreef in
berichtnews:60f8700e-f553-4acd-9c86-36ad7e=
ecc28e@[EMAIL PROTECTED]
>
>
>
>
>
> > 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, =A0 =A0 =A0 =A0 =A0 =A0 =A0 T1.RATING2,T2.CASH,
> > T2.NON_CASH_OS =A0FROM(SELECT T1.CIS,
> > CASE WHEN T1.RATING1 <=3D '7b' AND T2.RATING2 > '7b' AND
> > T1.RATING1<>'10' AND T2.RATING2<>'10' THEN 1
> > WHEN T1.RATING1 > =A0 =A0 =A0 '7b' AND T2.RATING2 =A0 =A0<=3D '7b' AND
> > T1.RATING1<>'10' AND T2.RATING2<>'10' THEN 2 WHEN T2.RATING2 =3D '10'
> > THEN 3
> > END FLAGE, =A0 =A0 =A0 =A0 =A0 T1.RATING1,T1.ARCH_DATE,
> > T2.RATING2
> > FROM
> > ( =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 =A0 =A0SELECT
> > CIS, INT_RAT RATING1, ARCH_DATE
>
> > FROM =A0 =A0ACLMARCH.RATINGS_T =A0 =A0 =A0WHERE EOM_FLAG=3D'Y'
> > AND ARCH_DATE>
> > TRUNC(SYSDATE, 'YEAR')
> > ) T1,( =A0SELECT =A0 =A0 =A0 =A0CIS, INT_RAT
> > RATING2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=A0FROM =
ACLMARCH.RATINGS_T
> > WHERE EOM_FLAG =A0 =3D 'N'
> > ) T2WHERE T1.CIS=3DT2.CIS
> > AND T1.RATING1 <> T2.RATING2 AND T1.RATING1 NOT IN ('RETAIL', '0')
> > AND T2.RATING2 NOT IN ('RETAIL', '0') =A0 =A0 =A0 =A0 =A0 =A0)
> > T1,ACLMARCH.CUST_SUMMARY_T T2
> > WHERE =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 T1.CIS=3DT2.CIS AND T1.FLAGE IS
NO=
T NULL
> > AND =A0 =A0 =A0 =A0T2.EOM_FLAG=3D =A0 =A0'N'
>
> > -----------------------
>
> > the below after arrange
>
> > SELECT
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T2.NAME,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T2.SEGMENT,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T1.FLAGE,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T1.RATING1,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T1.ARCH_DATE,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T1.RATING2,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T2.CASH,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T2.NON_CASH_OS
> > FROM(
> > =A0 =A0 =A0 =A0SELECT
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T1.CIS,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0CASE
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHEN T1.RATING1 <=3D
'7b'=
AND T2.RATING2 > '7b'
> > AND T1.RATING1<>'10'
> > AND T2.RATING2<>'10' THEN 1
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHEN T1.RATING1 > '7b'
AN=
D T2.RATING2 <=3D '7b'
> > AND T1.RATING1<>'10'
> > AND T2.RATING2<>'10' THEN 2
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHEN T2.RATING2 =3D
'10' =
THEN 3
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0END FLAGE,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T1.RATING1,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T1.ARCH_DATE,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0T2.RATING2
> > =A0 =A0 =A0 =A0FROM
> > =A0 =A0 =A0 =A0(
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0SELECT =A0CIS, INT_RAT RATING1,
ARCH_DATE=
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM ACLMARCH.RATINGS_T
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE EOM_FLAG=3D'Y'
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND ARCH_DATE>TRUNC(SYSDATE, 'YEAR')
> > =A0 =A0 =A0 =A0) T1,
> > =A0 =A0 =A0 =A0(
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0SELECT CIS, INT_RAT RATING2
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM ACLMARCH.RATINGS_T
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE EOM_FLAG=3D'N'
> > =A0 =A0 =A0 =A0) T2
> > =A0 =A0 =A0 =A0WHERE T1.CIS=3DT2.CIS
> > =A0 =A0 =A0 =A0AND T1.RATING1 <> T2.RATING2
> > =A0 =A0 =A0 =A0AND T1.RATING1 NOT IN ('RETAIL', '0')
> > =A0 =A0 =A0 =A0AND T2.RATING2 NOT IN ('RETAIL', '0')
> > ) T1,
> > ACLMARCH.CUST_SUMMARY_T T2
> > WHERE T1.CIS=3DT2.CIS
> > AND T1.FLAGE IS NOT NULL
> > AND T2.EOM_FLAG=3D'N'
>
> Use a tool like TOAD, Freetoad, plsql developer, Oracle SQLDeveloper,
etc.=
> They all come with query beautyfiers. And there are a lot more in the
> freeware/shareware domain.
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -
I already have query but I want arrange it
And I use TOAD, if this program can arrange the query automatic let me
know how.
Thanks for your replay.
|
