Toad World® Forums

Additional formatting options

Would it be possible to get additional code formatter options added to handle Cognos Passthrough SQL?

When using passthrough SQL in Cognos you can specify prompt values with the use of #prompt or #promptmany.

However Toad doesn’t like this and when you try to format code that has this type of logic in there it sees it as bad syntax and wil not then format the code.

A couple of examples are below:

#promptmany (‘CostCenter_Param’,‘string’, ’ ', ‘and ACCOUNT_BALANCE_SUMMARIES.BUSINESS_UNIT IN (’ , ‘’ , ‘)’ )#

GL_#prompt(‘Category_Code_Param’,‘token’,‘CATEGORY_CODE_13’)#MASTER GL#prompt(‘Category_Code_Param’,‘token’,‘CATEGORY_CODE_13’)#_MASTER

These prompts can be placed anywhere in the SQL code to provide user generated values.

I appreciate any feedback provided.

It looks as if the Cognos syntax is [tag:prompt]…# which parser/formatter could treat the same way as // style comments. Am I right? Is this complete?

Thanks,

Andre

Hi Andre,

You are right that the # is used to start and end each user generated prompt value.

The only problem there might be treating the more complicated 2nd example I provided where in it is being used to determine the name of the table it is going to select from. So that 2nd example will generate a table name GL_(whatever the user selected)_MASTER. The 2nd prompt in that statement is just to generate an alias for the table of the same name.

I guess I would be happy with it being treated in one of 2 ways.

Either ignore the entire line if it contains # or like you say treat the encapsulated # the same as a /* … */ style comment.

If it were to treat it like a comment then I’m not sure how it would handle the GL_ at the beginning and the _MASTER at then end?

So if we would interpret GL_#prompt(‘Category_Code_Param’,‘token’,‘CATEGORY_CODE_13’)#_MASTER as a single identifier then that would work?

BTW do you have some link to the Cognos syntax?

Hi Andre,

I think that would work yes.

You would think that Cognos would provide some kind of documentation on this however I think they see using pass through SQL in their reporting solutions as being a dirty method to building reports. So its hard to find any kind of definitive documentation for it.

That being said there are some websites that provide help such as Cognoise (see below). This is a forum based website much like toadworld. The link I have provided is asking the question about the structure of the #prompt# macro function.

www.cognoise.com/index.php

Actually found some proper documentation!

pic.dhe.ibm.com/…/ug_cr_rptstd_id51579macro_functions.html

Hi Andre,

Did you get any further with adding this functionality.

Thanks,

George …

Hi George,

We’re currently investigating if, or how we can implement this, although I must say this has some lower priority.

I’ll come back to you as soon as I have more news.

Thanks,

Andre