SQL Calculation Fails Every Time

[LifeAero]

Hey,

I'm at a complete loss right now, so if anyone here could give me some help
with this, it would be highly appreciated. I've spent the last entire work day
searching for an answer to this problem, but I've come up with absoloutely
nothing.

I am working on a project at work which involves using ColdFusion MX 6.1 and
Oracle SQL 9.1i (amongst other things), now I'm by no means an expert with
either of these, but I know enough to get by. However, I'm having the
stupidest problem right now trying to run a simple SQL Query.

An example of something I am trying to do is as follows:

<cfquery name="test" datasource="wh_devel">
SELECT program, bac, eac, bac + eac as baceac
FROM OPP.CAWP
WHERE rownum <=5
</cfquery>

When I run this CFML, I get the following error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC driver for
Oracle]Error in row 1

The error occurred in C:\inetpub\WH_Devel\TMPe5n96m2u47.cfm: line 10

8 :
9 :
10 : <cfquery name="test" datasource="wh_devel">
11 : SELECT program, bac, eac, bac + eac as baceac
12 : FROM OPP.CAWP

Even if I try using SELECT program, bac, eac, (bac + eac) as baceac, or even
use a different mathematical operator, such as *, /, - etc, it still returns
the same errors.

Now, I know the problem is not with Oracle, as using their SQL Plus interface,
these queries all work perfectly, outputting the fields and then outputting a
field calculated from adding "bac" and "eac", but I need this to work in a CFM
page. I've tried doing the math calculations for queries like this in CFML
tags, rather than in the SQL query, but it only outputted a single record,
rather than all the records I am pulling off with the query.

I've tried this using several different tables in our database, and a few
different CFM pages we have cooked up, but I get the same problem every time.
I also thought it might be that the + symbol in my query required an escape
character to avoid being seen as CFML, but that also didn't help. The cfquery
tag is fine, as is the datasource - if I take the "bac + eac" out, it works
perfectly - so it's nothing to do with that either.

Is this some kind of bug in ColdFusion or am I doing something completely
wrong?

Thanks in advance for any help you guys can give me.


Ross
Smiths Aerospace
Electronic Systems, Cheltenham

[CF_Oracle]

The code looks right. Check columns format make sure they are numbers and not
NULL . Put to_number around like (to_number(bac) + to_number(eac)) as baceac -
it would not heart and maybe you will get more specific error message.
Good luck!

[Heapster]

First thing to check is that your datasource verifies OK in Administrator I would suggest.

[LifeAero]

Hey guys, sorry I didn't reply for so long, but for future reference, I didn't
get this problem solved using what I was originally trying.

I tried out what you said CF_Oracle, but got the same errors, both columns are
already in number format and NOT NULL. Also Heapster - the datasource is fine,
we are using it for hundereds of other pages with no issues like this.

In the end I just used a loop and set a variable to equal bac + eac and
outputted it to the form, rather than running the calculation through the
CFQUERY. Not ideal, but it does the job and I can't afford to waste any more
time hunting down the cause of the original error.

Anyway, thanks alot to both of you for the help.


Ross
Smiths Aerospace
Electronic Systems, Cheltenham.

[paross1]

Judging from your error message, I believe that your main problem is with using
Microsoft's ODBC for Oracle driver, instead of the driver from Oracle, or the
"native" JDBC connection. Years ago I had nothing but problems using the
Microsoft driver because of its severe limitations, especially when I tried
calling stored procedures.

Phil

[LifeAero]

Hey paross1, I realise it was a couple of weeks ago now, but thanks for the
post.

I think you may be spot on with the driver issue. Last night I started working
on our financial reporting framework, and noticed that if I run a SELECT SUM()
through a cfquery on any type of column, number, float etc, regardless of the
size or precision set when the table was created, and any of the numbers
involved, including the calculated total is over 99, CF throws up that same bug
"Error in row 1".

Obviously this is not good when 95% of the figures we are dealing with are 6
or 7 digits.

Unfortunately for me, the particular server I'm working on is off-site, and
administrated at one of our other sites in the US (I'm in the UK), so I'm going
to have to try and get hold of someone in IT there and explain this to them,
and hopefully get them to sort out the driver. Until then, I'm pretty much
stuck which is irritating.

Alright, well thanks for the help, it's appreciated. If / when I get this
sorted out I'll post up the solution for future reference.

[surenr]

Check if you have some numerical values stored for bac and eac.Sometimes when
you do not store anything it might store NULL which might cause the error. One
method is to store 0 (digit zero) for all NULL values for bac and eac attribute
in the database.

INSERT (bac)
VALUE(0)
WHERE bac =NULL

similarly for eac.

[zaq42]

Hi LifeAero, did you ever get an answer for this problem? This is exactly the
problem we face, and we cannot drop the driver from ODBC and start using JDBC.
Certain Oracle SQL causes errors going through either MS or Oracle's ODBC
driver.

Any ideas?

[zaq42]

All is well. I had an error in my JDBC connection. User error...anyone shoked? :-)