Format SQL throwing unmasked error for JSON_VALUEs in 9.5.3.9640

jamesdaily

I have a query that looks like the code below. As of today's SQL Prompt update, if I attempt to format the code below I'm given an error stating: "Object Reference not set to an instance of an object."

The SQL is properly formed (I can actually run it just fine) and this had worked prior to today's update.

SELECT
   a.AccountId
  , JSON_VALUE(co.CompanyConfiguration
              , '$.DefaultCompanyAccountConfiguration.MonthlyFeeConfiguration.MonthlyFeeConfigurationType'
              ) DefaultConfiguration
FROM
    Daybreak.Accounts a
    JOIN Daybreak.Users u
        ON u.UserId = A.UserId
    JOIN Daybreak.Companies co
        ON co.CompanyUserId = u.CompanyUserId

Jessica R

Hi @jamesdaily! 

I wasn't able to reproduce this from a quick test, so it may be related to the formatting options that you have enabled.

Just to confirm, do you still get it if you use the "Collapsed" style (SQL Prompt>Active style)? Could you please send a screenshot of what you have enabled from Options>Format>Styles?

Thank you!

jamesdaily

Hey @Jessica R

I did try switching it to Collapsed and I still get the error 

Here's my settings as requested

jamesdaily

I did disable the Column Alias option just now, seems to have gotten me the workaround I need to continue forward

Jessica R

Thanks James! It should work though without having to disable the Column Alias option.

I haven't been able to reproduce this though, so I think it might be something specific to your tables.

I tried creating dummy tables as below, but it still didn't throw the error:

<div>CREATE SCHEMA Daybreak;</div><div>GO</div><div><br></div><div>CREATE TABLE Daybreak.Accounts (AccountId INT, UserId int)</div><div><br></div><div>GO</div><div><br></div><div>CREATE TABLE Daybreak.Users (UserId INT,CompanyUserID int)&nbsp;</div><div><br></div><div>GO</div><div><br></div><div>CREATE TABLE Daybreak.Companies (CompanyUserID INT, CompanyConfiguration VARCHAR(MAX))</div><div><br></div><div><br></div><div>SELECT</div><div>&nbsp; &nbsp;a.AccountId</div><div>&nbsp; , JSON_VALUE(co.CompanyConfiguration</div><div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , '$.DefaultCompanyAccountConfiguration.MonthlyFeeConfiguration.MonthlyFeeConfigurationType' ) DefaultConfiguration</div><div>FROM</div><div>&nbsp; &nbsp; Daybreak.Accounts a</div><div>&nbsp; &nbsp; JOIN Daybreak.Users u</div><div>&nbsp; &nbsp; &nbsp; &nbsp; ON u.UserId = A.UserId</div><div>&nbsp; &nbsp; JOIN Daybreak.Companies co</div><div>&nbsp; &nbsp; &nbsp; &nbsp; ON co.CompanyUserId = u.CompanyUserId</div>

Can I just check if formatting that code above with Column Alias re-enabled also causes the error for you?

If not, would you be able to share the create statements for those three tables?  (If you'd like them to remain private, you can send them directly to support at support@red-gate.com instead of posting here!)