I'm trying to configure the formatter so that short IIF functions stay on a single line in SELECT clauses when they're under the max character limit (e.g., 120 chars).
SELECT
JournalId = jr.JournalId
,Flag1 = IIF(jr.ExpenseTransactionId IS NOT NULL
,'Y', 'N')
,Flag1 = IIF(jr.id2 IS NOT NULL
,'Y', 'N')
,Flag3 = IIF(jr.id3 IS NOT NULL
,'Y', 'N')
,FinCDType = IIF(jr.JournalAmountGBP >= 0
,'CREDIT', 'DEBIT')
FROM Treasure.Journal jr
/*------------------------------------------------------------
--expected behaviour
SELECT
JournalId = jr.JournalId
,Flag1 = IIF(jr.ExpenseTransactionId IS NOT NULL,'Y', 'N')
,Flag1 = IIF(jr.id2 IS NOT NULL,'Y', 'N')
,Flag3 = IIF(jr.id3 IS NOT NULL,'Y', 'N')
,FinCDType = IIF(jr.JournalAmountGBP >= 0,'CREDIT', 'DEBIT')
FROM Treasure.Journal jr
*/------------------------------------------------------------
I found the solution by myself for iif: In MyLists: add iif into and set line to 10, next lines: 1
However, there is stillan option that some built-in function has the similar multi-line behaviour without. I'd recommend creating a dedicated "built-in function" setup.
Sorry, I have one more if, isnull or built-in function strange behaviour. msql, profile:jr6
SELECT
LocalAmount = @AmountOnAccount
,LocalCurrencyCode =
(
SELECT a.CurrencyCode FROM Treasure.Account a WHERE a.AccountId = @AccountId
)
,DescriptionMain = IIF(ISNULL(@Description1, '') != '' ,@Description1 , '') + IIF(ISNULL(@Description2, '') != '' ,' | ' + @Description2 , '') + IIF(ISNULL ( @Description3 ,'') != '' , ' | ' +
@Description3 ,'')
/*------------------------------------------------------
-- expected behaviour
-- one of the option when the code is not too long for one line
SELECT SELECT
LocalAmount = @AmountOnAccount
,LocalCurrencyCode =
(
SELECT a.CurrencyCode FROM Treasure.Account a WHERE a.AccountId = @AccountId
)
,DescriptionMain = IIF(ISNULL(@Description1, '') != '', @Description1 , '') + IIF(ISNULL(@Description2, '') != '' ,' | ' + @Description2 , '') + IIF(ISNULL ( @Description3 ,'') != '' , ' | ' + @Description3 ,'')
-- I can force the structure manually, but the equal signs are not aligned. It works with the profile jr6:
SELECT
LocalAmount = @AmountOnAccount
,LocalCurrencyCode =
(
SELECT a.CurrencyCode FROM Treasure.Account a WHERE a.AccountId = @AccountId
)
,DescriptionMain = IIF(ISNULL(@Description1, '') != '', @Description1 , '') --
+ IIF(ISNULL(@Description2, '') != '' ,' | ' + @Description2 , '') --
+ IIF(ISNULL ( @Description3 ,'') != '' , ' | ' + @Description3 ,'') --
--IDEAL SOLUTION, including subselect in round brackets. All calculations are to the right from the aligned equal signs :)
-- if necessary some forced manual formating in the maths or concatenations is OK to deal with any type of operators and not to overcomplicate the formatting code
SELECT
LocalAmount = @AmountOnAccount
,LocalCurrencyCode =
(
SELECT a.CurrencyCode FROM Treasure.Account a WHERE a.AccountId = @AccountId
)
,DescriptionMain = IIF(ISNULL(@Description1, '') != '', @Description1, '')
+ IIF(ISNULL(@Description2, '') != '',' | ' + @Description2 , '')
+ IIF(ISNULL (@Description3 ,'') != '', ' | ' + @Description3 ,'')
------------------------------------------------------*/
Thank you for your reply. Is there any chance of implementing the ideal solution as per the sample below?
--IDEAL SOLUTION, including subselect in round brackets. All calculations are to the right from the aligned equal signs :)
-- if necessary some forced manual formating in the maths or concatenations is OK to deal with any type of operators and not to overcomplicate the formatting code
SELECT
LocalAmount = @AmountOnAccount
,LocalCurrencyCode =
(
SELECT a.CurrencyCode FROM Treasure.Account a WHERE a.AccountId = @AccountId
)
,DescriptionMain = IIF(ISNULL(@Description1, '') != '', @Description1, '')
+ IIF(ISNULL(@Description2, '') != '',' | ' + @Description2 , '')
+ IIF(ISNULL (@Description3 ,'') != '', ' | ' + @Description3 ,'')
