RE: [sqr-users] Re: DUAL table: String2Em: String to DecimalObjectConversion Error

["Peter Burton" <Peter_Burton@hyperion.com>]

All,

The problem here is that Oracle 9.2.0.3 changed they way they return
numerical values without specifying a scale.  SQR Server 8.1 was
modified to handle both the old and new mechanisms.  Oracle provided a
work-around --

alter system set event='10499 trace name context forever,level 1' 
COMMENT='return 0 for the scale if NUMBER created without specifying the
scale'
SCOPE=SPFILE

but that fix will not work in Oracle 10g -- this was by design from
Oracle.

Therefore, you need to upgrade you version of SQR Server to at least 8.1
or always return a character and then have SQR convert it a number
before you use it.

Peter

-----Original Message-----
From: sqr-users-bounces+peter_burton=hyperion.com@sqrug.org
[mailto:sqr-users-bounces+peter_burton=hyperion.com@sqrug.org] On Behalf
Of Nathan Stratton Treadway
Sent: Friday, September 29, 2006 10:02 PM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: [sqr-users] Re: DUAL table: String2Em: String to Decimal
ObjectConversion Error

On Wed, Sep 27, 2006 at 03:34:35PM -0300, April Wells wrote:
> I keep getting the above error ((SQR 6806) String2Em: String to
Decimal 
> Object Conversion Error.)
> when I select from DUAL. We've recently migrated our databases from
Oracle 
> 8 to 10G which I know causes this error, and I was able to fix the
error in 
> my main SQR programs in a normal select (by putting a TO_CHAR around 
> numeric fields with no scale), but I can't seem to resolve this error
when 
> selecting from the DUAL table.
> ie.
> 
> ....
> $seq_type = ID_SEQ.NEXTVAL
> 
> BEGIN-SELECT ON-ERROR=fatal_error
> [$seq_type]            &new_seq=NUMBER
> FROM DUAL
> END-SELECT
> 
> ....
> (SQR 6806) String2Em: String to Decimal Object Conversion Error.
> 
> Any suggestions greatly appreciated!

You don't mention exactly what different approaches you tried, or if you
really are doing something "fancy" that would require the use of dynamic
SQL as you show here... so I'll just stick to the "simple" answer: when
I have been using SQR v6.2 against Oracle 9i, I was able to use the
following code to read from a sequence:

  begin-select
  round(ID_SEQ.NEXTVAL) &new_seq
  from dual
  end-select

Note in particular that if you use ROUND() instead of TO_CHAR(), the
value returned by Oracle will be of a numeric datatype rather than
character, so you don't need to say "=NUMBER" in the SQR.  (Actually, if
you tried to assign the result of a TO_CHAR() into "&new_seq=NUMBER", I
assume you got a type-mismatch error from SQR anyway.)

If you do actually need to use dynamic SQL for some reason, you
should be able to do so by just moving the ROUND() expression into the
dynamic portion.

In any case, this error is caused by the datatype of the column or
expression that's being selected; the fact that you are selection from
DUAL instead of some other table shouldn't matter.

Hope that helps.

                                                Nathan
------------------------------------------------------------------------
----
Nathan Stratton Treadway  -  nathanst@ontko.com  -  Mid-Atlantic region
Ray Ontko & Co.  -  Software consulting services  -
http://www.ontko.com/
 GPG Key: http://www.ontko.com/~nathanst/gpg_key.txt   ID:
1023D/ECFB6239
 Key fingerprint = 6AD8 485E 20B9 5C71 231C  0C32 15F3 ADCD ECFB 6239

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users


If you have received this e-mail in error, please delete it and notify the 
sender as soon as possible. The contents of this e-mail may be confidential and 
the unauthorized use, copying, or dissemination of it and any attachments to 
it, is prohibited. Internet communications are not secure and Hyperion does 
not, therefore, accept legal responsibility for the contents of this message 
nor for any damage caused by viruses. The views expressed here do not 
necessarily represent those of Hyperion. For more information about Hyperion, 
please visit our Web site at: www.hyperion.com



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users