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 RepliesWe 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.
View 6 Replies View RelatedI'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.
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')
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?
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 RelatedI 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
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.
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.
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 RelatedMy 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
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
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'
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.
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 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?
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 RelatedI' 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
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.
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;
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
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.
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]....
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 ;
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 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
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?
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]...