sql greatest,decode,instr. minimize code lines sql

[zeny xu]

I did not write this code and please excuse me for my beginner questions?


? Is there a way to make this code cleaner. Code it where its less lines of
code?


? Can we make the 2 conditions separate rather than a one after the other?
For ex. Not like this WHEN INSTR(CASESTAB_1.CASENOTXT,'-ll?) > 0 THEN

decode(CASESTAB_1.RECDT,NULL,TO_DATE('01/01/1900','mm/dd/yyyy'),CASESTAB_1.RECD
T)

? why is it in a pair like this:
WHEN INSTR(CASESTAB_1.CASENOTXT,'-ll?) > 0 THEN

decode(CASESTAB_1.RECDT,NULL,TO_DATE('01/01/1900','mm/dd/yyyy'),CASESTAB_1.RECD
T)
if the string to find in
CASESTAB_1.CASENOTXT is independent to comparing date?
Can the string search and date be separate?

? Is the a shorter way of comparing the 4 dates.? Alternatives
----------------------------------------------------------------
Greatest(
CASE
WHEN INSTR(CASESTAB_1.CASENOTXT,'-ll?) > 0 THEN

decode(CASESTAB_1.RECDT,NULL,TO_DATE('01/01/1900','mm/dd/yyyy'),CASESTAB_1.RECD
T)
WHEN INSTR(CASESTAB_1.CASENOTXT,'-ss?) > 0 THEN
decode(ERTAB.FINERDT,NULL,TO_DATE('01/01/1900','mm/dd/yyyy'),ERTAB.FINERDT)
WHEN INSTR(CASESTAB_1.CASENOTXT,'-dd ') > 0 THEN

decode(ERTAB.PUBLICATIONDT,NULL,TO_DATE('01/01/1900','mm/dd/yyyy'),ERTAB.PUBLIC
ATIONDT)
WHEN INSTR(CASESTAB_1.CASENOTXT,'-ff?) > 0 THEN

decode(ERTAB.PUBLICATIONDT,NULL,TO_DATE('01/01/1900','mm/dd/yyyy'),ERTAB.PUBLIC
ATIONDT)
ELSE
decode(CASESTAB.CPDT,NULL,TO_DATE('01/01/1900','mm/dd/yyyy'),CASESTAB.CPDT)
END
,decode(CASESTAB.CPDT,NULL,TO_DATE('01/01/1900','mm/dd/yyyy'),CASESTAB.CPDT))

sql finds the suffixes from CASESTAB_1.CASENOTXT
AND
compares dates 4
? CASESTAB_1.RECDT
? ERTAB.FINERDT
? ERTAB.PUBLICATIONDT
? CASESTAB.CPDT
AND GETS THE LATER OF THE 4 DATES.


1. \find the suffix from caseN0 field/col
-ll
--ss
-dd
-ff

CASESTAB_1.CASENOTXT
2. dates to find the latest date.
? CASESTAB_1.RECDT
? ERTAB.FINERDT
? ERTAB.PUBLICATIONDT
? CASESTAB.CPDT

Using greatest function.
3. the tables are

1. ERTAB
2. CASESTAB
3. CASESTAB
:o

[paross1]

Well, I don't know how much simpler it could get and still do what it is
designed to do. For example, the statement

WHEN INSTR(CASESTAB_1.CASENOTXT,'-ll?) > 0 THEN

decode(CASESTAB_1.RECDT,NULL,TO_DATE('01/01/1900','mm/dd/yyyy'),CASESTAB_1.RECDT
)

basically means that if the CASENOTXT column contains '-ll?, then look at the
RECDT column and if it is NULL, use the date value of '01/01/1900', otherwise
use the date value in the RECDT column.....

and so on through the other WHEN clauses in your CASE statement. Is this what
you are expecting thsi query to do?

Phil

[zeny xu]

thanks,

yup that is what it is suppose to do find thes suffixes from these columns

but the date coulms are independant . meaning the date are just to be
compared and find the greates .
it does not have to be tied with finding the strings (4 suffixes) .
so i am wondering why it is
like
if you find the suffix then find the date not null .



i guess i really want to just separte them.


find the suffixes

find the dates and compare and find the greates and create a column that have
all the later dates
?

hope i am expressing it right.

[paross1]

Without seeing the entire query, it is hard to speculate why it was written the way that it was.

Phil