I have a select statement that selects all columns from the join of 3 oracle views. I would like to change it to select only the distinct rows, not sure how to code this. Here is my sql statement:
select *
from myschema.view_1 acct
Left JOIN myschema.view_2 freq
[Code].....
Having production system: 11.2.0.1 on Windows Server x64
Test system: 9.2.0.1 on Windows XP
Problem preface: to get all unique CASEID which should be checked up by biometric system.What i should check - all CASEs for different PERSONs having same PHONEs at least among one phone type (1..4).Real table contains little bit more than 10 million records.I made test scripts.
Below the DDL for test table creation:
------------------------------------------
-- Create CASEINFO test table
------------------------------------------
DROP TABLE CASEINFO;
CREATE TABLE CASEINFO
[code]...
Below i've put SQL/DLL to make test data.number of records inserted 2 millions.
PERSON_COUNT := #/8;
------------------------------------------
-- fill CASEINFO with sample data
------------------------------------------
DECLARE
I INTEGER;
[code]...
Below SQL select to check the data in created table.
------------------------------------------
-- Check test data counters
------------------------------------------
SELECT 'TOTAL',count(*) from CASEINFO
UNION ALL
SELECT 'LEGAL',count(*) from CASEINFO where
[code]...
The PROBLEM is that i am experiencing HUGE perfomance problems on both test and production systems with that query:
select distinct b.caseid
from CASEINFO a, CASEINFO b
where (a.person<>b.person) and (a.sex=b.sex) and
(
(a.phone1=b.phone1) or
(a.phone1=b.phone2) or
(a.phone1=b.phone3) or
[code]...
This query takes almost 90 minutes to execute.And i do not know how to avoid this.Full SQL file to make test attached.
nex lines showing SELECT DISTINCT return different rows when used with ORDER BY.
CREATE TABLE M1( ID_ NUMBER, A NUMBER, B NUMBER);
CREATE TABLE V1 ( A NUMBER, B2 NUMBER);
DELETE FROM M1;
DELETE FROM V1;
INSERT INTO M1 (ID_, A,B) VALUES (107, 5,1);
INSERT INTO M1 (ID_, A,B) VALUES (108, 11,1);
INSERT INTO M1 (ID_, A,B) VALUES (109, 17,1);
[code]....
I am little confused applying DISTINCT on a Multiple table Inner join.
Table: Role
=========================
role_id email
1 xxx@abc.com
2 yyy@abc.com
3 zzz@abc.com
Table: notification_role
===========================
id role_id process_id
1 1 p1
2 1 p2
3 1 p3
4 1 p1
5 2 p2
6 2 p2
7 2 p3
8 2 p3
9 3 p4
Table: process
================
process_id proces_name
p1 process1
p2 process2
p3 process3
p4 process4
Expected Result
====================
role.role_id role_email process_process_id process_name
1 xxx@abc.com p1 process1
1 xxx@abc.com p2 process2
1 xxx@abc.com p3 process3
2 yyy@abc.com p2 process2
2 yyy@abc.com p3 process3
3 zzz@abc.com p4 process4
QUERY::
select distinct c.process_id a.role_id,a.email_address,c.process_name
from role a, notification_role b, process c
where a.role_id=b.role_id and b.process_id = c.process_id
I think this is easier than I think but all I want to do is count the distinct rows in a table.
WITH data AS
(SELECT 1 id1, 1 id2, 111 val1
FROM dual
UNION ALL
SELECT 1, 2, 222
FROM dual
UNION ALL
[Code]..
seems to get what I want
ID1VAL1CNT1
111114
212224
321114
422224
but can the count be done in a stand alone query such as??
SELECT DISTINCT id1,
val1,
COUNT(*) over() cnt1,
COUNT(*) over(PARTITION BY id1) cnt2
FROM data
ORDER BY id1, val1
NOT what I want
ID1VAL1CNT1CNT2
1111152
2122252
3211153
4222253
i need a Select * from tablename just 1 Row for each distinct ActionMode column value
CREATE TABLE INSTRUCTIONAUDITLOGSS
(
TNUM NUMBER(10) PK NOT NULL,
BATCHTNUM NUMBER(10),
ENTRYTYPE VARCHAR2(8 BYTE),
USERGROUP VARCHAR2(8 BYTE),
USERID VARCHAR2(8 BYTE),
PRODUCT VARCHAR2(8 BYTE),
[code]...
in 10g report builder i have written this query
----------------------------------------------------
SELECT DISTINCT(A.TRANS_NUM)
, A.POST_CD
, A.TRANS_DT
, A.EFF_DT
, A.TRANS_TYPE
, ( SELECT DES FROM SMT_CODE_LIST WHERE CD = A.TRANS_TYPE) DTRANS_TYPE
, A.TIME_STAMP
[code]....
this query returns multiple row. how to get distinct row.
I have a table PRODUCT with following structure:
Productcode Productname Quantity
AF Ade Fgh 100
LO Ldo Ope 50
SK Ske Kro 47
....
There may be any number of records in the table. Now I need to print distinct products in different columns such as.
AF LO SK
100 50 47
There may be any number of products...I need to print all those products as different columns as shown above. How to write the Stored procedure for this?
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].....
I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.
SELECT DISTINCT a.emp_id, a.cal_id, TO_CHAR(a.ts_date, 'DD/MM/YYYY') tsdate, a.ts_date, 1 as days
FROM tmsh_timesheet a
INNER JOIN project b ON TO_CHAR(b.proj_id) = a.proj_id
INNER JOIN tmsh_ts_calendar c ON c.cal_id = a.cal_id
INNER JOIN (SELECT a.cal_id, a.emp_id, MAX(a.status) as status, a.create_dt, a.create_by FROM tmsh_stat_hist a
[Code]...
this query results
EMP_IDCAL_IDTSDATE
048283404/10/2010
048283502/11/2010
048283503/11/2010
048283504/11/2010
048283508/11/2010
i need the ts date to be in like this
04/10/2010
02/11/2010 - 04/11/2010
08/11/2010
I am only able to extract only 4000 characters from the clob column "DESCRIPTION".how to get more characters or max for that column with the same query concept?
select distinct
o.id "Organization ID",
en.entity_id "Contact ID",
en.entity_cd "Note Entity Code",
ed.entity_name "Note Entity",
en.entity_id "Note Entity_id",
[code]....
I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.
eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..
How to write a query for this inside a loop.
I want to list out all the views in a schema. What table can I query?
View 2 Replies View RelatedI have a set of rows based on a complex view from multiple table.
I will be updating some of its columns from front-end . Is there any possible ways to lock those rows of data while updating and no other users can update it;
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
5 rows selected.
I have a problem with views and nested selects which I cannot explain. Here is a trimed down version of the research I have done. notice the following:
1) all code is executed from the same user CDRNORMALCODE. this user has all views and procedural code
2) all data is owned by a different user CDRDATA. This user has no views and no code.
My problem is this:
If I reference the table directly with a delete statement that uses a nested select (i.e. IN clause with select), the index I expect and want is used.But if I execute the same delete but reference even the most simple of views (select * from <table>) instead of the table itself, then a full table scan is done of the table.
Here is an execute against the table directly (owned by cdrdata). Notice the reference to the table in the table schema on line 3. Also please notice INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1 at the bottom of the plan.
SQL> show user
USER is "CDRNORMALCODE"
SQL>
SQL> explain plan for
2 delete
[code]...
OK, here is an update. The views I am useing normally have instead of triggers on them. If I remove the instead of trigger the problem looks like it goes away, when I put the trigger back the problem comes back.But why would an instead-of-trigger change the query plan for a view?
SQL> DELETE FROM PLAN_TABLE;
5 rows deleted.
SQL> explain plan for
2 delete
3 from BSNSS_CLSS_MNR_CASE_RULE_SV
[code]...
i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work
View 1 Replies View RelatedThe view below creates, however displays duplicate rows. Why is this may I ask?
CREATE OR REPLACE VIEW customer_order_vw
AS
SELECT
a.customer_id,
[Code]....
select rl.org_rollup_skey from (select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486')) p
INNER JOIN IA.DIM_ORG_HIER h
ON p.fc_org_skey = h.desc_org_skey
inner join IA.FCT_FCST_SLS_ORG_ROLLUP rl
on h.GPRNT_ORG_SKEY = rl.org_rollup_skey
Above join is taking is running forever even as subquery
(select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486'))
returns no rows and this subquery give result in 10 seconds according to me Full query should not take more tha 20 secs.
Getting error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
create table try1
(id_number varchar2(10),
item1 varchar2(10),
item2 number(10),
item3 varchar2(10))
[code]....
Table's data:
ID_NUMBERITEM1ITEM2ITEM3ROWID
1asasas12dadasdaAAA9/BAAOAAA0JtAAA
22dadad231fsfsfAAA9/BAAOAAA0JtAAB
I need to join ISSUED_REMOVED Table with ITL Table. having each quantity each row.
Eg. If a unit Serial no '354879019900009' has a part (1015268) issued 8 times and then unissued 4 times so finally the part was issued 4 times. so I need 4 rows to show for each qty 1 for that part and unit serial number.
-- ITL Table
Create table ITL_TEST (
ITEM_SERIAL_NO, ITEM_BCN, ITEM_ID, ITEM_PART_NO, OPER_ID,
ISSUED_REMOVED_PARTNO, ISSUED_REMOVED_QUANTITY, QUANTITY, SHIPMENT_ID)
[code]....
-- Issued Removed table
create table ISSUED_REMOVED_ITEM
(REPAIRED_ITEM_ID, ISSUED_REMOVED_ITEM_ID, ISSUED_PART_ID, OPER_ID, ISSUED_REMOVED_QUANTITY)
as select
122013187, 1323938, 1015268, 308, 2 from dual union all select
122013187, 1323939, 1015269, 308, 2 from dual union all select
122013187, 1323940, 1015268, 308, 2 from dual union all select
[code]....
-- The way I need to join the Issued_Removed Table
select * from ITL_TEST ITL
left join
issued_removed_item iri
on iri.REPAIRED_ITEM_ID = ITL.ITEM_ID --ITL.ITEM_ID --rlsn2.item_id --126357561
and iri.oper_id = 308 --in ( 308, 309)
[code]....
We are doing select statements. I have 3 tables that I need to get information out of and I believe I need to use a join but everything I put into oracle gives me an error.I'm doing the selects for a pharmacy and have a customer table, a drug table, and a prescriptions table.
I need to write a select statement that shows what customers are taking what drugs and how many mgs they take
customer_id,
customer_first_name,
customer_last_name,
drug_id,
drug_name,
prescription_unit
i think i need that information for the select but I cant seem to write a select statement that runs without errors.
How Can I delete the returned two rows?
1 select s.reg_no,s.course_code,
2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag
3 from attendance a ,src s
4 where a.semester_code=1
5 and a.semester_year=2013
6 and s.semester_code=1
[code]....
I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:
delete taba1 t1
where t1.campo1 in ( select distinct(tr.campo1)
from tabla1 tr,
tabla2 t2
where t2.error = 0
tr.campo1 = t2.campo1
and tr.campo2 = t2.campo2
[Code]...
I am trying to make a select statement to join a few tables together. What i would like to know is if i can do this by saying the following.
I want to select indviduals that have a skill, but i want to say that if they have any of these skills to show the name so for example
SELECT S.NAME
FROM EMPLOYEE S ,PROJECT_TEAM T, SKILL_LIST L
WHERE T.PROJECT = 'Tesco' AND
L.SKILLNO = 'skill1' or 'skill2' or 'skill3';
When executing a SELECT with JOIN in oracle error as I am having the title. If I change the JOIN to where the procedure works normally. When installing my application, it works normally for a day or two, then it only shows the end of communication error. What I do is select the following:
SELECT /*+ FIRST_ROWS(10) */ TM1.EsqCodigo, TM1.EsqDescricao, TM1.EsqTipo, TM1.EsqConector, T2.ACnDes, TM1.EsqStatus, TM1.EsqVersaoNfeRecepcao, TM1.EsqVersaoArqNfeRecepcao, TM1.EsqVersaoArqNfe, TM1.EsqVersaoProcNfe, TM1.EsqVersaoNfeRetRecepcao, TM1.EsqVersaoArqNfeRetRecepcao, TM1.EsqVersaoNfeCancelamento, TM1.EsqVersaoArqNfeCancelamento, TM1.EsqVersaoNfeInutilizacao,
[code]...
is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?
in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.
Where filter middle_rows save before join and grop by operation?
It is save rows in PGA Private SQL Area or save blocks in SGA databuffer?
I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.
Materialized view is created by
CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';
I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.
I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.
View 3 Replies View Related