Liveware Publishing Support Forum

Hi,____For some reason, only some of the field values in my report are displayed, even though the exact query in another tool shows them very clearly.____For example, in a query like this:____SELECT__ STUDENT_ID, FIRST_NAME, LAST_NAME__FROM__ STUDENTS__ORDER BY STUDENT_ID____, all of the values for each of the fields will show up in Sql Explorer or PlSql Developer. But when running the exact same query in SQL Designer, the First and Last name are not displayed. If I try a calculated field like Len( FIRST_NAME ), it shows up as 0.____What seems to be happening, is that only Numeric and Date fields are displaying. Any and all character fields, are showing up as empty.____I had a similar problem, years back, with Sql Server and Report Works. It was never solved, but this one is on a completely different machine with a different database.__________

The problem lies with problems with certain types of character fields (usually VARCHAR). For these types of issues, we recommend that you use a function built into R&R:____SQLEXP()____which takes as its arguments a valid expression / function on the database you^re using. For instance, if you have a varchar field called "first_name" in Oracle and you wanted to convert it to character, you^d issue the function:____CAST(first_name as char(30)) in your select statement. Using the function above, you could create a calculated field in R&R called cFirstName and the expression would be:____SQLEXP("CAST(first_name as CHAR(30))")____Then you should be able to drop the new field cFirstName onto the report as desired. SQLEXP() forces the SQL database to evaluate the expression in quotes on the server and return its contents.__

Thanks CST. Unfortunately, even with the CASTing, the varchars aren^t displaying anything.____