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.

View 2 Replies


ADVERTISEMENT

Record Count Mismatch Between Select And Insert

Mar 20, 2013

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.

View 3 Replies View Related

SQL & PL/SQL :: Record Of Last Executed Queries

Sep 15, 2010

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.

View 39 Replies View Related

SQL & PL/SQL :: Same Query Taking Different Time When Executed Through Different DBs?

Feb 19, 2013

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.

View 6 Replies View Related

SQL & PL/SQL :: How To Obtain Data Returned By Function When Executed Using Dynamic Query

May 25, 2011

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.

View 10 Replies View Related

SQL & PL/SQL :: Convert Update Query To Dynamic To Be Executed From Execute Immediate Statement

Oct 25, 2010

I want to convert the below SQL to a dynamic sql to be executed from execute immediate statement.

UPDATE transaction SET loannum = lpad(loannum,12,'0')

View 15 Replies View Related

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related

SQL & PL/SQL :: Inner Join Record Count?

Dec 24, 2012

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?

View 3 Replies View Related

Record Count Difference

Mar 19, 2013

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';

NUM_ROWS
----------
68323

View 4 Replies View Related

SQL & PL/SQL :: Count Number Of Record Based On Some Condition

Jun 28, 2013

i have two tables one as test_master and other one limit master.

test master table structure

ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure

limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --

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.

how to achieve the same using sql/oracle query.

View 7 Replies View Related

SQL & PL/SQL :: Bulk Insert Based On Record Count

Jun 19, 2012

Is there any defined record count range for the following ways of bulk insert :

INSERT INTO ABCTEMP SELECT * FROM DEFTEMP;

OR

through a cursor, bulk fetch and bulk insert under a loop.

View 6 Replies View Related

Forms :: Record Count - Applied And Saved?

Jan 27, 2010

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.

View 7 Replies View Related

SQL & PL/SQL :: How To Display The Status And Record Count In Separate Rows

Mar 6, 2013

My problem is i need to display the status and the record count in separate rows.How can i do it

Example

SELECT 'A' STAT, COUNT(A) CNT,'B' STAT, COUNT(B) CNT,'C' STAT, COUNT(C) CNT
FROM TAB1;

OUTPUT SHUD BE LIKE

STAT COUNT
A A_cnt
B B_CNT
C C_CNT

View 13 Replies View Related

SQL & PL/SQL :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

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:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies View Related

Reports & Discoverer :: Pick Latest Record Rank And Count?

Sep 1, 2010

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'

View 3 Replies View Related

Server Utilities :: Increasing Commit Record Count In Sql Loader?

Oct 11, 2011

I have created a sql loader control file to load a delimeter file in a table.

The file gets loaded into the table properly, however the commit is happening for every 64 records.

My aim is to rise the commit count by 1000 records.

View 1 Replies View Related

Application Express :: Display Custom Message For Report When Record Count Returned

Nov 8, 2012

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.

View 2 Replies View Related

How To Get COUNT On Query

Mar 4, 2009

I have this query and I want to get the COUNT:

SELECT first_np AS n_p FROM dvc
UNION
SELECT second_np AS n_p FROM dvc
UNION
SELECT n_p FROM dc;

This returns one column which is the n_p; how do I get the count of n_p?

View 2 Replies View Related

PL/SQL :: Insert Dataset Into Some Table?

Jul 9, 2013

Can we use result of with query_name ... to insert dataset into some table? I'm usung 11.2.0.3 

View 5 Replies View Related

Displaying Only Max Value Of The Count In Query

Jun 18, 2007

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

View 5 Replies View Related

SQL & PL/SQL :: How To Find The Count In The Query

Apr 16, 2010

from the below string i want to find the count of the "<-" using a single query.

string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"

If not the query, pointer on using SQL functions.

View 3 Replies View Related

SQL & PL/SQL :: Total Count Per Day Query?

May 31, 2013

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%')

AND direction = 'I'

ORDER BY calldate, calltime;

View 6 Replies View Related

SQL & PL/SQL :: Query For Grouping And Count?

Feb 24, 2010

TASK_ID, TASK_STATUS, TASK_OWNER
================================
00001 , OPEN , ABC
00002 , OPEN , XYZ
00003 , WIP , ABC
00004 , CLOSED , XYZ
00005 , WIP , XYZ
00006 , CLOSED , XYZ
00007 , OPEN , XYZ

Output Required
Owner , Open , WIP, Closed
ABC 1 1 0
XYZ 2 1 2

View 9 Replies View Related

SQL & PL/SQL :: Total Of Count Query

Apr 29, 2010

i need the total of the count query I am executing.My query is

SELECT COUNT(*) FROM po_headers WHERE
CREATION_DATE BETWEEN '01-MAR-2010' and '31-MAR-2010' GROUP BY VENDOR_ID

this gives output as
6
1
3
4

My objective is to find the total of distinct vendors for a given date range.how should I modify this query.

View 1 Replies View Related

Count And Group Query?

Apr 23, 2012

I have a report I created and I need to get a Total Count by Plan Code Description for Each State. I do this and get all my data:

SELECT
mv.R1_State,
mv.subscriber_id,
mv.plan_code,
pm.description,
mv.line_of_business,

[code]...

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

[code]....

View 1 Replies View Related

Oracle Dataset - How To Split Datetime

Jun 2, 2008

my problem is:

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 ;

View 4 Replies View Related

How To Count Siblings In A Hierarchical Query

Sep 24, 2012

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.

View 1 Replies View Related

SQL & PL/SQL :: Query To Display The Office And Count(*)?

Nov 1, 2012

I need a query to display the office and count(*)

where rownum > 6 i.e

here is the data

office
------
hq
hq
hq
hq
hq
hq1
hq1

[code]....

I need a query to display the top 3 counts and the 4 row should be sum of count of other offices.

my result should look like this

office count(*)
--------- --------
hq 5
hq1 4
hq2 3
other 10

View 4 Replies View Related

SQL & PL/SQL :: How To Get Count On Executing Dynamic Query

Sep 16, 2010

l_query := 'SELECT sedol ' ||
'FROM integration.tmp_attributed_sedol ' ||
'WHERE ' || p_field || '=''' || p_ref_number || ''' ';

by using the execute immediate or any other command, how can i check whether the query returned any rows or not?

View 2 Replies View Related

SQL & PL/SQL :: Delete After Count Oldest Row Query?

Mar 22, 2011

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),

[code]...

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved