SQL & PL/SQL :: Record Count Mismatch In Dataset And Query Executed
Oct 14, 2011
I am using an query to fetch the data from oracle DB and fill dataset using oledb dataadapter in ASP.net.When i run the same query in PL/SQL i am getting 14952 records,but when i am filling it to dataset i am getting only 13700 records.
We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
I have some troubles when I try to retrieve last executed queries in a database.
For example;
I run the script below:
select distinct t.first_load_time, t.sql_text, t.last_load_time, s.username from v_$sql t, v$session s where s.username='SYS'
And as a result, I retrieve the queries executed by SYS user. But the problem is that, if SYS user executed the same query more than once, only the very first record is shown.
It is like this,
SYS user executes "select * from table_abc" at 10:54:35, and after that SYS executes the same query at 13:45:55. and after running the query above, I can only see the record which was executed at 10:54:35. I need to see the both results.
I have a query which is executing fast in dev env,but very long time in qa env.What is the criteria when this behaviour occurs.Though qa is having more data than dev.But still it is taking long time for 1 rows also.When I am using the query rownum<=1.So What to check for this.
I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?
The code I have is as follows:
CREATE OR REPLACE PACKAGE qi_test IS
TYPE typ_qi_data IS RECORD( iQIFlag NUMBER(1), iIPFlag NUMBER(1), iRiskIndicator NUMBER(1), iDenominator NUMBER(8), iNumerator NUMBER(8) [code]........
I want to be able to execute the above function using dynamic SQL. Initially tried:
DECLARE f2_data qi_test.typ_qi_data_tab; BEGIN EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;' USING OUT f2_data; [code]......
...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:
f2_data := qi_test.get_f2_data;
...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.
What is the maximum number of rows that is possible to be produced by inner joining two tables having N and M (M < N) number of records on a non-prime column?
I am trying to get a record count of this table and i get 2 different output , select count(*) shows 68560 and after updating the stats the NUM_ROWS shows 68323, why the difference .
SQL> select count(*) from dcepcd;
COUNT(*) ---------- 68560
SQL> select num_rows from dba_tables where table_name='DCEPCD';
ct_id and limit_id is match column used for mapping.
now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.
Why showing at the status bar "3 records applied and saved" instated of "2 records applied and saved". 2 means 1 for Header and 1 for Detail. How I change status bar.
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this
name semester status year A 1 enrol 2010 A 2 withdraw 2010 A 3 enrol 2010 B 1 enrol 2010 B 2 withdraw 2010
I want to count their latest status only. It should come up with Total Enrol - 2 Total Withdrawn - 1
For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work? Here's my calculation:(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name)) It tells me that 'Aggregation of Analytic function not allowed'
Our application has a homepage that displays results of several SQL statements that are defined as reports. One report in particular should only display a custom message when Count of Invoices having a particular status > 0, and not the actual invoice count itself.
I have gotten the layout to not display the count but I need to be able to display a custom message that says "Problem Invoices exist. Please see Invoices Report for more information." I've looked over the Report Attributes definition page but I cannot find how to display this custom message.
I' m doing a query on multiple tables willing to get only top scorers from a certain round. Here's the relevant part of relation:
SOCCER_TEAM(TEAMID, NAME, CITY) PLAYER (PLAYERID, NAME_SURNAME, DOB, TEAMID) GAME_STATS(ROUNDID, GAMEID, TIME, PLAYERID, STATTYPE)
TIME is No between 1-90 representing the minute of the game STATTYPE is IN('GOAL', 'OWN GOAL', 'RED', 'YELLOW')
Here's my sql code for the query:
SELECT ROUNDID, NAME_SURNAME, NAME, COUNT(STATTYPE) FROM GAME_STATS, PLAYER, SOCCER_TEAM WHERE PLAYER.PLAYERID IN (SELECT GAME_STATS.PLAYERID FROM GAME_STATS WHERE STATTYPE='GOAL' AND PLAYER.TEAMID = SOCCER_TEAM.TEAMID) AND STATTYPE='GOAL' AND GAME_STATS.PLAYERID = PLAYER.PLAYERID GROUP BY ROUNDID, NAME_SURNAME, NAME ORDER BY ROUNDID, COUNT(STATTYPE) DESC
This results in correctly displaying all scorers from all the rounds, yet I haven't been able to construct the HAVING clause to display ONLY the top scorers from each round (there can be multiple of them scoring equal top amount of goals and I need to show them all)
p.s. I have underlined primary keys, while foreign keys are in cursive, if it is of any relevance
I need to modify my query so that it can give me a total(duration) and total(stlmntcharge) per day in april 2013 starting from the 1st till the 30th. At the moment my query looks like below:
SELECT sum(duration),sum(stlmntcharge) FROM voipcdr WHERE (calldate >= TO_DATE('20130401','YYYYMMDD') AND calldate <= TO_DATE('20130430','YYMMDD')) AND (remtrunkid IN (SELECT UNIQUE trunkid FROM trunks WHERE description LIKE '%Telkom%' AND gw_range_id = '61' AND trunkid like '9%'))
or remip in(SELECT UNIQUE startip FROM gateways WHERE rangename LIKE 'vo-za%' OR rangename LIKE 'PC-IS-VOIS%')
But I cannot get the count I have to do a separate Query to get the count here it is How can I put the two together to get my count information and Report information together in one Report???
Select Count(pm.description), mv.R1_State FROM windsoradm.member_mv mv
i running a sql-query in visual studio 2005 with the oracle dataset. currently my datetime is in format mm/dd/yyyy hh:mm:ss. I wish to split the datetime in dd/mm/yyyy only(without the hh:mm:ss)
EXEC (' SELECT XNP_TIMER_VIOLATION.VIOLATION_TIME,.. FROM XNP_TIMER_VIOLATION,.. ) AT npcrpt ;
How to count siblings in a hierarchical query? I'm trying to get a listing of employees
SELECT LEVEL, last_name||', '||first_name AS Manager, count(employee_id) FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id GROUP BY level
This returns 4 levels. I'm wanting to add up the number of siblings under the level 2 instead of listing them all.
I have one table that have many records. For the maintenance purpose I like to delete old record based on Customer No.-That is Mobile NO.If each Customer have more than 300 records, I like to delete by everyday batch process.can't figure out how to apply each Customer No.(Specific Column), I could sort (order by few column - SAVE_DT or SMS_ARV_CLC) how to write this kind of query? I try rownum but no more progress.
Here is my table CREATE TABLE TM_060_SMS_TEST ( SMS_SEQ VARCHAR2(18 BYTE), SMS_RCV_CLC VARCHAR2(14 BYTE), CUST_NUM VARCHAR2(12 BYTE),