Toad World® Forums

Formatting Case Statement


#1

This
SELECT CASE WHEN SYSDATE BETWEEN ivas.start_datetime AND NVL(ivas.stop_datetime, SYSDATE) THEN ‘Y’ ELSE ‘N’ END
FROM as_inventory_asset_states ivas

Should format something like this:

SELECT CASE
WHEN SYSDATE BETWEEN ivas.start_datetime AND NVL(ivas.stop_datetime, SYSDATE) THEN ‘Y’
ELSE ‘N’
END
FROM inv_asset_states ivas


#2

Hi Jet,

Sorry for getting back to you late.

You can select an option in Oracle Formatter Options -> General -> Indenting: Position THEN on the same line, not on the next line. When this option is selected, the THEN would normally stay on the same line like what you proposed. But the script you provided is somehow not working as you expected. Because the line WHEN… THEN ‘Y’ is too long to the right margin 78 or 80 characters. The formatter wraps this line and it decides to put the ‘Y’ and ‘N’ on the next line to THEN and ELSE.

Here’s what i got:
–original
SELECT CASE
WHEN SYSDATE BETWEEN ivas.start_datetime
AND NVL (ivas.stop_datetime, SYSDATE) THEN
‘Y’
ELSE
‘N’
END
FROM as_inventory_asset_states ivas;
–make the line shorter to fit one line then it looks good
SELECT CASE
WHEN TRUE AND NVL (ivas.stop_datetime, SYSDATE) THEN ‘Y’
ELSE ‘N’
END
FROM as_inventory_asset_states ivas;

Let me know if this works for you or not.

Thanks,
Vincent


#3

Ok, I’m not following something. It it’s long, it puts it on one line and if it’s short it formats it properly.

Sounds like a bug to me! :wink:

If should format like this:

SELECT CASE
WHEN SYSDATE BETWEEN ivas.start_datetime AND
NVL(ivas.stop_datetime, SYSDATE) THEN
‘Y’
ELSE
‘N’
END
FROM as_inventory_asset_states ivas;

Jet

Message was edited by: joethejet


#4

Hi Jet,

Did you mean you got the result like the format of the first script that you posted? i.e.,
SELECT CASE WHEN SYSDATE BETWEEN ivas.start_datetime AND NVL(ivas.stop_datetime, SYSDATE) THEN ‘Y’ ELSE ‘N’ END
FROM as_inventory_asset_states ivas

If it’s like this, could you please provide the whole script after you format it including the header? (the header is something like /* Formatted on 20-Feb-2010 15:15:53 (QP5 v5.144) */)

Thanks,
Vincent


#5

the whole script:

/* Formatted on 2/22/2010 3:44:44 PM (QP5 v5.141) */
SELECT CASE WHEN SYSDATE BETWEEN ivas.start_datetime AND NVL(ivas.stop_datetime, SYSDATE) THEN ‘Y’ ELSE ‘N’ END
FROM as_inventory_asset_states ivas


#6

Hi Jet,

Could you also give me the formatter options file? Which is C:\Documents and Settings\user\Application Data\Quest Software\SQL Navigator 6.3.0 Beta\FmtOptions.opt. I tried your script with formatter v5.141 but haven’t been able to repeat the problem yet. The options file may help.

Thanks,
Vincent


#7

attached
FmtOptions.opt (1.92 KB)


#8

Hi Jet,

According to the options file you attached, you set the Right Margin up to 180. This value control how many characters one line can hold. It should normally be 78 or 80. If you change it back, the script will be formatted correctly.

Thanks,
Vincent


#9

Well, that’s nonsensical.

I want to line break after each then and, I’ve asked it to stack my AND.

If this is what it’s “supposed” to do, then I think you need to give an option to format it like I asked. It shouldn’t be dependent on the length of the line.

jet


#10

Hi Jet. The stacked option you selected doesn’t work for the BETWEEN…AND. We currently don’t have any option to wrap/stack CASE…WHEN and BETWEEN…AND yet. I have created an enhancement CR for this.

Thanks,
Vincent


#11

OK, I can see that, but I want to break up my then and else. If I type in an IF statement, it formats like this:

BEGIN
IF NULL = NULL THEN
DBMS_OUTPUT.put_line(‘nul’);
ELSE
DBMS_OUTPUT.put_line(‘junk’);
END IF;
END;

And, if I do a case statement in pl/sql it does this:

BEGIN
CASE
WHEN NULL = NULL THEN
DBMS_OUTPUT.put_line(‘nul’);
ELSE
DBMS_OUTPUT.put_line(‘junk’);
END CASE;
END;

Shouldn’t it do the same thing in a sql statement?

jet


#12

Yes, I agree with you. I will add this piont to the CR.