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
oracle 10g to select column from about 8 table . I start with this statement
select A.a, B.b, C.c, D.d, E.e, F.f, G.g, H.h from A full outer join B on(A.a=B.b) full outer join C on(B.b=C.c) full outer join D on(C.c=D.d_ . . .. . . .
I have a table that contains 15 columns. Say each columns contains name of fruits. And another master table which contains names of valid fruits. Now I have to create another table from these tables such that for each column if the fruit name is present in the master table then fruit-name is populated else "invalid" is populated. I know using left join for 15 times to the same master table can work. But I found this very clumsy. Is there any other way of doing it.This is just a sample case. In real scenario I have a table of 800 million records and master table of 30000 records.
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
In normal sql statement: select sum(order_items.quantity*items.price) sales_price from order_items,items where order.item_id=items.item_id;
I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.
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.
select field1,field2,field3 from Table1 union select field1,field2,field3 from table2
In the query from table2 i am getting duplicate rows, HOW can i retrieve only distinct rows...Using distinct keyword did not work...if i have to post create and insert statements for this one...
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.
What I want to do is to return all of the rows from TABLE1 that are NCI regardless, and if they are NCI, I want to return the corresponding records from TABLE2 and TABLE3.
If TABLE1 has a record of NCI but there are no corresponding records in TABLE2 or TABLE3, then of course the columns for TABLE2 and 3 would be blank.
I can get all of the NCI records from TABLE1 when I LEFT JOIN with TABLE2, but when I try to specify TABLE3 in the FROM statement, only the records that are NCI in TABLE1 AND have data in TABLE2 are returned, not just all records with NCI in TABLE1.
Let me know if I can further clarify.
I know that you do not have access to my tables, but here is an example of my code so that you may understand my quandary further:
SELECT l.sku AS "SKU", l.loc AS "LOC", l.qty AS "QTY", o.ncikey AS "NCI", r.description AS "NCI DESC", o.qtyexpected AS "NCI QTY EXP", o.qtyreceived AS "NCI QTY REC", o.loc AS "NCI LOC", o.status
I am trying to insert records in multiple tables. I know how to view data using joinig, but unable to understand how to insert records in multiple tables using Joining. I searched it on net, but didn't find much. I have also tried to write a code, but it is not working, I have seen some examples on different websites where people are using SELECT in INSERT statement for joining. What is the correct Syntax to INSERT record in Multiple tables.
Insert into library_users, library_users_info (library_users.username, library_users.password, library_users_info.address, library_users_info.phone_no) VALUES (...)
I have 8 tables and I want full outer join on these to get the output. The tables are very small having 10 rows at max and consists of only two columns (date and value).
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.
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)
I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?
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
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;
I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production [code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.
I am having trouble retrieving the Max, latest date, from a table with a join to one another table and other fields from both.I was able to get the MAX service_date grouped by id. But once I tried to add more fields to the query and another table it won't work.
Here is what I have:
selectMAX(cs.service_date), cs.notes, cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd from cs join wr on cs.applicant_id=wr.applicant_id where wr.status_cd='AC' group by cs.applicant_id
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS: Policy id plan name 111 A Plan 111 B Plan 111 Z Plan 112 A Plan 112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3 111 A Plan B Plan Z PLan 112 A Plan Z PLan
SQL> desc RCA_SMART_CARD; Name Null? Type ----------------------------------------- -------- ---------------------------- N_CARD_ID NOT NULL NUMBER(10) C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20) C_SIM_MSISDN VARCHAR2(20) C_SIM_IMSI VARCHAR2(20) C_LINKED_CARD VARCHAR2(20) N_PRO_IDENTIFIER NOT NULL NUMBER(4) C_CARD_TYPE VARCHAR2(1) N_SIM_STATE NUMBER(1) N_EEPROM_SPACE_LEFT NUMBER(9) N_VOLATILE_SPACE_LEFT NUMBER(9) N_NONVOLATILE_SPACE_LEFT NUMBER(9) N_CARD_OPTI NOT NULL NUMBER(15) N_PRODUCT_ID NUMBER(10) D_CREATION_DATE DATE D_MODIFICATION_DATE DATE D_STATUS_MODIFICATION_DATE DATE
SQL> desc IMEI; Name Null? Type ----------------------------------------- -------- ---------------------------- MSISDN NOT NULL VARCHAR2(20) IMEI NOT NULL VARCHAR2(16) DATE_MOD NUMBER(13) IMSI VARCHAR2(18) ICCID VARCHAR2(20) T_PROF RAW(20) EXTRA_DATA VARCHAR2(100)
If I want to join two table together . I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .
two history tables with each record having effective date and end date needs to join (date is in dd/mm/yyyy)
table one
effdate enddate ID Name 01/08/2010 04/08/2010 01 devendra 04/08/2010 06/08/2010 01 deven
table two
effdate enddate ID Family 01/08/2010 02/08/2010 01 X 02/08/2010 03/08/2010 01 Y 03/08/2010 05/08/2010 01 Z 05/08/2010 06/08/2010 01 W
Expected output
effdate enddate ID Name Family 01/08/2010 02/08/2010 01 devendra X 02/08/2010 03/08/2010 01 devendra Y 03/08/2010 04/08/2010 01 devendra Z 04/08/2010 05/08/2010 01 deven Z 05/08/2010 06/08/2010 01 deven W
Below is the desc table : SQL> desc RCA_SMART_CARD; Name Null? Type ----------------------------------------- -------- ---------------------------- N_CARD_ID NOT NULL NUMBER(10) C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20) C_SIM_MSISDN VARCHAR2(20) C_SIM_IMSI VARCHAR2(20) C_LINKED_CARD VARCHAR2(20) N_PRO_IDENTIFIER NOT NULL NUMBER(4) C_CARD_TYPE VARCHAR2(1) N_SIM_STATE NUMBER(1) N_EEPROM_SPACE_LEFT NUMBER(9) N_VOLATILE_SPACE_LEFT NUMBER(9) N_NONVOLATILE_SPACE_LEFT NUMBER(9) N_CARD_OPTI NOT NULL NUMBER(15) N_PRODUCT_ID NUMBER(10) D_CREATION_DATE DATE D_MODIFICATION_DATE DATE D_STATUS_MODIFICATION_DATE DATE
SQL> desc IMEI; Name Null? Type ----------------------------------------- -------- ---------------------------- MSISDN NOT NULL VARCHAR2(20) IMEI NOT NULL VARCHAR2(16) DATE_MOD NUMBER(13) IMSI VARCHAR2(18) ICCID VARCHAR2(20) T_PROF RAW(20) EXTRA_DATA VARCHAR2(100)
If I want to join two table together. I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .
Explain the mechanism of this sql statement. I tried to understand how it goes but I really got confused.
select e.last_name, m.last_name mgr from employees e join employees m on (e.manager_id = m.employee_id)
The first line seems clear to me that will bring the names of employees and second column will bring names of managers. Second line is clear as well, but the third line couldn't understand. in my mind it should be like that
m.manager_id = e.employee_id
I tired this but doesn't work so what happened exactly in that full statement, I mean how it will match. I just want to understand the mechanism.
