Oracle Rounding issue

I want to sum a total, but the only way I can get to work is use Rounding which I do not want to use.

Not sure how to get this Oracle statement to work without Rounding.

I am doing this inside of a SQL stored procedure.
This works but user does not want rounding done.

(SELECT ROUND(TO_NUMBER(SUM(R.TRANBAL))) FROM GPCOMP1.GPRECL R WHERE ROUND(TO_NUMBER(SYSDATE-R.INVDATE)) > 90 GROUP BY ''G'') as ARTotal, 

If I do this I get error.

(SELECT (TO_NUMBER(SUM(R.TRANBAL))) FROM GPCOMP1.GPRECL R WHERE (TO_NUMBER(SYSDATE-R.INVDATE)) > 90 GROUP BY ''G'') as ARTotal, 


  Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

Open in new window

thayduckProgrammer AnalystAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
I'm guessing that ROUND and TRUNC work without a decimal precision because the openquery is able to successfully handle the integer value.  but if you provide a decimal precision it's a float which then gets corrupted to an nvarchar

try the to_char as suggested above.


you might need to use a CONVERT on the outside though
0
 
slightwv (䄆 Netminder) Commented:
Why are you using TO_NUMBER after the SUM?  The SUM will do an implicit conversion.

Try this:
SELECT SUM(nvl(to_number(R.TRANBAL),0)) ...
0
 
sdstuberCommented:
you have extra conversions and a group by that doesn't add any functionality

what does this produce?


(SELECT SUM(R.TRANBAL) FROM GPCOMP1.GPRECL R WHERE (SYSDATE-R.INVDATE) > 90)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
thayduckProgrammer AnalystAuthor Commented:
sdstuber:

Tried this and got error.

(SELECT SUM(R.TRANBAL) FROM GPCOMP1.GPRECL R WHERE (SYSDATE-R.INVDATE) > 90) as ARTotal,  

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

slightwv:

Tried this and got error.

(SELECT SUM(nvl(to_number(R.TRANBAL),0)) FROM GPCOMP1.GPRECL R WHERE (SYSDATE-R.INVDATE) > 90) as ARTotal    

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.



Data Type for TRANBAL in Oracle table is FLOAT. I do not work with Oracle much.


0
 
sdstuberCommented:
what is the data type of r.invdate?
0
 
thayduckProgrammer AnalystAuthor Commented:
Data Type of r.invdate  is DATE
0
 
thayduckProgrammer AnalystAuthor Commented:
Looks like you are on right track with date.

When I change SYSDATE-R.INVDATE to SYSDATE-SYSDATE and run program, it does not fail  but gives me 0 for my total.

So maybe problem is in subtracting a SYSDATE from a DATE type field ?
0
 
sdstuberCommented:
if tranbal is FLOAT and invdate is DATE,  then this syntax is valid:  

(SELECT SUM(R.TRANBAL) FROM GPCOMP1.GPRECL R WHERE (SYSDATE-R.INVDATE) > 90) as ARTotal,


slightwv's query would also work but has extra steps that shouldn't be necessary


what does the rest of your statement look like?
Are you doing any other operations on the ARTotal result?


0
 
sdstuberCommented:
are you sure INVDATE is a date?

maybe it's just a string that "looks" like a date.
0
 
slightwv (䄆 Netminder) Commented:
>>(SYSDATE-R.INVDATE) > 90

Not really part of the question but since you are new to Oracle I feel that I should make sure you understand Oracle Date math.

Oracle dates have a time stamp built in.  When you subtract two dates, you get the number of days but this is also factional.  You might get 90.1234 days.  So make sure when you say '> 90' a result of 90.1 should also not be counted.

You might need to truncate the time portion from the dates.
0
 
thayduckProgrammer AnalystAuthor Commented:
This statement is a subquery within a SQL stored Procedure.  Yes, INVDATE is a Data Type DATE.

The subquery is accessing a Oracle table.

I did it this way because that was the only way I could get statement to work.

If I remove ROUND, it fails. Hardly ever work with Oracle. But need this to work without ROUNDING.


(SELECT ROUND(TO_NUMBER(SUM(R.TRANBAL))) FROM GPCOMP1.GPRECL R WHERE ROUND(TO_NUMBER(SYSDATE-R.INVDATE)) > 90 GROUP BY ''G'') as ARTotal,  



Here is partial list from Oracle table showing INVDATE:

