i work on oracle 8i with around 950 tables in my database. when i export or import it gives me the no of rows exported / imported from each table. is it possible to take a print out of the no of rows in each table through a single query .
select count(*) from each table takes a long time , since there are 950 tables.
difference in the values that are returned?
select count(*) from aaa;
COUNT(*)
----------
1000001
select num_rows from dba_tables where table_name = 'AAA';
NUM_ROWS
----------
994202
I have a table in which I can see via which communication channel we have previously communicated with our customers on different dates.
customer_interaction_log (I removed the date column)
customer_id channel
------------------------
1 SMS
1SMS
1SMS
1Email
1Email
2SMS
My goal is to have a de-normalized summary table in which total communication volume for each distinct channel is displayed per customer.Briefly, I would like to reach the following output
customer_interaction_summary
customer_id sms_countemail_count
-----------------------------------
132
210 (or null)
When I run the sql below I am not able to de-normalize by customer id; counts are accurate.
SELECT distinct cil.customer_id,
(CASE channel WHEN 'SMS'
THEN (SELECT COUNT (channel) FROM customer_interaction_log cil1
where cil1.channel='SMS' and cil1.customer_id=cil.customer_id) END) SMS_COUNT,
(CASE channel WHEN 'Email'
THEN (SELECT COUNT (channel) FROM customer_interaction_log cil2
where cil2.channel='Email' and cil2.customer_id=cil.customer_id) END) EMAIL_COUNT
FROM customer_interaction_log cil;
Output:
customer_id sms_countemail_count
-----------------------------------
1 3
21
1 2
I am trying to have one row per customer, however as you can see above it is not the case.
CREATE TABLE CUSTOMER_INTERACTION_LOG (
CUSTOMER_ID NUMBER(2),
CHANNEL VARCHAR2(10 BYTE)
);
Insert into customer_interaction_log (CUSTOMER_ID, CHANNEL) Values (1, 'SMS');
Insert into customer_interaction_log (CUSTOMER_ID, CHANNEL) Values (1, 'SMS');
[code].....
Consider the following tables
MANAGERID
101
102
103
104
MANAGERID EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27
write sql query to get the following output without using minus,union,intersect.
MANAGERID EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27
I am using the SQL-Developer to access and manipulate a database. I am not very sure about the format of the database (I'm new to databases), but I had to setup the TNS-folder.
Anyway, I guess the problem is the same for any database.
I am having a table with the BOM (bill of material) positions of certain articles and I want to change the BOM quantities of some of the articles. What happens is that I can only change some of the rows. For other rows I get the message like (it is in German, so I try to translate it):
"data was commited in another/the same session already. row cannot be updated"
This error message looks like there is somebody else locked on the database and manipulating it, correct? Is that possible to see somewhere which processes/people are currently accessing to the database?
I saw that there is one process/another database, which is having the authorization to access to the database. But where can I check if this process is accessing to the database?
BTW: I used to do this process before, and it worked. I had been able to manipulate arbitrary entries on the database. I guess that the process or the person, mentioned above, hasn't been accessing to the database at that time.
I have the task that I have to determine the number of parts that need to be produced based on the number of products sold for the day (each product consists of many parts).
I am using SQL 11g Express.
The report would look something like this:
{OrderDate PartID PartDesc NumOfParts(Total for that day)
10-24-2011 2001 12" X 12" Solid Shelf 108
10-24-2011 2003 12" X 24" Solid Shelf 32
10-24-2011 3001 96" Side Panel 50
[code].......
My issue is, I can't get the equation right to produce the total number of parts. I think I need to multiply ProductPart.NumOfParts by SUM(CustOrder.Qty) Group by CustOrder.SKU.
Below I have what the calculations should look like
SKU PartID Total Parts Needed by SKU
DVCK1212 1001 42 (7 orders * 6 shelves)
DVCK1812 1002 0 (NONE)
DVCK2412 1003 42 (7 orders * 6 shelves)
DVCK3012 1004 78 (13 orders * 6 shelves)
[code].......
Here's my script:
/**PRODUCT**/
CREATE table Product
(
SKU VARCHAR2(10) NOT NULL,
ProdDesc VARCHAR2(50) NOT NULL,
Price NUMBER(5,2),
PRIMARY KEY(SKU)
);
[code].......
Query 1
SELECT ProductPart.NumOfParts
FROM ProductPart,
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder
[code].......
Result: I am only getting a total of 2 parts
Query 2
SELECT ProductPart.Qty,
ProductPart.PartID,
ProductPart.PartDesc
(SELECT COUNT(CustOrder.SKU)
FROM CustOrder
GROUP BY CustomerOrder.SKU)TotalProducts,
[code].......
ORA-00936: missing expression
Query 3
SELECT Part.PartDesc.
ProductPart.NumOfParts
FROM ProductPart, Part
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder
[code].......
ORA-00933: SQL command not properly ended
Query 4
SELECT o.OrderDate AS "Date Ordered",
o.OrderDate + 5 AS "Date Due",
pp.PartID AS "Part No.",
pp.NumOfParts * COUNT(o.SKU) AS "Qty"
FROM CustOrder o
[code].......
ORA-00934: group function is not allowed here
We have two tables, TableA and TableB that contain list of accounts and balances.The requirement is to compare the balances of accounts in both the tables, and if there is a difference, then record that difference with account number in another table.
Both TableA and TableB contain more than 10 million rows.What is the best way to do this task in PL/SQL? A join on TableA and TableB to know the differences has become very slow due to large volume.
I have a table as shown below,
SQL> select * from Main_Table;
AA
--------------------
SUB1
SUB2
Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.
Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like
create or replace view sample
as
select * from SUB1 union all
select * from SUB2
How can this be achived. I tried as shown below:
spool file_to_exe.sql
select 'select * from ' || AA ||' union all ' from Main_table;
spool off
i will end up in a union all "extra" , if i do like this.
how can achieve this ?..
I have a table "exam results"
create table eresults
(student_name varchar2(20),
section_name varchar2(4),
exam_id NUMBER (4))
marks NUMBER (3))
[code]....
My requirement is that I need another column named "top scored" which will show how many times each student took highest marks for his Section in each exam_id"
For example in above data the following students "Top scored" for thier respective section in each exam_id:
STUDENT_NAME SECTION EXAM_ID
DOLLY A 1
RIZWAN B 1
PAUL C 2
ZAKIR D 2
[code]....
So, based on above my requirement is as below
STUDENT_N SECTION_NAME COUNT(EXAM_ID) SUM(MARKS) MAX(MARKS) top_scored
ALEENA C 2 147 91 0
ASIM D 2 68 45 1
ASLAM B 2 70 56 0
ATIF D 2 2 2 0
AYSHA B 2 114 78 0
[code]....
the following conundrum.
Having the Following information:
Col A Col B
---------------
134 | 1049
0 | 1050
12 | 1051
0 | 1052
0 | 1053
0 | 1054
0 | 1055
0 | 1056
0 | 1057
What I want is to count the number of the similar occurrences on Col A starting from the bottom and stopping at the first that is different.
Taking the example above I would get 6, that is the number of repeated "0" from the last value "1057" until "1052".
I have two tables one source table and one destination . Column names and data types of both table are same.
source table (source)
ID NAME
1 aa
2 bb
3 cc
. ...
. ..
. ..
destination Table(dest)
ID NAME
1 aa
2 bb
I need destination table like
destination table
ID NAME
1 aa
2 bb
3 cc
. .....
. ....
I want to remove repeated rows
Find the code below,
DECLARE
L_DE_TGUP_ID BBP_ALLOC.DATA_ELEMENT.DATA_ELEMENT_ID%TYPE;
L_ZERO_EXP EXCEPTION;
L_DATA NUMBER;
BEGIN
SELECT DATA_ELEMENT_ID INTO L_DE_TGUP_ID FROM BBP_ALLOC.DATA_ELEMENT WHERE DATA_ELEMENT_CD = 'TGUP';
SELECT DECODE(UPPER('0.0028'),'',0,'0',0,'NULL',0) INTO L_DATA FROM DUAL;
[code]......
Here the relationship between the source and target tables is one to one. but still i am getting "unable to get the stable set of rows from the source tables" error. Source query retrieves only 2 rows which are distinct. but still getting this error.
A block shouldn't have rows from multiple tables... Is that true? I read in one of the OTN thread (i don't exactly remember the thread name) that a block can have data from multiple tables. If it doesn't have, what's the table directory in block signifies?
View 9 Replies View RelatedI have a q, If i have:
table1(p_id, birth_date,lid)
table2(lid)
table3(s_id, birth_date, lid)
Now i would like to count from table 1 all p_id with birthday is the same as a s_id.Currently i am doing this:
select s_id, count(tbl1.pid)
inner join table 3
using (lid)
inner join table 1
using (lid)
Where to_char(tbl1.birth_date,'DD') = to_char(tbl2.birth_date,'DD') AND
to_char(tbl1.birth_date,'MM') = to_char(tbl2.birth_date,'MM')
when i check it using data, i get a target_id whose birth_day is same as TWO people in tbl1 yet according to that query i am only getting ONE person! rest of the results from that query look fine!
I have a table t
create table T
(player varchar2(40),
for_team varchar2(10),
Vs_team varchar2(10),
matchid number,
[code]...
Note: Each player can appear for more than one team in different matches. For exmaple, John appeared for Team A in matchid = 1 and then in matchid = 2 he appeared for team B. So same could be for other players
Requirment: I want for each player, the sum of total number of matches and points (which is easy using SUM) but along with total number of different teammates he played with in all the matches he appeared [for his team(s)] and also the total number opposition players and lastly the total number of different players he played with in all the matches he appeared in.
Just to clarify some terms, incase any doubt:
Here teammates = all players who appeared (for_team) in a match from the same team for whom the respective player also appeared in the same match.
opposition players = all players who appeared for VS_team played by each player.
total different players = all unique players who appeared for for_team or vs_team in the matches in which each player appeared.
Here is my desired outout:
Player total Matches Sum(points) Different teammates Different opposition players total different players
player played with player played with player played with
John 3 4 5 5 10
Fulton 2 11 3 4 6
Sarah 1 9 2 3 5
[code]..
I want one simple query and shortest query to achieve about output since in my actual table data is huge.
Have a query to find the occurence of a particular word in a string using a query.
ex: str := 'a,b,a,c,d'
search_str := 'a'
=> need to get the number of times 'a' is getting repeated in the string str.
output: 2
When i am running the below merge statement it gives the error as below
MERGE INTO non_tab tt
USING (SELECT non_tab.acct,
non_tab.mis,
non_tab.n_gaap_skey,
non_tab.run,
non_tab.mit_ul AS t0,
CASE
WHEN (( CASE
[code]...
ORA-30926: unable to get a stable set of rows in the source tables.
create a procedure or cursor to allocate extents to all tables with zero rows for all the user in the database.I have used the below query to check table with zero rows and no extents allocated.
select onwer,table_name,initial_extent
from dba_tables where initial_extent is null order by owner;
I generated the query to allocate extents by using concatenation in the above query.
select 'ALTER TABLE '||table_name|| ' ALLOCATE EXTENT; '
from dba_tables where initial_extent is null order by owner;
now I want the extent allocation for such table auutomatically for aal the tables with zero rows.
I am trying to insert rows in two tables using sql loader.
I have two tables in database as
SQL> desc name
Name Null? Type
---------------------- -------- ------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE
SQL> desc name3
Name Null? Type
--------------------- ----------- -------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE
I created controlfiles as
[oracle@DBTEST sqldri]$ cat datafile.ctl
options (direct=true)
load data
INFILE *
into table name truncate
when id='1'
[code]....
when i run sql loader as
[oracle@DBTEST sqldri]$ sqlldr hr/hr control=/u01/sqldri/datafile.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 2.
no rows is inserted..the log file contain entries as
[oracle@DBTEST sqldri]$ cat datafile.log
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: /u01/sqldri/datafile.ctl
Data File: /u01/sqldri/datafile.ctl
Bad File: /u01/sqldri/datafile.bad
[code]....
I want to compare two tables , and delete the common rows from the first table
Here is what i have done :
Create table test1(Test1C1 Number,
Test1C2 varchar2(50));
Create table test2(Test2C1 Number,
Test2C2 varchar2(50));
Insert into test1 values(1,'testdata1');
Insert into test1 values(2,'testdata2');
Insert into test1 values(3,'testdata3');
[code].......
it deletes all the records from Table test1. What should I modify here ? or should I write a different query ?
The desired contents in table test1 will be
2 testdata2
4 testdata4
6 testdata6
8 testdata8
10 testdata10
I am trying to write part of an SQL where it gives me a count of bookings in any 6 month period made from the first booking.Example of records
enquirynumberenquiryaddresssubjectcodebookingdate
613651 Burberry AvenueBCHR20/10/2008 07:00:00
613801 Burberry AvenueBCHR20/11/2008 07:00:00693021 Barberry AvenueBCHR07/09/2009 07:00:00
I am so far getting 3 as a count result based on SQL below. I want the count to return 2 (because its inside the 6 month range):
SELECT
ce1.enquirynumber,
ce1.enquiryaddress,
es1.subjectcode,
b1.bookingdate,
(SELECT count(b2.bookingdate)
[code]....
When I am executing merge statement I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
MERGE INTO TAN_LIST t
USING (SELECT * FROM (SELECT row_number () over (partition by TANNO order by null) rn,
dno,
TANNO,
SOL,
[Code]....
The query is fectching below data.
SELECT dno,TANNO,SOL,DESC,class,ct_dt,tcost
FROM MAT_LIST;
DNOTANNOSOLDESC CLASS CT_DT TCOST
63007565ADclass A A12345 08/28/131
[Code]...
Intially thers is no records in the TAN_LIST table. When I run the merge statemnt I am able to insert all 15 records into that table.
When I run the same merge statemment second time I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
So that I have used partition by cluase in the slect statement and I am able to resolve the error.
But it's inserting only 14 records not all 15. How to process all 15 records without the error..
I would like to write a query on USER_SOURCE that can display the number of code lines for each procedure/function in a package. Is it possible to write such a query? Maybe by using analytical functions?
for example in the following example i would like to count the lines between
"PROCEDURE proc1 IS" and "END proc1;" and between "PROCEDURE proc2 IS" and "END proc2;"
SQL> select text from user_source where name='PKG_TEST' and type='PACKAGE BODY';
TEXT
--------------------------------------------------
PACKAGE BODY PKG_TEST IS
/*************************************************
****/
PROCEDURE proc1 IS
BEGIN
update t1 set EDITION_NAME = 'AAAAAAA';
commit;
END proc1;
[code]....
I am trying to update a million rows in one table with the values from another tables.
Table being updated CI_ADJ_CHAR column CHAR_VAL_FK1
Table from which values will be used CK_ADJ columns (cx_id, ci_id)
The CI_ADJ_CHAR.CHAR_VAL_FK1 values match CK_ADJ.CX_ID and should be updated with the value CK_ADJ.CI_ID.
The CK_ADJ table has 1.3 million rows and both the columns have indexes defined. Table definitiuon mentioned below
The CI_ADJ_CHAR table has 14 million rows and will update 1 million rows and has an index on the ADJ_ID column but not on the CHAR_VAL_FK1 column.
CREATE TABLE FEB05
(NAME VARCHAR2(20),
CITY VARCHAR2(20))
INSERT INTO FEB05 VALUES ('AKON','CIT22')
INSERT INTO FEB05 VALUES ('MARIA','WOT101');
[code]......
I WANT A QUERY THAT WILL GIVE O/P AS TO GET DISTINCT COUNT OF NAME AND CITY COLUMN separately.
I.E. THE O/P SHOULD BE
Quote:Count(dist name) Count(dist city)
6 5
How the people in production counts the exact position of the data in fixed format of Sql*loader? Isn't it critical especially in a critical and having many column to be inserted.
View 6 Replies View RelatedCurrently I am working on conversion project. We want to verify the both applications updated database values are same or not.
if you run same transaction in both application values are updated in the database. I want compare both database tables, its updated same values in table or any mismatch in the table, is there any tools available right now to compare the two rows values in same tables.
I have one table in a particular database and will have to create another table in a different database. I will have to join these two tables from two different DB's.
How should I go about doing this? Is this a good practice? or is it always better to use a single DB?
What are the disadvantages of joining two tables from two different DB's?
I had face the problem regarding to database structure changes..
I had a old database which contain multiple column for 1 empno. which contain multiple subjects and its total marks.
Now the new structure is like all subjects and total marks as a row.
Eg. OLD data Structure
EMPNO SUNBJECT_CODE TOTAL_MARKS
1111 S1 45
1111 S2 21
1111 S3 55
1111 S4 41
NEW DATA STRUCTURE
EMPNO SUB1 MARK1 SUB2 MARK2 SUB3 MARK3 SUB4 MARK4
1111 S1 45 S2 21 S3 55 S4 41
I have more than 1.5 lakhs records in my old data structure..
Is there any Possibilities direct from Sql or i have to do it manually in excel sheet?
I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size
group by Condition, Size;
COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL