What are the challenges you face when working across database platforms? Take the survey
Options

Removal of space before FROM in sub-query undesired, how to stop it?

I have the following Sql query and Sql Prompt insists on removing the space before the FROM in the sub-query every time. How do I stop this?

SELECT TOP (50000000)
       [Id]
     , [SourceBranch]
     , [DestBranch]
     , [MachineName]
     , [CreationTime]
     , [Duration]
     , (
           SELECT COUNT(r.Id)FROM dbo.Results r WHERE r.RunId = Run.Id
       )
FROM [dbo].[Run]
WHERE 1 = 1
      AND CreationTime >= '2021-03-01'
      AND Duration IS NOT NULL
ORDER BY Run.Id;

Answers

  • Options
    NaM_CatNaM_Cat Posts: 5 New member
    I have same problem with removal of space before NOT LIKE
    select *
    from   dbo.table as ao (nolock)
    where  ao.Name != 'test'
           and isnull(ao.LockReasonId, 0) not in (7, 6, 8, 19, 15, 16, 18, 17, 3)
           and isnull(ao.ExternalId, '')not like 'likk'
           and ao.LockReason is not null;
  • Options
    Hi Donald,

         Thank you for raising this query.  I'm going to open a support ticket for you so we can track the progress.  

    Regards,
    David Harris.
    Product Support Engineer | Redgate Software

    Have you visited our 
    Help Center?
  • Options
    CathrineCathrine Posts: 32 Silver 4
    edited June 15, 2021 10:13AM
    This appears to be related to the Function Calls -> Add Spaces Around Parentheses option. I have the same issue.

    What I would like to see is:
    SELECT COUNT(*) FROM TableWith the setting unchecked, it gets formatted as:
    SELECT COUNT(*)FROM Table
    With the setting checked, it gets formatted as:
    SELECT COUNT (*) FROM Table
    Can confirm that this also happens in Azure Data Studio.
  • Options
    AZVAZV Posts: 1 New member
    Hello,

    Activation of option Function Calls -> Add Spaces Around Parentheses fixes the issues with space removal before the FROM but adds another one. The following format looks weird:

    <div>SELECT GETDATE () FROM Table1;</div><div>SELECT (CASE WHEN MyFunc () = 1 THEN 5 ELSE 0 END) FROM Table1;</div>

    Meanwhile, deactivating the option doesn't affect the space before the equals sign but removes the space before the FROM

    <div>SELECT GETDATE()FROM Table1;</div><div>SELECT (CASE WHEN MyFunc() = 1 THEN 5 ELSE 0 END) FROM Table1;</div>

    Kindly fix the bug. We need space before keywords and after function calls like the following:
    <div>SELECT GETDATE() FROM Table1;</div><div>SELECT (CASE WHEN MyFunc() = 1 THEN 5 ELSE 0 END) FROM Table1;</div>
  • Options
    Not sure why this user question was suggested to me as a reply?  Still seems like a bug to me that RedGate should fix.  
Sign In or Register to comment.