Toad World® Forums

Formatting Ugliness


#1

Try formatting this:

SELECT 5 * TRUNC ( (TO_DATE (‘27-dec-2010’) - TO_DATE (‘01-dec-2010’) + 1) / 7) – days in whole weeks
- COUNT (*) – minus bank holidays
– base level of left over days
+ MOD (TO_DATE (‘27-dec-2010’) - TO_DATE (‘01-dec-2010’) + 1, 7) – minus number of weekend days hit
- DECODE (
– if sunday then 1
TO_CHAR (TO_DATE (‘01-dec-2010’), ‘DY’), ‘SUN’, 1, – otherwise
DECODE ( – if day of week plus left over days = 8 then 1
TO_NUMBER (TO_CHAR (TO_DATE (‘01-dec-2010’), ‘D’)) + MOD (TO_DATE (‘27-dec-2010’) - TO_DATE (‘01-dec-2010’) + 1, 7) , 8, 1,
– otherwise
DECODE ( – if day of week plus left over days is nine or more then 2
SIGN (TO_NUMBER (TO_CHAR (TO_DATE (‘01-dec-2010’), ‘D’)) + MOD (TO_DATE (‘27-dec-2010’) - TO_DATE (‘01-dec-2010’) + 1, 7) - 9) , -1, 0, 2)))
–INTO working_days
FROM llnl_hol
WHERE llnl_cal_dt BETWEEN ‘01-dec-2010’ AND ‘27-dec-2010’;

It will get pretty ugly. (I know it’s already ugly, but it gets uglier)


#2

Hi Jet,

I can see the formatted result becomes worse when I select “Wrapped” option for Plus-Minus-Mul-Div-Concat operators style. This is quite obviously an error with the formatter. Do you see any other types of ugliness so that I can report them all together?

Thanks,
Vincent
Fmt_Ugly.jpeg


#3

Hi Jet,

I can see the formatted result becomes worse when I select “Wrapped” option for Plus-Minus-Mul-Div-Concat operators style. This is quite obviously an error with the formatter. Do you see any other types of ugliness so that I can report them all together?

Thanks,
Vincent
Wrap_Math.jpeg


#4

I don’t think the wrapped option makes much of a difference because I don’t have and/ors in the query.

The biggest thing I see wrong is the huge blank spaces put in there.

If I use wrap operators it’s somewhat better when I have comments, but ugly when I don’t have comments. If I stack it does oK, but then there is a lot of weirdness with parms on the decode (and others) when you do a wrap it seems that once you pass the column width it stacks everything.

if you take the comments out it’s a little different.
SELECT 5 * TRUNC ( (TO_DATE (‘27-dec-2010’) - TO_DATE (‘01-dec-2010’) + 1) / 7) - COUNT (*) + MOD (TO_DATE (‘27-dec-2010’) - TO_DATE (‘01-dec-2010’)
+ 1, 7) - DECODE (
TO_CHAR (TO_DATE (‘01-dec-2010’), ‘DY’),
‘SUN’, 1,
DECODE (
TO_NUMBER (
TO_CHAR (
TO_DATE (‘01-dec-2010’),
‘D’)) + MOD (
TO_DATE (‘27-dec-2010’) - TO_DATE (‘01-dec-2010’) + 1,
7),
8, 1,
DECODE (
SIGN (
TO_NUMBER (
TO_CHAR (
TO_DATE (‘01-dec-2010’),
‘D’)) + MOD (
TO_DATE (‘27-dec-2010’) - TO_DATE (‘01-dec-2010’) + 1,
7) - 9),
-1, 0,
2)))
FROM llnl_hol
WHERE llnl_cal_dt BETWEEN ‘01-dec-2010’ AND ‘27-dec-2010’;

Is there a way to say what to do with nested functions? I don’t see it.

Hope this helps.

Merry Christmas to everyone,

Jet
p.s. I overwrote the “template code” you guys send with the product. Is there any way to get it back?