EasyCFM.COM ColdFusion Forums / Oracle Related Issues / *Misspelled* SQL which still works in SQLPlus

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: *Misspelled* SQL which still works in SQLPlus

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

kauk0
07-28-2005 @ 1:40 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 10
Joined: Apr 2004

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 435
Joined: Feb 2003

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 435
Joined: Feb 2003

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


Website Designed and Developed by Pablo Varando.