DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: problem in report with number and varchar2

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Posts
    96

    problem in report with number and varchar2

    hello,

    I have several columns in a table with number datatype.for eg : select a||' '||b from tablename
    However, when i concatenate some of the columns with number datatype
    and use reports 6i, and when i click the properties of the field in the layout editor, the datatype is varchar2. why is this?

    if i dont concatenate, then the property in layout editor for those are number.

    what is the problem.

    Thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Concatanation is a string operation. In order to concatanate numbers Oracle must convert them to varchar2's first. the result of the concatanation is therefore a varchar2. you can wrap the result in a to_number() function to convert it back again.

    By the way, what data are you storing as numbers and concatanating? It sounds like there may be some bad design practice going on -- concatanation is not a "natural" operation to apply to numbers
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2001
    Posts
    96
    Actually, I am doing a report which is hard to expain here, the report output for the accounts department is such that i have to do all tricks.they need the result side by side on a paper,comparitive result of this week to previous week.
    select
    to_char(GROSREV,'$99999,9999,999.99'))||''||to_char(GROSREV1,'$99999,9999,999.99')
    if i do this, in the reportdesigner,the property is varchar2.

    how do i convert this to number, so that in the reportdesigner,it is number only,so that i can play with formats inside the designer.

    thanks

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    To do that, you would have to bring back the values individually, as numerics. It would probably be best to define the format as part of the select query, as you have done - you can't convert a series of number concatanated together with dollar signs and spaces back to a number.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width