SELECT clause - too much space in columns calculations

3 Posts
3 Users
0 Reactions
1,136 Views
Posts: 13
Topic starter
(@kmpel)
Member
Joined: 8 months ago
[#150]

There is too much space in the select columns, pushing some parts of the calculations to the right side. It's often too far to support easy calculation readability.

MSSQL, profile used: jr5

image
SELECT
    CtrlRecordId   =                                                                                                                        DataSource.CtrlRecordId
   ,[AccountName]  =                                                                                                                        'AMEX'
   ,TransactionURC = CONVERT(NVARCHAR(MAX), DataSource.[Date], 103) + ' | ' + CONVERT(NVARCHAR(MAX), -1 * DataSource.Amount) + ' | ' +      DataSource.Reference
   ,TransactionOperation = IIF((-1 * DataSource.Amount) >= 0 , 'CREDIT', 'DEBIT')
   ,TransactionDate   =                                                                                                                     DataSource.[Date]
   ,TransactionTime   = CONVERT(TIME, NULL)
   ,AmountOnAccount   = -1 *                                                                                                                DataSource.Amount
   ,LocalAmount       = -1 *                                                                                                                DataSource.Amount
   ,LocalCurrencyCode =                                                                                                                     'GBP'
   ,TransactionType   =                                                                                                                     DataSource.Category
   ,Description1      =                                                                                                                     DataSource.AppearsOnYourStatementAs
   ,Description2      =                                                                                                                     DataSource.[Description]
   ,Description3      =                                                                                                                     DataSource.ExtendedDetails
   ,UserComment       =                                                                                                                     NULL

FROM StageTyped.AMEXBA_0001__StatementLong DataSource
WHERE DataSource.CtrlCargoRecordValidityFlagId < 100
    AND DataSource.CtrlETLStatusFlagId         = 1

/*-----------------------------------------------------------------------------------------------------------------------------
--expected behaviour:

SELECT
    CtrlRecordId         = DataSource.CtrlRecordId
   ,[AccountName]        = 'AMEX'
   ,TransactionURC       = CONVERT(NVARCHAR(MAX), DataSource.[Date], 103) + ' | ' + CONVERT(NVARCHAR(MAX), -1 * DataSource.Amount) + ' | ' + DataSource.Reference
   ,TransactionOperation = IIF((-1 * DataSource.Amount) >= 0 , 'CREDIT', 'DEBIT')
   ,TransactionDate      = DataSource.[Date]
   ,TransactionTime      = CONVERT(TIME, NULL)
   ,AmountOnAccount      = -1 * DataSource.Amount
   ,LocalAmount          = -1 * DataSource.Amount
   ,LocalCurrencyCode    = 'GBP'
   ,TransactionType      = DataSource.Category
   ,Description1         = DataSource.AppearsOnYourStatementAs
   ,Description2         = DataSource.[Description]
   ,Description3         = DataSource.ExtendedDetails
   ,UserComment          = NULL

FROM StageTyped.AMEXBA_0001__StatementLong DataSource
WHERE DataSource.CtrlCargoRecordValidityFlagId < 100
    AND DataSource.CtrlETLStatusFlagId         = 1
-----------------------------------------------------------------------------------------------------------------------------*/

 


2 Replies
Posts: 20
(@jasonccrane)
Member
Joined: 3 years ago

Would also love a solution for this for Oracle DB


Reply
Posts: 12
(@guidomarcel)
Member
Joined: 5 years ago

Hi Jason,

thanks for the feedback. I see the issue. The parser is handling the last token like an ALIAS. 

As work-around you could switch-off the ALIAS alignment option.

grafik

 


Reply
Share: