Alias on new line, how do I get it on the same line in the formatter options?

[hammer15026]

I’m using 12.12.0.39 Toad for Oracle.

using the formatter it puts pseudo names on a new line.

For example.

SELECT 2
SQL_CODE,
SOLDCUST.NAME
CONTRACT_CUSTOMER_NAME,
FROMCUST.NAME
BA_ASSGN_TO_CUSTOMER_NAME,
FROMCUST.LOCATION_CODE
BA_ASSGN_SHIPTO_LOCATION,

I want this to look like this

SELECT 2 SQL_CODE,
SOLDCUST.NAME CONTRACT_CUSTOMER_NAME,
FROMCUST.NAME BA_ASSGN_TO_CUSTOMER_NAME,
FROMCUST.LOCATION_CODE BA_ASSGN_SHIPTO_LOCATION,

I can not find the settings in the formatter options to set this.

Thanks

[avergison]

Hello hammer15026,

I’m getting your desired format as soon as I set the right margin setting to 59 or higher.

Thanks,
Andre

[aybrady]

I am also having this problem can we please get a fix for this?

[dwmosman]

Here's a case of the problem. The margin is set to 100 (using Toad Beta 13.0.0.69)

The following script will be reformatted with alias's moved to the following line. However, if you delete the four lines with the concatenated case statement, then the alias's will work properly and will NOT be moved to the next line.

SELECT
sgb.sgbstdn_pidm person_uid
, tcc.academic_period_mod
|| CASE
WHEN sgb.sgbstdn_coll_code_1 in ('FC','GS') THEN '5'
ELSE '0'
END academic_period
, tcc.academic_period_mod
, sgb.sgbstdn_levl_code student_level
, sgb.sgbstdn_coll_code_1 college
, tcc.academic_year
, tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc
INNER JOIN saturn.sgbstdn sgb
ON sgb.sgbstdn_pidm = tcc.person_uid

Code with Case Statement

Code with Case Reformatted

Code without Case

Code without Case Reformatted

[avergison]

Formatter folds from the top of the statement downwards until everything fits inside the margin. As the CASE clause on a single line is way too long it has to be folded. But the expression is “more downward” than the aliases, so the aliases fold first.

Originally I tried to improve the visualization by aligning all aliases, but you are right in that a long CASE statement - which should be folded - should not spoil the game.

I’ll look how to fix that. (QP-2931)

Thanks,
Andre

[avergison]

For now I fixed it in QP5 version 5.330 as follows (formatted forms while varying the margin from wide to narrow):

SELECT sgb.sgbstdn_pidm person_uid,
tcc.academic_period_mod || CASE WHEN sgb.sgbstdn_coll_code_1 IN (‘FC’, ‘GS’) THEN ‘5’ ELSE ‘0’ END academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code student_level,
sgb.sgbstdn_coll_code_1 college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc INNER JOIN saturn.sgbstdn sgb ON sgb.sgbstdn_pidm = tcc.person_uid;

SELECT sgb.sgbstdn_pidm
person_uid,
tcc.academic_period_mod || CASE WHEN sgb.sgbstdn_coll_code_1 IN (‘FC’, ‘GS’) THEN ‘5’ ELSE ‘0’ END
academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code
student_level,
sgb.sgbstdn_coll_code_1
college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc INNER JOIN saturn.sgbstdn sgb ON sgb.sgbstdn_pidm = tcc.person_uid;

SELECT sgb.sgbstdn_pidm
person_uid,
tcc.academic_period_mod || CASE WHEN sgb.sgbstdn_coll_code_1 IN (‘FC’, ‘GS’) THEN ‘5’ ELSE ‘0’ END
academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code
student_level,
sgb.sgbstdn_coll_code_1
college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc
INNER JOIN saturn.sgbstdn sgb ON sgb.sgbstdn_pidm = tcc.person_uid;

SELECT sgb.sgbstdn_pidm person_uid,
tcc.academic_period_mod
|| CASE WHEN sgb.sgbstdn_coll_code_1 IN (‘FC’, ‘GS’) THEN ‘5’ ELSE ‘0’ END academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code student_level,
sgb.sgbstdn_coll_code_1 college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc
INNER JOIN saturn.sgbstdn sgb ON sgb.sgbstdn_pidm = tcc.person_uid;

SELECT sgb.sgbstdn_pidm
person_uid,
tcc.academic_period_mod
|| CASE WHEN sgb.sgbstdn_coll_code_1 IN (‘FC’, ‘GS’) THEN ‘5’ ELSE ‘0’ END
academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code
student_level,
sgb.sgbstdn_coll_code_1
college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc
INNER JOIN saturn.sgbstdn sgb ON sgb.sgbstdn_pidm = tcc.person_uid;

SELECT sgb.sgbstdn_pidm person_uid,
tcc.academic_period_mod
|| CASE
WHEN sgb.sgbstdn_coll_code_1 IN (‘FC’, ‘GS’) THEN ‘5’
ELSE ‘0’
END academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code student_level,
sgb.sgbstdn_coll_code_1 college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc
INNER JOIN saturn.sgbstdn sgb ON sgb.sgbstdn_pidm = tcc.person_uid;

SELECT sgb.sgbstdn_pidm person_uid,
tcc.academic_period_mod
|| CASE
WHEN sgb.sgbstdn_coll_code_1 IN (‘FC’, ‘GS’) THEN ‘5’
ELSE ‘0’
END academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code student_level,
sgb.sgbstdn_coll_code_1 college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc
INNER JOIN saturn.sgbstdn sgb
ON sgb.sgbstdn_pidm = tcc.person_uid;

SELECT sgb.sgbstdn_pidm person_uid,
tcc.academic_period_mod
|| CASE
WHEN sgb.sgbstdn_coll_code_1 IN (‘FC’, ‘GS’)
THEN
‘5’
ELSE
‘0’
END academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code student_level,
sgb.sgbstdn_coll_code_1 college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc
INNER JOIN saturn.sgbstdn sgb
ON sgb.sgbstdn_pidm = tcc.person_uid;

SELECT sgb.sgbstdn_pidm person_uid,
tcc.academic_period_mod
|| CASE
WHEN sgb.sgbstdn_coll_code_1 IN
(‘FC’, ‘GS’)
THEN
‘5’
ELSE
‘0’
END academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code student_level,
sgb.sgbstdn_coll_code_1 college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc
INNER JOIN saturn.sgbstdn sgb
ON sgb.sgbstdn_pidm = tcc.person_uid;

SELECT sgb.sgbstdn_pidm
person_uid,
tcc.academic_period_mod
|| CASE
WHEN sgb.sgbstdn_coll_code_1 IN (‘FC’, ‘GS’)
THEN
‘5’
ELSE
‘0’
END
academic_period,
tcc.academic_period_mod,
sgb.sgbstdn_levl_code
student_level,
sgb.sgbstdn_coll_code_1
college,
tcc.academic_year,
tcc.academic_year_desc
FROM odsmgr.z_total_credits_by_col_vw tcc
INNER JOIN saturn.sgbstdn sgb
ON sgb.sgbstdn_pidm =
tcc.person_uid;

( hmm, the IN(…) should be folded as well… )

Is this any better?

Thanks,
Andre

[avergison]

(please copy/paste using a Courier or similar font, this board fails to show it this way)

[deshin13]

Hi there,

Where can I download the latest formatter options from?

Thanks in advance.