Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

Calculated Value - generates Sql ERROR

edited May 2003 in End User

Hello Report-Builder Freak,

I've got a Problem with calculated Fields in Version 7.02. I can't change
the SQL-Fieldalias. This fieldvalue is set by the Report-Builder, but the
aliasname generates sometimes a SQL-Error. For example the table-Column
sName is a String:

sName || ' is a nice name ' generates the SQL-Fieldalias sName_'

If I could edit the field SQL-Fieldalias I would change the Value - but I
cant.


In hope

Helmut Auffinger.

Comments

  • edited May 2003
    The Field SQL Alias isn't able to be changed. It should be auto generated
    correctly. This is an expression type string calc field? What database are
    you using? Can you recreate this with the DBDemos database? It works fine in
    tests here.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited May 2003
    Drop Table Demo_For_You;

    CREATE TABLE Demo_For_You (
    sName VARCHAR2(20) NOT NULL
    );

    INSERT INTO Demo_For_You(sName) VALUES ('Jim Bennett');
    INSERT INTO Demo_For_You(sName) VALUES ('Helmut Auffinger');

    Select sName || ' is a nice name '
    FROM Demo_For_You;

    Cheers,

    Helmut Auffinger.
  • edited May 2003
    What are the steps necessary to reproduce the error?

    Here is one test I tried.

    1.Open the RBuilder\Demos\EndUser\AutoJoin example project

    2.Ran the project without modifications

    3.Create an Employee query dataview using the Query Wizard

    4.Selected the Calc tab to launch the Query Designer

    5.Create a calc field on FirstName field

    6.Select Expression from the drop down of the Function

    7.Edit the Expression edit box and enter Employee.FirstName + ' ' +
    Employee.LastName

    8.Close query designer and execute query

    It worked fine.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited May 2003
    >
    Yes but what happens, if the last concatination is only a string constant?
    So please do not concatenat to database fields
    Please try Employee.FirstName || ' hallo World'
  • edited May 2003
    I did receive an error. However, we submit this directly to the server, so
    it is up to the database to determine if it is valid SQL. I did try using
    Employee.FirstName + 'hello world' and that does give an error. Then I tried
    Employee.FirstName + "hello world" and that worked. Use double quotes when
    you want to end the expression type calc with a string conatenation.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited May 2003
    > I did receive an error. However, we submit this directly to the server, so
    tried
    Okay we forgot to tell Oracle 8i and BDE 5.11 with vendorinit sqlora32.
    But first concatonation between strings does not work with the plus signe
    and Strings have to be included into single quotes!
    Interesting is, if you include a <|| NULL> (concationation with a null
    string" then your translation into the sql syntax works.

    Regards

    Nicolas
This discussion has been closed.