10/18/2011
12/01/2011
10/03/2011
11/01/2011
11/01/2011
12/01/2011
10/13/2011
11/01/2011
12/01/2011
0
 
sdstuberCommented:
if your data types are correct, then the syntax I posted above is correct for Oracle.

since you're getting sql server errors, perhaps something is happening there to mess with the results.

your list of values doesn't indicate they are dates, those results could have come from a sting type or a date type


if adding ROUND works some magic to make it work,  which ROUND does it?  you added 2 of them.

also, what about the question I asked earlier.  What is the rest of the query?



0
 
thayduckProgrammer AnalystAuthor Commented:
Here is entire Stored Procedure. It works fine except for ROUNDING issue.



USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptMenu]    Script Date: 01/05/2012 08:46:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [NonFreight].[procRptMenu] @ARTotal             NUMERIC (15, 2) = 0 OUT,
                                           @ARDisputed          NUMERIC (15, 2) = 0 OUT,
                                           @ARUnDisputed        NUMERIC (15, 2) = 0 OUT,
                                           @DisputeGroups       INT = 0 OUT,
                                           @DisputesIndividual  INT = 0 OUT,
                                           @EmailsSentToday     INT = 0 OUT,
                                           @EmailsSentYesterday INT = 0 OUT,
                                           @PhoneCallsToday     INT = 0 OUT,
                                           @PhoneCallsYesterday INT = 0 OUT,
                                           @PromisesYesterday   INT = 0 OUT,
                                           @DisputesYesterday   INT = 0 OUT
