SQL & PL/SQL :: Display Ename / Contact_no / Location Joining The Two Tables?
Apr 13, 2010
i have two tables
create table tab1(
ename varchar2(10),
empid number(3) primary key);
SQL> insert into tab1 values('Ram', 101);
1 row created.
SQL> insert into tab1 values('Hari', 102);
1 row created.
SQL> insert into tab1 values('Shyam', 103);
[code]....
but i want like this
EMPID CONTACT_NO(PHONENO, MOBILENO)LOCATION
---------- -----------------------------------------
101 80123456 Banglore
101 9986234567 Banglore
102 809863728 Banglore
102 9032456578 Banglore
103 409863728 Hyderabad
[code]....
i want to display the ename,contact_no,location joining the two tables.
View 1 Replies
ADVERTISEMENT
May 19, 2011
can we link a table that contains a foreign key with a sequence table? i have a table that has a sequence bookid and i want to link this table to another table that contains a foreign key of bookid.can i link this two table and how?
View 1 Replies
View Related
Apr 26, 2013
I have a table (promo_custom) with following colimns (id, id_promo, button_promo, button_submit, css, user, date) and other table (promo_buttons) with following columns (id, id_design, name, url, username, date) .
Columns promo_custom.button_promo and promo_custom.button_submit are foreign keys (references promo_buttons.id).
I want to create a view showing table promo_custom but instead of columns button_promo and button_submit I would like to show the column url of them.
View 11 Replies
View Related
Apr 4, 2010
I tried to split the query into 2 parts , the outcome is fine but when ever i merge them ( include other table fields ) . The result is 3X3 rows .. it should be just 3 rows
The First Query ( Is running fine , 3 Rows returned )
SELECT
WORKORDER.WONUM,
WORKORDER.ESTSERVCOST,
WPM.ITEMNUM,
WPM.ITEMQTY,
[code]......
The Second Query ( Is running fine also, 3 Rows returned )
SELECT
WORKORDER.WONUM,
WORKORDER.ESTSERVCOST,
WP.DESCRIPTION,
WP.LINECOST
FROM WORKORDER , WPSERVICE WP
WHERE PARENT IS NULL
AND WORKORDER.WONUM = WP.WONUM
AND WORKORDER.WONUM = :WO
The problem occurs when i try to merge both these queries , as the result shows 9 rows ..
SELECT
WORKORDER.WONUM,
WORKORDER.ESTSERVCOST,
WPM.ITEMNUM,
WPM.ITEMQTY,
[code]......
View 3 Replies
View Related
Jan 16, 2013
We have two tables tab1 and tab2 same structures and empid and id are keys.
We need to query for records that doesnt exist in tab2 and exist in tab1 based on keys above and then insert into t2.And also we have a date column in tab1...
if we have two records..that match empid and id and if dates are different we have to take one record with the least date like min(Date) and insert that one record in to tab2.
View 11 Replies
View Related
Mar 24, 2012
I have a table called test_renames that has two columns(new_name and old_name) and one row with the following values
NEW_NAME OLD_NAME
DRUG10 DRUG1
I have another table called SH0 that has 10,000 rows and the column of interest is called ITEM1. ITEM 1 contains the following distinct drug names:
DRUG1,DRUG2,DRUG3,DRUG4,DRUG5
Each DRUG has multiple rows associated with it.
I need to only update rows for DRUG1 and set the name from DRUG1 to DRUG10 for all rows where ITEM1 is DRUG1.
However I need to write this update statement by joining with the values in the test_renames table that contains the NEW_NAME and the OLD_NAME.
DRUG1 has 2,000 rows associated with it. The following SQL statement gives me 2,000 rows
(SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG,sh0 sh
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
However when I run the following update statement, it appears that the SQL query updates all the 10,000 rows in the table instead of just touching the 2,000 rows for DRUG1. It does change all the rows for DRUG1 to DRUG10, but all the other ITEM1 values become null. What I need from the update statement is only chnage the rows for DRUG1 to DRUG10 and leave the other rows that I associated with DRUG2, DRUG3 etc. Here is my update statement.
UPDATE SH0 SH
SET ITEM1 = (SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
This update statement touches all 10,000 rows. I am looking to update only the relevant 2,000 rows for DRUG1.
View 2 Replies
View Related
May 20, 2010
consider the following
WITH table1 AS
(
SELECT 345 id, To_Date('1/31/2010', 'mm/dd/yyyy') dt, 123 cid, 'test' txt FROM dual UNION ALL
SELECT 345 id, To_Date('2/1/2010', 'mm/dd/yyyy') dt, 121 cid, 'test2' txt FROM dual UNION ALL
SELECT 345 id, To_Date('2/2/2010', 'mm/dd/yyyy') dt, 112 cid, 'test3' txt FROM dual UNION ALL
SELECT 345 id, To_Date('2/3/2010', 'mm/dd/yyyy') dt, 162 cid, 'test4' txt FROM dual UNION ALL
[code]..
i want to output the following:
{code}
IDDT CIDTXT
3451/31/2010123test6
3452/1/2010121test2
3452/2/2010112test3
[code]....
as you can see, i want to join both tables (table1 and ov_dates) and take dt from ov_dates table look it up in table1 and thengrap ov_dt and looking up in table1 and copy values of one row to the other.
for example, in ov_dates i have 3/31 dt and 4/7 ov dt. i will look for 3/31 and 4/7 row in table 1 and copy columns values from 4/7 to 3/31you can see that cid for 3/31 stay the same (since i dont want to replace the value) but txt value was replace with value from 4/7
another examplein ov_dates we have 1/31 dt and 2/5. so i go into table1 get 1/31 and 2/5 row and copy values from 2/5 into 1/31/ as you can see the txt column value for 1/31 becametest6 since it was taking from 2/5. the same thing for the rest ofdata
View 10 Replies
View Related
Nov 22, 2010
i am trying to left join a selection of two or more tables. what i have found, and solved part of my problem, is that oracle left joins only the last table in the select statement ...
i.e : select * from A, B left join C on C.id = A.id wouldn't work because left join applies to B and not A.
but as my queries grow i need to make something as follows :
select * from A, B
left join C on (C.ID_A = A.ID and C.ID_B = B.ID)
[... evantually more left joins as the preceding one may go here]
this query works for DB2 but Oracle claims that "A"."ID" is an invalid identifier, while the B.ID is recognized since it's the last table stated before the "LEFT JOIN" keyword.
View 7 Replies
View Related
Apr 8, 2012
I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.
Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.
CREATE TABLE DIVISION
(div_code NUMBER(5),
emp_num NUMBER(5),
div_name VARCHAR(25),
CONSTRAINT pk_division primary key (div_code));
[code].......
SELECT - this SELECT statement is supposed to generate the output of the CREATE VIEW CODE above. SEE Crows Foot diagram attached.
View 9 Replies
View Related
Oct 4, 2013
We have taken export expdp backup from prod database (primary database- Data Guard).
1.) Import impdp is very slow 10GB/Hrs on staging database (Data Guard MAXIMUM AVAILBILITY)Since Server configuration, database version and configuration, operating system everything are same as production. No blocking, locking or waiting sessions
2.)import impdp is fast 90GB/Hrs on Test standalone database and this test database is running in NOARCHIVE LOG mode with oracle standard version after that no more difference.
CPU,Memory,network and disk I/O are look normal while importing on both databases.why that much difference on import.
View 1 Replies
View Related
Mar 23, 2011
inserting data in the table.
We are getting more than 20,000 rows (example: ename) from java and they want to insert all the 20,000 enames together in a table having single column ename. For ex:
create table emp(ename varchar2(10));
Currently they are doing
insert into emp values('raj');
insert into emp values('raja');
.
.
.
so on for 20,000.
They are saying that it hits the performance as we are doing insert for 20,000.
Do we have any way where we can insert all the 20,000 ename in one go.
View 9 Replies
View Related
Feb 23, 2013
create or replace function akk(p_empno number) return varchar2
is
cursor c1 is
select ename from emp where empno=p_empno;
v varchar2(10);
[code]...
View 8 Replies
View Related
Sep 19, 2007
I've tried using Select * from ALL_TABLES but this does not return me the column info for each table.
How do I get an output of all tables in a database along with column (e.g datatypes etc)?
I'm using TOAD v8.6.1.
View 3 Replies
View Related
May 13, 2013
In SQL...I want to display the manager details and the corresponding employee details and i donot want to use joins or co-related subqueries
I am trying it to use CASE or DECODE..
Here is the Query..
SELECT *
FROM (SELECT empno,
ename,
sal,
job,
CASE
WHEN empno IN (SELECT mgr FROM emp)
[code].....
The output is as follows..
7839KING5000PRESIDENT7839 KING Manager 5000 0 10
7698BLAKE2850MANAGER 7698 BLAKE 7839 2850 0 30
7782CLARK2450MANAGER 7782 CLARK 7839 2450 0 10
7566JONES2975MANAGER 7566 JONES 7839 2975 0 20
7902FORD3000ANALYST 7902 FORD 7566 3000 0 20
7788SCOTT3000ANALYST 7788 SCOTT 7566 3000 0 20
I am able to display only the Manager details or employee details and not both at the same time..
View 13 Replies
View Related
Feb 28, 2008
I want to display all records from table 1 (even the null values) that do not match records in Table 2. Below I am creating both tables and I am posting the result query I need.
------------------------------------
CREATE TABLE temp_table1
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);
CREATE TABLE temp_table2
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);
[code].....
The result query need to have 5 rows as shown below
NAME LAST_NAME STATE BIRTH_DATE
------------ ------------ ----- ---------------------
john smith MA 12/1/1979
null null AZ null
null null CT null
null null MA null
null null CT null
View 5 Replies
View Related
Mar 5, 2010
i'm trying to display the hierarchical relationship between the tables (parents-child-subchild).
[b]table structure[/b]
DEPT
|PK-DEPT_ID
|
EMP
|pk-EMP_ID
|fK emp_DEPT_fK((FK column is dept_id references dept table dept_id column))
[code]....
but by using below query i am not getting complete heirarichy.
SELECT LEVEL,
Table_Name,
Constraint_Name,
R_Constraint_Name ,
SYS_CONNECT_BY_PATH(Table_Name, '/') Path
[code]....
View 2 Replies
View Related
Apr 30, 2010
I am stuck somewhere in my project, situation is that we are trying to display data gathered from 3 different tables, we used cursors for this, here is the cursor declaration.
DECLARE
procedure DocInfoCur(myWhere IN varchar2) IS
init_dept_id number;
init_dept_name varchar2(40);
rcvg_dept_id number;
rcvg_dept_name varchar2(40);
CURSORCUR_DOCINFO IS
SELECT DISTINCT I.CTN, I.DIARYNUMBER, I.INITIATINGDATE, T.TYPENAME, I.INITIATINGDEPT,
I.DOCUMENTSUBJECT, I.DESTINATIONDEPT
FROM DOCUMENTINFO I, DOCUMENTTYPE T, DEPARTMENT D
[code]...
This displays all the records out there.
Now, I have a search form where I need to filter the same data with different parameters, I have compiled the entered data in one string, now i need to join that string at the end of where clause of my cursor, how can I do that, i have tried concatenating both but received compile error, is there a way out in the current scenario???
View 1 Replies
View Related
Mar 27, 2013
want to display records of two tables in in one form.I have 2 tables. tab1 and tab2
columns in tab1 are code, desc, part, onhand_qty AND
columns in tab2 are code, room_no, row_no, rack, shelf, qty, remarks.
Common column in both the tables is code.
i want to display all the columns of both the tables where tab1.code=tab2.code ,and tab1.onhand_qty is not equal to SUM of tab2.qty
View 1 Replies
View Related
Aug 13, 2013
what is difference between "ocrconfig -export location" and "ocrconfig -manualbackup location"
View 1 Replies
View Related
Aug 11, 2013
/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */
CREATE PROCEDURE p_get_name (
p_empno IN OUT NUMBER,
p_name OUT VARCHAR2,
p_err OUT NUMBER
[code].......
Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.
View 25 Replies
View Related
Oct 14, 2013
Shouldn't they produce the same output?
SELECT c1.c_no, c1.c_name, c1.city
FROM customers c1 LEFT OUTER JOIN customers c2 ON (c1.city = c2.city AND c1.c_name<>c2.c_name); SELECT c1.c_no, c1.c_name, c1.city
FROM customers c1 RIGHT OUTER JOIN customers c2 ON (c1.city = c2.city AND c1.c_name<>c2.c_name);
View 8 Replies
View Related
Jan 30, 2007
We have a table for reports. If user A submits a report ...and say the sequence # is 242. When this report goes to Admin ...he submits this request then in same table we add another row with say sequence # 245. THEN we update column called 'Asctd ID' for 242 and add 245 in there. and then update Asctd ID for 245 and add 242 in there.
(This table has many fields, one of which is report Name field)
Now i am running a query like this...
SELECT b.JOB_ID, a.DESC, TO_CHAR(a.CREATE_DATE,'MM/DD/YYYY'),
DECODE(a.DLVRY_TYPE,'','PDF',a.DLVRY_TYPE), DECODE(a.FLG,'','N/A',
a.FLG), ((TO_DATE(a.CREATE_DATE,'DD-MON-YY')) + 21) -
TO_DATE(SYSDATE, 'DD-MON-YY'), c.STATUS_DESC, a.SIZE_NUM,
b.PRVDR_ID, a.asctd_id, d.NM
FROM REQUEST_DIM a, PROVIDER_DIM b, STATUS_DIM c, DIM d
WHERE a.FLAG = 'P' and RTRIM(a.RPT_RQST_USER_ID) = 'TEST02' AND a.RPT_RQST_TYPE = 'D'
AND a.RPT_RQST_ACTIVE_IND = 'A' and a.asctd_id is not null and a.RPT_RQST_ID = b.RPT_RQST_ID
and b.JOB_ID = c.JOB_ID and b.PRVDR_ID = d.PRVDR_ID
AND c.CREATE_DT = (SELECT MAX(d.CREATE_DT)
FROM STATUS_DIM d
WHERE d.RPT_RQST_ID = b.RPT_RQST_ID and d.JOB_ID = b.JOB_ID )
ORDER BY a.RPT_RQST_ID, a.CREATE_DATE
Now this query is run by the admin (job 245) ...it returns a bunch of stuff and also the name of the report that the admin gave this. But when admin sees this we want to be able to displace the report name that user A gave it (asctd ID 242). so the row 245 HAS a asctd ID 242. and there is a row 242 from which we can get the name easily. But i dont know if this is possible in ONE QUERY??
View 2 Replies
View Related
Jan 7, 2013
Basically, i was asked to join Table 1 and Table 2 together in oracle. I have been given the values of Table 1. For example, Depot_ID has value of DEP01, DEP02, DEP02 etc. Therefore, I have to create table 2 with those attributes given below. But the attribute name of the Table 2 is Depot_No which is different.
My question: Is it correct to give the same values of Depot_ID in Table 1 to Depot_No in Table 2, such as, DEP01, DEP02, DEP02?
How to join those two tables below:
Table 1:
Depot_ID
Address
Location
Table 2:
Product_ID
Depot_No
Quantity
Aisle
Bay
View 2 Replies
View Related
Sep 21, 2011
I have two tables
Tab1 columns c1,c2,c3 data as
t1r1c1,t1r1c2,t1r1c3
t1r2c1,t1r2c2,t1r2c3
t1r3c1,t1r3c2,t1r3c3
Tab2 columns c1,c2,c4 data as
t1r1c1,t1r1c2,t2r1c3
i want to get results like below
t1r1c1,t1r1c2,t1r1c3_t1r2c3_t1r3c3
join columns will be c1 and c2 columns.
View 2 Replies
View Related
Mar 29, 2010
I create a second "DATE_PAIRS" table, and join a few subqueries. It works with conditions (ID=1), but scanning the whole table (2+ million records) is abysmal.
Table:
ID Date Price
---------------------
1 2010/1/1 100
1 2010/1/2 125
1 2010/1/5 115
2 2010/1/1 115
2 2010/1/2 105
2 2010/1/5 125
I'd like to return
ID Date1 Date2 Price1 Price2
--------------------------------
1 2010/1/1 2010/1/2 100 125
1 2010/1/2 2010/1/5 125 115
etc ..
View 11 Replies
View Related
Oct 16, 2010
I have two tables A and B
CREATE TABLE A(EMP_ID NUMBER, EMP_NAME VARCHAR2(100))
CREATE TABLE B(EMP_ID NUMBER, EMP_ATT1 VARCHAR2(10), EMP_ATT2 VARCHAR2(10))
INSERT INTO A VALUES(1, 'ONE');
INSERT INTO A VALUES(2, 'TWO');
INSERT INTO A VALUES(3, 'THREE');
[Code]....
This query returns all the matching row of A and B
SELECT A.EMP_ID, A.EMP_NAME, B.EMP_ATT1, B.EMP_ATT2
FROM A
INNER JOIN B ON A.EMP_ID=B.EMP_ID
The output for this shows:
EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
2 TWO 2ATT1.1 2ATT2.1
3 THREE 3ATT1 3ATT2
The requirement is to avoid duplicate rows even if matched:
EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
3 THREE 3ATT1 3ATT2
View 8 Replies
View Related
Feb 8, 2011
I have two tables. The first contains all Segment information within Oracle i.e;
Table 1
Segment Description Flex_Value
1 North 1234
1 South 1235
1 East 1236
2 Car C001
2 Boat B001
and the second table contains financial data, but only the segment code;
Table 2
Date Segment1 Segment2 Value
01/01/11 1234 C001 10,000
02/01/11 1235 C001 10,000
what I want to return is some of the columns within table two as well as additional columns for the segment descriptions.
Below is an extract of what I have put together, and it does return what I need, but I'm sure there is a much more efficient way of creating the query;
SELECT
b.DATE,
b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,
[code].....
View 6 Replies
View Related
Jan 20, 2011
joining this query instead of using the left join. Reason is want to show the score column in a different place and also do not want to show the second IPS column that is used in the joined query.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
select * from
(
select i.ips,
p.project_name,
p.project_segment,p.location,p.project_exec_model,
p.project_exec_model||' - '||p.project_config pmodel,
one.score schedule,two.score cost,three.score execution,four.score commercial,
nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0) as total,
[code]....
View 6 Replies
View Related
Apr 10, 2013
I am new to writing queries for an oracle database and I was giving a bit of challenge. Here's the situation. I have 3 tables I am using. 2 of the tables are being used to transpose people's names from rows to columns by account number (there are multiple people associated with each account). The last table is a pretty straight forward query. I can run each query by itself and I get the results I want. But when I try to compile the two together, I start getting a variety of errors. Below is the two queries:
Query 1 (returns about 1,500 rows):
SELECT DISTINCT CAST (EIS_DW.ACCTCOMMONLOAN.ACCOUNT as VARCHAR(20)) as ACCOUNT_NUM,
EIS_DW.ACCTCOMMONLOAN.ACCOUNT_STATUS,
EIS_DW.ACCTCOMMONLOAN.MINOR_DESCRIPTION,
EIS_DW.ACCTCOMMONLOAN.OWNER_NAME,
[code]......
Query 2 (returns about 570 rows):
SELECT ACCTNBR,
max(DECODE (rn , 1, FULLNAME)) GUARANTOR_1,
max(DECODE (rn , 2, FULLNAME)) GUARANTOR_2,
max(DECODE (rn , 3, FULLNAME)) GUARANTOR_3,
max(DECODE (rn , 4, FULLNAME)) GUARANTOR_4,
[code]....
Ideally, I want to join these two queries on ACCOUNT and ACCTNBR. I have tried working my first query into my second query, but the best I get with that, is the 570 or so accounts, not all the accounts.
View 4 Replies
View Related
Jul 31, 2012
based on a provided list of key values, joining the collected list against a source table to retrieve additional information related to the key. In this simple example, the procedure accepts a list of employee numbers. The goal is to print a list of names associated with those numbers.
The method is to materialize the list of employee numbers as rows and join those rows to a source table to get the names. I have used BULK COLLECT. Why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:
SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );
here is my demo SQL, which you should be able to execute against the SCOTT schema without any change
declare
type employee_numbers is table of emp.empno%type index by binary_integer;
type employee_names is table of emp.ename%type index by binary_integer;
type employees_record is record (empno employee_numbers, person_name employee_names);
records employees_record;
[code]...
View 12 Replies
View Related
