Comments Line in SQL

building semantic layer
1

Oracle 9i
BOXI R2

How do I add automated comments line in user’s SQL. Is it done in Designer ? Once users run an query in webi or deski, there are comments lines in his/her SQL.

/UNV:ORDER:/
SELECT
GDW.D_CALL.CALL_ID
FROM
GDW.D_CALL
/UNV:Orderl:@VARIABLE(‘BOUSER’):@VARIABLE(‘DOCNAME’):/

or

/UNV:CONTRACTS:/
SELECT
SA.TABLE.OBJID
FROM
SA.TABLE
/CONTRACTS:@VARIABLE(‘BOUSER’):@VARIBALE(‘DOCNAME’):/

Thank you very much.

Bonita (BOB member since 2008-03-04)

2

You can use the END_SQL parameter to add a comment at the end of any SQL generated from the universe. I’m not sure if we can add a comment to the beginning in any sort of automatic fashion. For example, here’s what I have in one of my universes for the END_SQL parameter:

/* User = @variable('BOUSER') Report = @variable('DOCNAME') */

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Thank, Dave.

Your Blog is great. Thanks for all information you shared on BoB and your blog. :nopity:

Bonita (BOB member since 2008-03-04)

4

I had never heard of the END_SQL parameter before. You can get something similar by creating a measure object with the following SQL:

/*UNV:Order:@VARIABLE('BOUSER'):@VARIABLE('DOCNAME'):*/ 0

and put that object first in any report. Then you would get:

SELECT
  /*UNV:Order:myusername:This Report:*/ 0,
  GDW.D_CALL.CALL_ID
FROM
  GDW.D_CALL

Which is close to what you want.

Dennis W. Disney :us: (BOB member since 2003-09-17)

5

Hi, Dennis, welcome back. :slight_smile: The challenge with the solution you provided is it relies on the user to take some action. The END_SQL parameter was added so that folks could include special database instructions like “for uncommitted read” and so on, but it works perfectly for adding comments to the SQL as well. By setting it as a universe parameter, it’s going to be automatic on every query, including adhoc.

Dave Rathbun :us: (BOB member since 2002-06-06)

6

I was doing some real complex Oracle Financials development at a client for a long time and I fell out of the habit of posting. My account was locked when I tried to sign in.

Depending on the users to do something is folly. However, the SQL monitoring tool may always want the information in the same place in the SQL and if it is at the end, it will never be in the same place. Reports developed by IT could always have the object included. The solution depends on the requirements.

It’s a very cool feature that I hadn’t heard of before. I wish I had because it would have simplified a lot of support issues - DBA’s calling me about SQL that is running long but they don’t know if it is from a universe I support or if it is from a published report.

Dennis W. Disney :us: (BOB member since 2003-09-17)