Topic: *Misspelled* SQL which still works in SQLPlus


kauk0    -- 07-28-2005 @ 1:40 AM
  Hi, I'm having problems with oracle database queries.
Problem is that it seems my SQL is misspelled somehow.
I'm connecting to Oracle database with with Microsoft' ODBC for Oracle. The ODBC driver what comes with ORa9.2 does not show floating numbers correctly (in Access). Workaround is to use the MS driver. But when I created datasource from that ODBC Socket, it is very picky about SQL. for example ';' must stripped off and so on. Gives me "ORA-00911" errors. It also seems that no Sum(), Count() functions can be used in this datasource.
It gives me error as below:

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

SQL: part of it

     SELECT
          NW.NAME AS RegionA,
          AI.START_TIME AS HourA,
          Sum(AI.REGISTER_VALUE) AS EnergyA,
          Count(AI.START_TIME) AS CountA

Those Count() and Sum() seemed to be the problem.
If I qoute them there is no error but results are wrong.

SQL: 'Sum(AI.REGISTER_VALUE)' AS EnergyA,

Also this works either:
Sum'('AI.REGISTER_VALUE')' AS EnergyA,

All these are to show floating numbers correctly.

If I create test.mdb and ODBC with Ora92 (OraTest) and link tables from OraTest to test.mdb Access shows
number like "0.3453" & "0.898" as NULLS. Numbers like "1.234" are as "1234" or something.

Thats why I thought I should connect directly to Oracle but CFMX Standard has not  Oracle-driver.
So i tried tthe above..

Verry messy situation.

if someone still understood what I tried to explain (newbie way Smile ) and knows workaround I'd be very happy.






sparty2809    -- 01-06-2006 @ 8:32 AM
  Did you ever get this to work?

Thanks,
sparty2809

Please help me get a Laptop!
http://laptops.freepay.com/?r=25452082
And while you are at it, get one for yourself!


Webmaster    -- 01-06-2006 @ 10:39 AM
  well, have to see your entire query. What are you grouping by?

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email


sparty2809    -- 01-06-2006 @ 10:47 AM
  My query is like the one above:

SELECT   Sum(VALUE1) Value1
          Sum(Value2) Value2
FROM TABLE

Thanks,
sparty2809

Please help me get a Laptop!
http://laptops.freepay.com/?r=25452082
And while you are at it, get one for yourself!

This message was edited by sparty2809 on 1-6-06 @ 10:59 AM


EasyCFM.COM ColdFusion Forums : http://archive.easycfm.com/forums
Topic: http://archive.easycfm.com/forums/viewmessages.cfm?Forum=33&Topic=7533