need program make the query easier to read Options

#1

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'

#2

<nova1427 (AT) gmail (DOT) com> schreef in bericht
news:60f8700e-f553-4acd-9c86-36ad7eecc28e (AT) c65g2000hsa (DOT) googlegroups.com...

Quote:

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

#3

<nova1427 (AT) gmail (DOT) com> schreef in bericht
news:60f8700e-f553-4acd-9c86-36ad7eecc28e (AT) c65g2000hsa (DOT) googlegroups.com...

Quote:

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

#4

<nova1427 (AT) gmail (DOT) com> schreef in bericht
news:60f8700e-f553-4acd-9c86-36ad7eecc28e (AT) c65g2000hsa (DOT) googlegroups.com...

Quote:

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

#5

<nova1427 (AT) gmail (DOT) com> schreef in bericht
news:60f8700e-f553-4acd-9c86-36ad7eecc28e (AT) c65g2000hsa (DOT) googlegroups.com...

Quote:

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

#6

<nova1427 (AT) gmail (DOT) com> schreef in bericht
news:60f8700e-f553-4acd-9c86-36ad7eecc28e (AT) c65g2000hsa (DOT) googlegroups.com...

Quote:

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

#7

<nova1427 (AT) gmail (DOT) com> schreef in bericht
news:60f8700e-f553-4acd-9c86-36ad7eecc28e (AT) c65g2000hsa (DOT) googlegroups.com...

Quote:

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

#8

<nova1427 (AT) gmail (DOT) com> schreef in bericht
news:60f8700e-f553-4acd-9c86-36ad7eecc28e (AT) c65g2000hsa (DOT) googlegroups.com...

Quote:

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

#9

On Apr 9, 10:56*am, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

Quote:

nova1... (AT) gmail (DOT) com> schreef in berichtnews:60f8700e-f553-4acd-9c86-36ad7eecc28e (AT) c65g2000hsa (DOT) 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 -

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.

#10

On Apr 9, 10:56*am, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

Quote:

nova1... (AT) gmail (DOT) com> schreef in berichtnews:60f8700e-f553-4acd-9c86-36ad7eecc28e (AT) c65g2000hsa (DOT) 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 -

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.