Cognos Impromptu query problem

[gara75]

Dear all,

Shouldn't be much of a problem but I use Impromptu 7 and I'm having trouble creating a report that can detect the presence of certain type of chars in my database fields.
I'm a user so I don't have access to the cat details nor the database access to run a sql query directly (which would be the easiest way in my opinion, anyway...)
My database, and the address field especially, is polluted with line fill chars [chr(10) in sql].
I haven't found any simple way to create a report to detect this since when I create impromptu reports, the only thing I can enter is a value but no function... (and typing ALT010 to create a L.F.as the value doesn't work)

I want Impromptu to run the equivalent of
SELECT ... BLA BLA BLA...
HAVING (((BK.[Address]) Like "%" & Chr(10) & "%"))

Is there any way I could do this, by creating one or several reports, or any way of modifying the sql query impromptu does?
Right now I'm pretty much forced to export the whole database and run my sql query separately!

Thanks in advance for your expertise

 

[drlex]

gara75,
It may depend on the exact flavour of DB that you're running Imp7 against, but I use the CHAR(...) function to distinguish non-alpha characters.
Otherwise, have you tried Report>Query>Profile tab>SQL radio button>Edit...? If you get a message along the lines of "couldn't translate into single db query", hold down shift+Ctrl+Alt while clicking on "Edit".

(Note that once you start editing SQL, you'll lose the ability to access the Data, Sort, Filter & Client/Server tabs, so experiment on a copy.)

soi là, soi carré

 

[gara75]

Thanks a lot for your answer, it appears that I don't have the edit button, probably a userclass issue but even my admin doesn't have it so they may have omitted to set the rights when deploying the catalogue. Kinda found an alt solution in the meantime with char(...) function in the report!