Count Rows

[JPimp]

I am trying to count the # of Rows in a Web based query from an Oracle DB, when I do this in SQL, it works superb, but in Oracle, I get no data at all or an error.

This is the code (pre-count)

SELECT * FROM WO_WORK_ORDER, SY_WO_STATUS WHERE WO_WORK_ORDER.PLANT = SY_WO_STATUS.PLANT AND WO_WORK_ORDER.STATUS = SY_WO_STATUS.CODE AND WO_WORK_ORDER.WO_TYPE = 'PM-Work Order'

If I insert a Count(*) or Count(WO_TYPE), which is what I want, I get absolutely nothing....

What am I doing wrong??

Kai-What?

 

[SantaMufasa]

I get absolutely nothing

Under what code circumstances do you get absolutely something? There is nothing particularly magically good or magically bad about Oracle's "COUNT(*)" function. So, if I were a betting man, I would say that there are no rows in your Oracle environment where your WHERE clause returns any rows, but in your SQL Server, there are rows that meet all of your WHERE-clause criteria.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JPimp]

If I do Count(*), i.e.

SELECT COUNT(*) FROM WO_WORK_ORDER WHERE DEPARTMENT = '06211' AND WO_TYPE = 'PM-Work Order' it gives me a row count, but if I were to add other criteria, i.e. I want to use the fields Department, WO_TYPE and a couple of other fields and then do a count on WO_TYPE for example, it returns an error that something is wrong with the statement, but nothing specific?

Kai-What?

 

[SantaMufasa]

...it returns an error that something is wrong with the statement, but nothing specific...

I've heard of that error:

ORA-37448: Something is wrong with the statement, but nothing specific.

<grin>


I'd love to actually see the error...Could you please post an actual copy and paste of both your SQL statement followed by the error message? If so, we can help.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

I've never yet come across an Oracle error along the lines of "ORA-nnnn, there is something wrong with this statement but we're not sure what."

Oracle always returns an ORA- code to indicate what the error was. It is usually fairly specific about what is wrong with the query. What is the error you're getting ?

 

[SantaMufasa]

Great thinks mind alike...HeHeHe.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JPimp]

Hehehehe, yeah I know what you mean, and I should have mentioned this from the go, I am pulling the data in using an Office Web component in FP 2003, hence the "no error" or any specific error message, it just gives me the lovely Microsoft generic error that "something is not correct..."..

So my guess at this point is that Oracle 8i is not playing nice with Office web components... :- (

Kai-What?

 

[JPimp]

Duuuh..stupid rookie mistake, I did not "Group By" all the data fields I was querying on, hence the issue...all solved!

Kai-What?

 

[Dagon]

So my guess at this point is that Oracle 8i is not playing nice with Office web components

Or maybe it's the other way round - the MS software is not playing nice with Oracle.

 

[JPimp]

Yeah, more likely, as we all know how Billy Boy wants us all to use MS SQL with all MS components :- )

Kai-What?