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.
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?
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?
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.