0 Replies Latest reply: May 10, 2007 2:41 PM by 3004 RSS

    SQR with Oracle Decode

    3004
      <p>We just upgrade from Oracle 9.02.0.4 to 9.02.0.8 and this simpledecode statement below is returning and very large number which isincorrect.  This only occurs on the first connection of anyuser to the database that runs this process but subsequent runs ofthe program with the same user returns the correct value.  Hasanyone seen this type of error before?</p><p> </p><p>We can rewrite this into a case statement and correct thisproblme but we have several hundred similiar statements and do notwant to change our entire system.  Any assistance would begreatly appreciated.</p><p> </p><p> </p><p> </p><div><font face="Arial" size="2">begin-procedure get_invoice<br>show 'Initial Var: ' #var_bill_to_id<br>let $aff_type = 'O'<br>let #org_id = 560<br>let #sys_id = 1<br>let #div_id = 2<br>show 'Org ID: ' #org_id<br>show 'Aff T: ' $aff_type<br>show 'Sys: ' #sys_id<br>show 'Div: ' #div_id<br>begin-select<br>decode($aff_type,'I',#sys_id,'S',#sys_id,'D',#div_id,#org_id)&var_bill_to_id<br>  show 'Select Var: ' &var_bill_to_id<br>  let #var_bill_to_id = &var_bill_to_id<br>  show 'Pound Var: ' #var_bill_to_id<br>from sys.dual<br>end-select</font></div><div> </div><div><font face="Arial" size="2">end-procedure<br></font></div><div> </div><div> </div><div><font face="Arial" size="2">Hyperion SQR Server 8.3<br>Copyright (c) 1994-2004 Hyperion Solutions Corporation.  AllRights Reserved.</font></div><div> </div><div><font face="Arial" size="2">Initial Var: 0.000000<br>Org ID: 560.000000<br>Aff T: O<br>Sys: 1.000000<br>Div: 2.000000<br>Select Var: -47530000000000000000<br>Pound Var: -47530000000000000000.000000</font></div><div> </div><div><font face="Arial" size="2">SQR: End of Run.</font></div>