Sitemap

Amicuk Programming Answers

Select Count(*) from... returns zero, select * from... returns 4000 recs?

-0001-11-30   Views:0

Advertisement

Hi, I have an odd situation where "select count(*) from my_view where some_clause" returns 0, but the exact same query fetching the data (ie "select * from my_view where some_clause") works fine and fetches, correctly, 4000+ records. I

Hi,
I have an odd situation where "select count(*) from my_view where some_clause" returns 0, but the exact same query fetching the data (ie "select * from my_view where some_clause") works fine and fetches, correctly, 4000+ records.
I have tried dropping and recreating the view, and collecting DB stats again, but the problem persists.
I had a look at the explain plans and they are very different, but both the SQL statements work fine on a copy of the database taken with impdp.
Does anyone have any suggestions on what might be going on here ?
Any help would be appreciated, I've run out of ideas !
Dave
Edited by: user10281551 on 18-Sep-2008 00:43
Edited by: user10281551 on 18-Sep-2008 00:44

The replay answer
Advertisement
Maybe it's the bug that a function based index is present and gets incorrectly used in the select count(*) statement?
If that's not the case, then please show us more details, like the statement and both execution plans.
Regards,
Rob.

Go to See the other 7 answers

Select Count(*) from... returns zero, select * from... returns 4000 recs?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have an odd situation where "select count(*) from my_view where some_clause" returns 0, but the exact same query fetching the data (ie "select * from my_view where some_clause") works fine and fetches, correctly, 4000+ records. I[More]

Count(1) returns null in group by

Category:DefaultRelease time:2015-10-11Views:130

hi gems..good afternoon.. I read that the COUNT() function always returns 0 (zero) if there is no matching rows in the table. The following code returns the 0 as expected: SELECT COUNT(1) FROM book_table              WHERE client_id = 10009          [More]

Why oracle function return 4000 characters ?

Category:DefaultRelease time:-0001-11-30Views:130

Dear All When I write any function to get some return value in a query, its width is set to 4000. Moreover, when i write the same query to create any LOV, it gives an error that record group field cannot be greater than 2000. Where the actual width o[More]

Count rows returned in a subquery in pl/sql block

Category:DefaultRelease time:2015-10-11Views:130

Hi, I'm working on a trigger in Oracle 11 to check that a student has met prerequisites before enrolling in a class. I need to determine how many rows came back on the query in order to determine if I should raise an exception or not. The following i[More]

An Average of a Count erroneously returns integers and not decimals

Category:DefaultRelease time:-0001-11-30Views:130

I have a table which holds sales information showing the invoice number and the branch (e.g. 'New York', 'Boston', etc..)  that sold the items. Each row represents a product on an invoice. I am trying to find out the average number of lines per invoi[More]

Query of Query count(column) returns blank not zero

Category:DefaultRelease time:-0001-11-30Views:130

This appears to be a bug in 7,0,1,116466 and 8,0,1,195765 Query of Query count(column) or count(*) returns blank not zero when there are no matches. Properly returns a number when there are query matches. e.g. select count(i_id) from PersonnelQuery w[More]

Counting Numbers Returned from a query

Category:DefaultRelease time:-0001-11-30Views:130

I am running a command using VMM 2012: Get-VMHost -ComputerName HV07-BH | Get-VM | Select-Object CPUCount It returns. I need to add the numbers listed below to get a total of 29 in a single command. I am unsure of how to do this. Any ideas would be a[More]

Help - count function returns strange results

Category:DefaultRelease time:-0001-11-30Views:130

hi everyone, here's my scenario: i'm trying to get the NUMBER OF REPORTS and NUMBER OF IMAGES GROUP BY MONTH from the two tables below. REPORT reportid(*primary key) date IMAGE reportid(*foreign key referring to report's table) image sample output: M[More]

How to also show counts that return 0?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I'm trying to count the number of users from some given countries. It goes like this: select country_id,count(*) from user_registry where user_id IN (307, 317,1472) and country_id IN (0,2,3) group by country_idThis is OK, and I get 2 users from c[More]

Trace(this["count"+ii]); returns undefined within function

Category:DefaultRelease time:-0001-11-30Views:130

So I have a function that looks like this. fu = function () { var count1 = 0; var count2 = 0; var count3 = 0; for (var ii = 1; ii<=3; ii++) { trace(this["count"+ii]); when it runs it returns undefined undefined undefined However,when I declar[More]

Advanced Data Grid with Count only returns zero

Category:DefaultRelease time:-0001-11-30Views:130

I'm just getting started with Flex and currently having problems getting the AdvancedDataGrid to count the total number of records for a group. I have the following for the control but all I get in the StudentCount column is 0 for all grouped rows. C[More]

Hot
Hi expert, i need your help!! i have configured the "Extended Notifications for SAP Business Workflow" with the tnx SWNADMIN and this was successful. I can write emails, but i can read this only on SAP GUI with the tnx SOST. I don't receive emai [More]
I have purchased 3 videos from itunes music store but they wont download. A message pops up that reads...."There was an error downloading your purchased music. The disk could not be read from or written to." I have gone to the advanced tab and c [More]
my pad mini not finish the icloud copy. I have try meny times and 5-6 days every day. it writes last copy 16/10/2013. sorry for my english. Please help meHello, rvj112.  Thank you for visiting Apple Support Communities.  I would need a little more in [More]
Hi all, hope someone can help with this one. Sometimes when I am doing drop ins using punch on the fly, no audio gets recorded..weird. If I deselect the record button and reselect it it seems towork again but this should not be surely. I am not using [More]
Hi, Suddenly the Oracle 11g r2 RAC scan listener was changed to intermediate state in our envrionment. How can we change state from intermediate to online state? please find the details as below, OS : HP Unix B.11.31 DB Version: 11.2.0.3.0 ora.LISTEN [More]
I had to disable iCloud Photo Library (as part of logging out of iCloud on my Mac) to troubleshoot an iCloud issue. When I logged back in to iCloud ad re-enabled iCloud Photo Library it says I don't have enough disk space to download the originals (3 [More]
When I write formulas, I sometimes get a syntax error. However when I quit Numbers, open my document again, click on the syntax error and then press "enter," the syntax error has been corrected. Because I've done this many times, I know it's not [More]
Hello EveryOne, Please help me urgently. I am updating some record through update page and I want to display them at home page ..I am getting problem in displaying fullname of employee , Salary, and Position....Remaining values I am able to display.. [More]
Hello. Im studying java swing and working on some experiment and badly need some help. Im using Graphics2D and not JLable or Icon for some reason. The problem is the image does not show up. What could be the reason and work around? Another, do you kn [More]
Hi, i want to set up master rep & work rep & execution rep in sunsolaris i have 2 ips one for production and 1 for developement. how many oracle users i requred and to set up master rep & work rep & execution rep's. please suggest me how t [More]