SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?
Jun 19, 2012
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)
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.
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]......
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.
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 ============================================== SQL> select * from books_cat; ID CAT QRY ---------- -------------------- -------------------------------------------------- 141 Oracle {Oracle} OR {RDBMS} 142 Russia {Russia} OR {Russian} SQL> select id, file_desc from books;
[code]....
I am learning Oracle Text and created the above routine to check whether Russia/Russian appears in the OracleTextDeveloper.txt file (text version of the standard Oracle documentation - B28303-03).
Some more information: -
DBMS_METADATA.GET_DDL('INDEX','IDCTXR_BOOKS_CAT') -------------------------------------------------------------------------------- CREATE INDEX "READER"."IDCTXR_BOOKS_CAT" ON "READER"."BOO KS_CAT" ("QRY") INDEXTYPE IS "CTXSYS"."CTXRULE" SQL> select token_text, token_type from dr$idctxr_books_cat$i;
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?
Oracle VM 2 Node RAC cluster Oracle 11G 11.2.0.3.0 Oracle 11g rel 2 GI
on current 2 node cluster we have GI and RAC db configured Nodes: vmorarac1,vmorarac2
we shutdown vmorarac1 to clone it to vmorarac5
on new node I have changed the hostname to vmorarac5 In /etc/sysconfig/network-scripts/ifcfg-eth0 change ip to new ip and same for /etc/sysconfig/network-scripts/ifcfg-eth1 152.144.199.210,152.144.199.211
mad echanges to /etc/hosts on vmorarac1/2 for 2 new IP address assigned to vmorarac5
I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.
HERE
select a.* from DOC a , FOLDER B where a.NUMBER= B.INCIDENT_ID and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.
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??
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.
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;
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select 'date_on' to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal 023 | John | 6000 143 | Tom | 9000 876 | Chi | 4000 987 | Alen | 7800
I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.
For example:
select x,y,z from mytable@dblink
returns 788,324 rows
while select x,y,z from mytable
returns 712,102 rows
It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.
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;
Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?
Background:
We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.
The legacy HR system allows multiple phone numbers to be stored in a simple table structure:
SELECT emp_id, phone_type, phone_number FROM employee_phones
The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)
From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.
That can be represented as:
SELECT * FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')
SELECT * FROM (SELECT * FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1
SELECT phone_number FROM (SELECT phone_number FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1
phone_number ------------------- 1111111111
However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.
(SELECT phone_number FROM (SELECT phone_number FROM employee_people_phones WHERE emp_id = export.emp_id ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1)
1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?
2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?
Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.
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.
I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.
Query 1 :- select a.col1,a.col2,b.col3,sum(b.col4) from tab a, tab b where a.key1=b.key1 and a.key2=b.key2 group by a.col1,a.col2,b.col3
Query 2 :- select a.col1,a.col2,b.col3,sum(b.col6) from tab a, tab b where a.key1=b.key1 and a.key2=b.key2 group by a.col1,a.col2,b.col3,b.col5
How to use a table type variable with a regular query? For example, in my PL/SQL proc I have a table of employee names.
v_emp_tbl; --contains 'John','Sally','Ted'
Then I want to get names in a table called tbl_employees which are not in the pl/sql table. So if the table contains 'John','Sally','Ted',and 'Don' then I want to see 'Don'.
Not sure how to do this. Essentially I want something like:
Select emp_name into v_single_emp from tbl_employees where emp_name NOT IN v_emp_tbl;
I know I can loop through the pl/sql table but I wanted to see if there is some other way.
I have one table which has 90 columns all has varchar2 datatype except one Column[primary key (Number)]. In this Table we have 1000 records, I want to fetch those records from Table which has value in all 90 columns means there is no null value in any column.
I know simple method Like this :-
column_name1 IS NOT NULL AND Column_name2 IS NOT NULL.
Like this we can write IS NOT NULL condition for all column.Is there any other way to write this Query because it makes Query very longer and it is very tedious job to write this Condition for all Columns.
I have the following table structure...............
Main_Head table name
main_head_id ,pk head_desc, head_id , sub_head_id
keys col table ref col sub_head_id main_head head_id
the table is recursive table self join ----------------------------------------- now i want to write the query which return all head_desc which have same head_id
