Re: need program make the query easier to read Options

<nova1427_at_gmail.com> schreef in bericht news:0f7da838-c07f-448b-bcd7-b05341152342_at_d45g2000hsc.googlegroups.com... On Apr 9, 10:56 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> <nova1..._at_gmail.com> schreef in
> berichtnews: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'
>
> 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 -

[Quoted] -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.

It used to be one of the basic functions of the editor, but may be, like so many options of TOAD, you'll have to pay for it. I don't have TOAD anymore, my license expired some years ago so I can not try this for you. I just checked freetoad, and the formatting tools are not part of freetoad. Oracle sqldeveloper is free and should work.

Shakespeare Received on Wed Apr 09 2008 - 12:28:14 CEST