AS
  BEGIN
      SET NOCOUNT ON;

      DECLARE @Sql VARCHAR(max)

      --Build #MenuTotal temp table  
      CREATE TABLE [NonFreight].[#MenuTotal]
        (
           [Group]                CHAR(1),
           [ARTotal]              NUMERIC (15, 2),
           [ARDisputed]           NUMERIC (15, 2),
           [ARUnDisputed]         NUMERIC (15, 2),
           [DisputeGroups]        INT,
           [DisputesIndividual]   INT,
           [EmailsSentToday]      INT,
           [EmailsSentYesterday ] INT,
           [PhoneCallsToday]      INT,
           [PhoneCallsYesterday]  INT,
           [PromisesYesterday]    INT,
           [DisputesYesterday]    INT
        )
      ON [PRIMARY]
      
       
        
        
      --Build query statement that will access Oracle tables and gather figures. 
      --This query statement will create 1 record in #MenuTotal table with totals to be displayed on Menu Web Page.
      
      SET @Sql='SELECT ''G'', 
      (SELECT ROUND(TO_NUMBER(SUM(R.TRANBAL))) FROM GPCOMP1.GPRECL R WHERE ROUND(TO_NUMBER(SYSDATE-R.INVDATE)) > 90 GROUP BY ''G'') as ARTotal,    
      (SELECT ROUND(TO_NUMBER(SUM(R.TRANBAL))) FROM GPCOMP1.GPRECL R WHERE ROUND(TO_NUMBER(SYSDATE-R.INVDATE)) > 90 AND R.TRANTYPE = ''B'' GROUP BY ''G'') as ARDisputed,     
      (SELECT ROUND(TO_NUMBER(SUM(R.TRANBAL))) FROM GPCOMP1.GPRECL R WHERE ROUND(TO_NUMBER(SYSDATE-R.INVDATE)) > 90 AND R.TRANTYPE = ''I'' GROUP BY ''G'') as ARUnDisputed, 
      
      (SELECT DISTINCT COUNT(G.PROBLEM_ID)
       FROM          GPCOMP1.GPRECL R, GPCOMP1.GPPROB_RECL GR, GPCOMP1.GPPROB G
       WHERE     R.TRAN_ID = GR.TRAN_ID AND 
                      GR.PROBLEM_ID = G.PROBLEM_ID AND R.TRANTYPE = ''B'') as DisputeGroups,

      (SELECT COUNT(G.PROBLEM_ID) 
       FROM          GPCOMP1.GPRECL R, GPCOMP1.GPPROB_RECL GR, GPCOMP1.GPPROB G
       WHERE     R.TRAN_ID = GR.TRAN_ID AND 
                      GR.PROBLEM_ID = G.PROBLEM_ID AND R.TRANTYPE = ''B'') as DisputesIndividual,
                      
      (SELECT     T.FAXES 
       FROM         GPCOMP1.GPTODO T
       WHERE      T.PERIOD = ''C'' AND T.RECTYPE = ''S'') as EmailsSentToday,       
       
      (SELECT     COUNT(A.CUSTNO)
       FROM         GPCOMP1.GPACTV A  
       WHERE      A.TYPE = ''F'' AND A.ACTVDATE between trunc(SYSDATE)-1 and trunc(sysdate) -1/86400) as EmailsSentYesterday,          
      
      (SELECT     T.CALLS 
       FROM         GPCOMP1.GPTODO T
       WHERE      T.PERIOD = ''C'' AND T.RECTYPE = ''S'') as PhoneCallsToday, 
       
      (SELECT     COUNT(A.CUSTNO)
       FROM         GPCOMP1.GPACTV A 
       WHERE      A.TYPE = ''C'' AND A.ACTVDATE between trunc(SYSDATE)-1 and trunc(sysdate) -1/86400) as  PhoneCallsYesterday,       
       
      (SELECT     COUNT(R.PROMISED) 
       FROM         GPCOMP1.GPRECLLOG R
       WHERE      R.PROMISED = ''P'' AND R.CREATED_ON between trunc(SYSDATE)-1 and trunc(sysdate) -1/86400) as PromisesYesterday,
       
      (SELECT     COUNT(P.PROBLEM_ID) 
       FROM         GPCOMP1.GPPROB P
       WHERE      P.PROBLEM_ID IS NOT NULL AND P.CREATED_ON between trunc(SYSDATE)-1 and trunc(sysdate) -1/86400) as DisputesYesterday                
                    
       FROM GPCOMP1.GPRECL R
       
       WHERE ROWNUM = 1       
       
       GROUP BY ''G'''
       
      SET @Sql = N'INSERT INTO [NonFreight].[#MenuTotal]

      SELECT  * from openquery 
  
     ([GPNFE],    ''' + Replace(@Sql, '''', '''''') + ''')'
     
      --Execute query statement 
      EXEC (@Sql)
      
      
      --Set 'Out Parms' with totals
      
      SET @ARTotal = (SELECT COALESCE(ARTotal, 0)
                      FROM   #MenuTotal)
      SET @ARDisputed = (SELECT COALESCE(ARDisputed, 0)
                         FROM   #MenuTotal)
      SET @ARUnDisputed = (SELECT COALESCE(ARUnDisputed, 0)
                           FROM   #MenuTotal)
      SET @DisputeGroups = (SELECT COALESCE(DisputeGroups, 0)
                            FROM   #MenuTotal)
      SET @DisputesIndividual = (SELECT COALESCE(DisputesIndividual, 0)
                                 FROM   #MenuTotal)
      SET @EmailsSentToday = (SELECT COALESCE(EmailsSentToday, 0)
                              FROM   #MenuTotal)
      SET @EmailsSentYesterday = (SELECT COALESCE(EmailsSentYesterday, 0)
                                  FROM   #MenuTotal)
      SET @PhoneCallsToday = (SELECT COALESCE(PhoneCallsToday, 0)
                              FROM   #MenuTotal)
      SET @PhoneCallsYesterday = (SELECT COALESCE(PhoneCallsYesterday, 0)
                                  FROM   #MenuTotal)
      SET @PromisesYesterday = (SELECT COALESCE(PromisesYesterday, 0)
                                FROM   #MenuTotal)
      SET @DisputesYesterday = (SELECT COALESCE(DisputesYesterday, 0)
                                FROM   #MenuTotal)
  END

Open in new window

0
 
sdstuberCommented:
you have added 2 ROUND calls, which, one seems to make it work? or does it require both?


why are you building an nvarchar   sql string?


build a smaller test case.

what happens if you just do this...


select * from openquery([GPNFE], 'SELECT SUM(R.TRANBAL) FROM GPCOMP1.GPRECL R WHERE (SYSDATE-R.INVDATE) > 90')
0
 
thayduckProgrammer AnalystAuthor Commented:
Tried this and still get same error.

(SELECT SUM(R.TRANBAL) FROM GPCOMP1.GPRECL R) as ARTotal,    



Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
0
 
sdstuberCommented:
ok, but what about any of the stuff I asked about above?

the rounding? the smaller test case? the nvarchar?

0
 
thayduckProgrammer AnalystAuthor Commented:
I do the rounding after doing a lot of searching to get this to work.

Smaller test case below.

The procedure I found that will work inside of a SQL Stored Procedure when accessing Oracle tables uses nvarchar. Never questioned since it always works.
Only issue so far is this ROUND thing.

If I cannot get to work, I will round in my sql subquery when updating OUT parms at end. I just did not think that this would give me so much trouble.

 
USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptMenutest]    Script Date: 01/05/2012 10:52:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [NonFreight].[procRptMenutest] @ARTotal             NUMERIC (15, 2) = 0 OUT                                          
AS
  BEGIN
      SET NOCOUNT ON;

      DECLARE @Sql VARCHAR(max)

      --Build #MenuTotal temp table  
      CREATE TABLE [NonFreight].[#MenuTotal]
        (
           [Group]                CHAR(1),
           [ARTotal]              NUMERIC (15, 2)
           
        )
      ON [PRIMARY]        
        
      --Build query statement that will access Oracle tables and gather figures. 
      --This query statement will create 1 record in #MenuTotal table with totals to be displayed on Menu Web Page.
      
      SET @Sql='SELECT ''G'', 
      (SELECT ROUND(TO_NUMBER(SUM(R.TRANBAL))) FROM GPCOMP1.GPRECL R WHERE ROUND(TO_NUMBER(SYSDATE-R.INVDATE)) > 90 GROUP BY ''G'') as ARTotal 
                    
       FROM GPCOMP1.GPRECL R
       
       WHERE ROWNUM = 1       
       
       GROUP BY ''G'''
       
      SET @Sql = N'INSERT INTO [NonFreight].[#MenuTotal]

      SELECT  * from openquery 
  
     ([GPNFE],    ''' + Replace(@Sql, '''', '''''') + ''')'
     
      --Execute query statement 
      EXEC (@Sql)
      
      
      --Set 'Out Parms' with totals
      
      SET @ARTotal = (SELECT COALESCE(ARTotal, 0)
                      FROM   #MenuTotal)
      
  END

Open in new window

0
 
sdstuberCommented:
one more try,  You have 2 uses of ROUND, which one does the "magic" that makes it work or do you need both of them?

how have you validated the data types of the columns?  

also,  "GROUP BY 'G'"  doesn't make any sense, or is that part of the "magic" too?

have you tried NOT using nvarchar?

in your example it's particularly wierd because you start with varchar and then concatenate to create nvarchar

have you tried the simple test case with the query I posted?  That really is valid syntax for oracle.

if you did,  make it even simpler


select  tranbal from GPCOMP1.GPRECL where rownum = 1
0
 
sdstuberCommented:
try this...


USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptMenutest]    Script Date: 01/05/2012 10:52:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [NonFreight].[procRptMenutest] @ARTotal             NUMERIC (15, 2) = 0 OUT                                          
AS
  BEGIN
      SET NOCOUNT ON;

      DECLARE @Sql VARCHAR(max)

      --Build #MenuTotal temp table  
      CREATE TABLE [NonFreight].[#MenuTotal]
        (
           [Group]                CHAR(1),
           [ARTotal]              NUMERIC (15, 2)
           
        )
      ON [PRIMARY]        
        
      --Execute query statement 
      EXEC ('INSERT INTO [NonFreight].[#MenuTotal] 
	          SELECT * from openquery([GPNFE],''select tranbal from GPCOMP1.GPRECL where rownum = 1'')')
      
      
      --Set 'Out Parms' with totals
      
      SET @ARTotal = (SELECT COALESCE(ARTotal, 0)
                      FROM   #MenuTotal)
      
  END

Open in new window

0
 
thayduckProgrammer AnalystAuthor Commented:
I do not want to use ROUND, but when I remove, stored procedure fails.

Have validated fields. These Oracle tables are part of  a software package that company has used for years.

Took out Group BY G. Did not need.

Just tried nvarchar and get same error.

Ran your query and it works.  Now how do you get it to work with summing ?
USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptMenutest1]    Script Date: 01/05/2012 11:42:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [NonFreight].[procRptMenutest1] @ARTotal             NUMERIC (15, 2) = 0 OUT                                          
AS
  BEGIN
      SET NOCOUNT ON;

      DECLARE @Sql VARCHAR(max)

      --Build #MenuTotal temp table  
      CREATE TABLE [NonFreight].[#MenuTotal]
        (
           --[Group]                CHAR(1),
           [ARTotal]              NUMERIC (15, 2)
           
        )
      ON [PRIMARY]        
        
      --Execute query statement 
      EXEC ('INSERT INTO [NonFreight].[#MenuTotal] 
	          SELECT * from openquery([GPNFE],''select tranbal from GPCOMP1.GPRECL where rownum = 1'')')
      
      
      --Set 'Out Parms' with totals
      
      SET @ARTotal = (SELECT COALESCE(ARTotal, 0)
                      FROM   #MenuTotal)
      
  END

Open in new window

0
 
sdstuberCommented:
we'll take it one piece at a time until we see where the error comes from
so, first, simply add the SUM

USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptMenutest1]    Script Date: 01/05/2012 11:42:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [NonFreight].[procRptMenutest1] @ARTotal             NUMERIC (15, 2) = 0 OUT                                          
AS
  BEGIN
      SET NOCOUNT ON;

      DECLARE @Sql VARCHAR(max)

      --Build #MenuTotal temp table  
      CREATE TABLE [NonFreight].[#MenuTotal]
        (
           --[Group]                CHAR(1),
           [ARTotal]              NUMERIC (15, 2)
           
        )
      ON [PRIMARY]        
        
      --Execute query statement 
      EXEC ('INSERT INTO [NonFreight].[#MenuTotal] 
	          SELECT * from openquery([GPNFE],''select SUM(tranbal) from GPCOMP1.GPRECL'')')
      
      
      --Set 'Out Parms' with totals
      
      SET @ARTotal = (SELECT COALESCE(ARTotal, 0)
                      FROM   #MenuTotal)
      
  END

Open in new window

0
 
thayduckProgrammer AnalystAuthor Commented:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptMenutest1]    Script Date: 01/05/2012 11:42:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [NonFreight].[procRptMenutest1] @ARTotal             NUMERIC (15, 2) = 0 OUT                                          
AS
  BEGIN
      SET NOCOUNT ON;

      DECLARE @Sql VARCHAR(max)

      --Build #MenuTotal temp table  
      CREATE TABLE [NonFreight].[#MenuTotal]
        (
           --[Group]                CHAR(1),
           [ARTotal]              NUMERIC (15, 2)
           
        )
      ON [PRIMARY]        
        
      --Execute query statement 
      EXEC ('INSERT INTO [NonFreight].[#MenuTotal] 
	          SELECT * from openquery([GPNFE],''select SUM(tranbal) from GPCOMP1.GPRECL'')')
      
      
      --Set 'Out Parms' with totals
      
      SET @ARTotal = (SELECT COALESCE(ARTotal, 0)
                      FROM   #MenuTotal)
      
  END

Open in new window

0
 
sdstuberCommented:
where does the error occur?

on the exec?  or on the set @artotal?
0
 
thayduckProgrammer AnalystAuthor Commented:
When I use ROUND(SUM(tranbal)) it runs with no error and a sum.
0
 
sdstuberCommented:
but what about what I asked?
0
 
thayduckProgrammer AnalystAuthor Commented:
exec
0
 
sdstuberCommented:
I'm going to guess it's happening on the EXEC

 EXEC ('INSERT INTO [NonFreight].[#MenuTotal] 
	          SELECT Convert(float,"[sumtranbal]")) from openquery([GPNFE],''select SUM(tranbal) sumtranbal from GPCOMP1.GPRECL'')')

Open in new window

0
 
thayduckProgrammer AnalystAuthor Commented:
yes. It is happening on exec.
0
 
sdstuberCommented:
did the convert work or not?
0
 
thayduckProgrammer AnalystAuthor Commented:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

(1 row(s) affected)

(1 row(s) affected)
0
 
thayduckProgrammer AnalystAuthor Commented:
Remove extra ) and got this error....


Msg 207, Level 16, State 1, Line 3
Invalid column name '[sumtranbal]'.

(1 row(s) affected)

(1 row(s) affected)
0
 
thayduckProgrammer AnalystAuthor Commented:
No convert did not work. Got 2 previous errors..
0
 
thayduckProgrammer AnalystAuthor Commented:
SELECT Convert(float,"[sumtranbal]"))  changed  to  SELECT Convert(float,''[sumtranbal]'')               correct me if wrong and got below error..

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
0
 
sdstuberCommented:
sorry I got my oracle/sqlserver syntax mixed up


EXEC ('INSERT INTO [NonFreight].[#MenuTotal]
                SELECT Convert(float,"SUMTRANBAL") from openquery([GPNFE],''select SUM(tranbal) SUMTRANBALfrom GPCOMP1.GPRECL'')')
0
 
thayduckProgrammer AnalystAuthor Commented:
Changed " (quote) to ''   ''[sumtranbal]'')       Or I could be wrong.....
0
 
sdstuberCommented:
oops, didn't see you had posted.

So it recognizes the column now?

what does your whole EXEC look like?
0
 
sdstuberCommented:
The "  around SUMTRANBL  are supposed to be double quotes,  not 2 single quotes
0
 
thayduckProgrammer AnalystAuthor Commented:
EXEC ('INSERT INTO [NonFreight].[#MenuTotal]
                SELECT Convert(float,"SUMTRANBAL") from openquery([GPNFE],''select SUM(tranbal) SUMTRANBALfrom GPCOMP1.GPRECL'')')




OLE DB provider "OraOLEDB.Oracle" for linked server "GPNFE" returned message "ORA-00923: FROM keyword not found where expected".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select SUM(tranbal) SUMTRANBALfrom GPCOMP1.GPRECL" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "GPNFE".
0
 
thayduckProgrammer AnalystAuthor Commented:


                SELECT Convert(float,"SUMTRANBAL") from openquery([GPNFE],''select SUM(tranbal) SUMTRANBAL from GPCOMP1.GPRECL'')')





Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.
0
 
sdstuberCommented:
ok, it's definitely a problem with openquery


use round, but, specify a precision that won't affect your result

for instance,  you are putting the sum into a numeric(15,2)  so use round(sum(tranbal),2)
0
 
sdstuberCommented:
also, check your drivers

there's no good reason a float from oracle should be returned as an nvarchar result.  It's getting converted/corrupted somewhere along the way
0
 
slightwv (䄆 Netminder) Commented:
>> so use round(sum(tranbal),2)

If it just the rounding that is the issue then use TRUNC.

for example they want 123.4567 to be 123.45 not 123.46:

trunc(sum(tranbal),2)
0
 
sdstuberCommented:
another option,  use to_char(sum(tranbal),'fm999999999999.99')

and see if the convert will work if we force a string value of our choice to be passed through,
rather than letting openquery mess with it
0
 
thayduckProgrammer AnalystAuthor Commented:
This does not work.

round(sum(tranbal),2)

(SELECT ROUND(TO_NUMBER(SUM(R.TRANBAL),2)) FROM GPCOMP1.GPRECL R WHERE ROUND(TO_NUMBER(SYSDATE-R.INVDATE)) > 90 GROUP BY ''G'') as ARTotal,


OLE DB provider "OraOLEDB.Oracle" for linked server "GPNFE" returned message "ORA-01481: invalid number format model".
0
 
slightwv (䄆 Netminder) Commented:
>>ROUND(TO_NUMBER(SUM(R.TRANBAL),2))

The ",2" needs to go with the ROUND call not the TO_NUMBER call.

ROUND(TO_NUMBER(SUM(R.TRANBAL)),2)

I still don't see the need for to_number but you seem to want to use it.

0
 
sdstuberCommented:
you've regressed back to the original stuff.

SELECT ROUND(SUM(R.TRANBAL),2) FROM GPCOMP1.GPRECL R WHERE (SYSDATE-R.INVDATE) > 90
0
 
sdstuberCommented:
when you put to_number around a value that is already a number, you force an implicit conversion of the number to a string and then back to a number again.  or if your to_number is overloaded to accept numbers, it's just  null step that doesn't really do anything except add a function call overhead
0
 
thayduckProgrammer AnalystAuthor Commented:
Guys, I do not want to use the ROUND or TO_NUMBER, but when I take it out, I get errors.

 I rather get figures that are rounded then no figures.

That is why I started this call, to get rid of ROUND.


This code causes errors:

ROUND(TO_NUMBER(SUM(R.TRANBAL)),2)
0
 
thayduckProgrammer AnalystAuthor Commented:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
0
 
sdstuberCommented:
>>>ROUND(TO_NUMBER(SUM(R.TRANBAL)),2)

as suggested multiple times don't use TO_NUMBER, take it out.
Unless you're actually going to convert something that isn't a number to a number,  don't use a conversion function.

we know what you want to do.  

the problem is COMPLETELY VALID SYNTAX is being ruined by openquery.

we can't do anything to change openquery.  So, you either upgrade your drivers so openquery will leave numeric results as numeric and quit trying to interpret them as nvarchar,   or if you are stuck with it as is, then you have to try to trick it.

ROUND (when it's not encumbered by other problems) seems to work as a trick to do whatever "magic" openquery needs.



0
 
thayduckProgrammer AnalystAuthor Commented:
Let me ask you this:

When this below statement is executed, am I rounding the final sum total or each individual TRANBAL before it is added to the final sum ?

If it is rounding the final sum, the most I would be off is 1.00 right ?


This code does work....

(SELECT ROUND(TO_NUMBER(SUM(R.TRANBAL)))  FROM GPCOMP1.GPRECL R  WHERE (trunc(sysdate) -1/86400) > R.INVDATE+90 GROUP BY ''G'') as ARTotal
0
 
sdstuberCommented:
ROUND(TO_NUMBER(SUM(R.TRANBAL)))


 sums the tranbal

then does a completely useless function call with to_number,  why won't you remove it?

then finally rounds the result
0
 
thayduckProgrammer AnalystAuthor Commented:
OK, took TO_NUMBER out and program still works.

But, it will not work without ROUND.
0
 
sdstuberCommented:
what about  

round(sum(r.tranbal),2)
0
 
sdstuberCommented:
also looks like you added the   GROUP BY 'G' back in

and you've modified your date clause.  

if you want all data that is more than 90 days before today,  then try this...

trunc(sysdate) -90 > R.INVDATE

if you have indexes on R.INVDATE,  this form will allow them to be used.

 I'm not sure what you were trying to do; but I'm pretty sure your date math with the 1/86400 was incorrect.

The result of (trunc(sysdate) -1/86400) > R.INVDATE+90

is equivalent to daying  you want invdate less than or equal to 2011/10/06 23:59:58

is that what you want?  an upper limit of 2 seconds before midnight from 3 months ago?

0
 
thayduckProgrammer AnalystAuthor Commented:

(SELECT round(sum(r.tranbal),2) FROM GPCOMP1.GPRECL R WHERE (trunc(sysdate) -1/86400) > R.INVDATE+90 GROUP BY ''G'') as ARTotal,    

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
0
 
thayduckProgrammer AnalystAuthor Commented:
I was given this code by another expert on this site.
I was using in this program later on. I thought I could use it to only select invoices that are aged over 90 days.


Originally used here:

(SELECT     COUNT(A.CUSTNO)
       FROM         GPCOMP1.GPACTV A  
       WHERE      A.TYPE = ''F'' AND A.ACTVDATE between trunc(SYSDATE)-1 and trunc(sysdate) -1/86400) as EmailsSentYesterday,    

Will this not work ?    
0
 
thayduckProgrammer AnalystAuthor Commented:
Took out GROUP BY and program still runs.

Also changed WHERE statement took look for invoices over 90 days old.

(SELECT ROUND(SUM(R.TRANBAL)) FROM GPCOMP1.GPRECL R WHERE trunc(sysdate) > R.INVDATE+90) as ARTotal,
0
 
slightwv (䄆 Netminder) Commented:
We understand that you do not want to use ROUND.  As stated before, the result is being placed in a 15,2 number ([[ARTotal]              NUMERIC (15, 2)
]   NUMERIC (15, 2))

If the sum ends up coming back as: 123.45678, what result do you want in ARTotal?
0
 
thayduckProgrammer AnalystAuthor Commented:
123.45
0
 
slightwv (䄆 Netminder) Commented:
>>123.45

re http:#a37382829

Use TRUNC instead of ROUND.
0
 
thayduckProgrammer AnalystAuthor Commented:
I put TRUNC instead of ROUND but final total shows xxxxxx.00.  So to me, it looks like it is still rounding to nearest dollar.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Did you specify the precision like in the post?

trunc(sum(tranbal),2)
0
 
thayduckProgrammer AnalystAuthor Commented:
Get this error when using trunc(sum(tranbal),2)



Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.


Get no error when using trunc(sum(tranbal))
0
 
thayduckProgrammer AnalystAuthor Commented:

THIS WORKED.

(SELECT TO_CHAR(SUM(R.TRANBAL)) FROM GPCOMP1.GPRECL R WHERE trunc(sysdate) > R.INVDATE+90) as ARTotal,


If you suggested this earlier and I missed, my fault.

But program runs and sums with no rounding.
0
 
thayduckProgrammer AnalystAuthor Commented:
Thanks for all help..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.