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

Format SQL throwing unmasked error for JSON_VALUEs in 9.5.3.9640

jamesdailyjamesdaily Posts: 3 New member
edited May 14, 2019 5:12PM in SQL Prompt
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


Answers

  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    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!



    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • Options
    jamesdailyjamesdaily Posts: 3 New member
    Hey @Jessica R

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

    Here's my settings as requested





  • Options
    jamesdailyjamesdaily Posts: 3 New member
    I did disable the Column Alias option just now, seems to have gotten me the workaround I need to continue forward
  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    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!)

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Sign In or Register to comment.