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
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.
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]......
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.
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.
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
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.
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.
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.
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);
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??
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
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.
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 ..
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
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].....
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]....
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.
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]...
I have the following four tables with the following structures Table A
ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE
Table B
ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE
when these two tables were joined like the following:
Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)
Now i have to join the next table C with table B
Table C structure is
ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer
I have the common column such as ColD2 and colB4 to get the Col D3
how do i join the existing query + join between table b and table c?
joining the Order table with the Asset table or the Order table with CX_ACTION table to Can i get reference on this .
select x.x_msisdn,
x.x_product_name,
x.x_action,
x.x_status_outcome,
a.recovered_dt,
x.x_amount,
[code].....
problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.
we have found something called table_exist_action=append in impdp.
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies View RelatedIn our schema we have corresponding audit tables for most of the production tables
Ex Table name Audit Table
EMP EMP_AU
DEPT DEPT_AU
Audit tables will have all the columns of production table along with audit columns AUDIT_DATE , AUDIT_OPERATION There are few production tables which are not having audit tables.I need to write a script to identify
1) Production tables where corresponding audit table is missing
2) Where there is column difference (In case any column missing in audit table) between Production table and Audit table
In Oracle database pl/sql 11.2: describe the differences between :
records,
tables, and
tables of records.
I've got table tab1 with sequence id column.
Also I've got tables tab2 and tab3 with indicatorid column in each table.
My need is to prevent inserting values into indicatorid column in tables tab2 and tab3 which are absent in id column in table tab1.
For example, at this moment id column in table tab1 has two values, 1 and 2. I can insert value 3 into indicatorid column in tables tab2 and tab3. I need to prevent from inserting value 3 into indicatorid column in tables tab2 and tab3 while it is absent in id column of tab1 table.
How do I perform it?
I have the Table name Location and synonym named Location in my DB. I am trying to create the proc where I am tryting to call the table. But its not working.
Example:
CREATE PROCEDURE TESTPROC
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('testing');
select count(*) from LOCATION;
END;
Compile error:
Error(5,22): PL/SQL: ORA-00942: table or view does not exist
if the command is successful:>alter table my_table shrink;The segment will be defragmented and the High Water Mark will be moved.But what is the importance of the HWM?
Whats the difference between commands?
>alter table my_table shrink; -- move HWM
>alter table my_table shrink compact; -- not move HWV
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?
