CODEWITHSUNDEEP
sql-serverqbo3
Eric Patrick
Eric Patrick

Reputation: 2247

Debugging a qbo3 Statement

I have a qbo3 statement that causes an error. When I invoke the statement, I am routed to an error page that does not contain enough information to help me determine the root cause of the problem.

Does qbo3 contain any tools to assist in debugging the statement?

Upvotes: -1

Views: 21

Answers (1)

Eric Patrick
Eric Patrick

Reputation: 2247

Custom statements created in qbo3 can be debugged from Templates > Reports > Ad-Hoc Report.

You can use this tool to iteratively test and fix any statements you are designing.

In the screen shot below, the statement Foreclosure/B2BDataStore is set up for testing:

enter image description here

Note:

  • The Module is Foreclosure
  • The Query is Basic
  • The Base Statement is B2BDataStore
  • The Payload contains a query string for any parameters you wish to pass to the statement

In the Results panel, we can see errors reported by SQL server, including:

Msg 209, Level 16, Line 20
Ambiguous column name 'ProcessID'.
...
Msg 207, Level 16, Line 238
Invalid column name 'ProcessProcessTemplateID'.

To see the actual SQL being processed, check the Debug Statement checkbox. Instead of executing the statement's SQL, qbo3 will generate the SQL and display it in the Results panel.

In this particular case, the culprit is:

SELECT ...
  ProcessID,
  ...
FROM Bankruptcy
  INNER JOIN Process ON Process.ProcessID = Bankruptcy.ProcessID
WHERE Process.UpdatedDate >= @FromDate

Since both the Bankruptcy and Process table contain ProcessID column, the correct syntax would be to clarify which table's ProcessID to return:

SELECT ...
  Process.ProcessID,
  ...
FROM Bankruptcy
  INNER JOIN Process ON Process.ProcessID = Bankruptcy.ProcessID
WHERE Process.UpdatedDate >= @FromDate

The second error appears to simply be a typo:

ProcessProcessTemplateID

should be

Process.ProcessTemplateID

Upvotes: 0

Related Questions