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)
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
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
I've a table, with fields - KEY1, KEY2, EVENT_DATE, AMOUNT. I want to sum up the amounts for a given period in time (eg. sum up amount for last 10 weeks).
select KEY1, KEY2, sum(AMOUNT) K1K2SUM from table1 where EVENT_DATE>sysdate-70 group by KEY1, KEY2;
Now, I want to same sum over KEY1 only.
select KEY1, sum(AMOUNT) K1SUM from table1 where EVENT_DATE>sysdate-70 group by KEY1;
Now, I want to supplement the first table with an extra column having K1SUM.
select O1.KEY1, O2.KEY2, K1K2SUM, K1SUM from (select KEY1, KEY2, sum(AMOUNT) K1K2SUM from table1 where EVENT_DATE>sysdate-70 group by KEY1, KEY2) O1, (select KEY1, sum(AMOUNT) K1SUM from table1 where EVENT_DATE>sysdate-70 group by KEY1) O2 where O1.KEY1 = O2.KEY1;
Doing two scans of the table is a little costly for me. Is there a single pass solution? I got a partial answer using,
select KEY1,KEY2,sum(AMOUNT) SUMS from table1 where EVENT_DATE>sysdate-70 group by grouping sets (KEY1, (KEY1,KEY2));
I have a table DW_ORDER_CHANNEL and I need to know what are the other objects accessing this table. As i need to alter this table so the dependent objects get invalid. how to get the dependent object on this table?
I�m Using Oracle 11.I have a table with 16 million rows and an index (let's call it the employee table with an index on department). I need to select all the employees whose departments are located in the uk. I achieve this by selecting all the department numbers from departments where location = 'UK' in a sub select then plug this into the main query as follows:
SELECT * FROM employees WHERE department IN (SELECT department from departments where location = 'UK');
It takes ages, 25 seconds or more, the explain plan shows its doing a full table scan on emplyees. I need it to use the index. The sub query is instant and returns only 5 rows. If I explicitly put the 5 numbers in the IN clause the query uses the index and executes in 0.04 seconds. See below:
SELECT * FROM employees WHERE department IN (1,2,3,4,5);
I need it to use the subquery once and then use the index on the main table.
I have a table A on dev with definition as TAble A(address,name) and the same table on Prod is defined as Table A(name,address).
my question is Ihave one package in that am trying to insert into this table as follows:
INSERT INTO A SELECT b.name name, a.address address,
[Code]....
so the query works on Prod but fails on Dev because column order is different.
I have 2 solutions:
1. I can mention column names in insert line and modify the query but tomorro some body changes again the definition of table A I need to change the query, so do I have solution in oracle sql that can handle the column order without specifying the column names in insert line.
so tomorrow On prod column order and on Dev column order is different though my sql should successfully execute.
i need a SQL query which should return me relevant table names. i.e. if there is table 'EMP' , then query should give table names with below result:
EMP EMP_1 EMP_2 EMP_3 EMP_4
i.e. All tables which is starting with EMP (No Hardcoding of table, It should be dynamic way).I know we can achieve through SELECT * FROM USER_ OBJECTS WHERE OBJECT_NAME LIKE 'EMP%'. But here object_name i will passing dynamically.
select gam.SOL_ID,COUNT(gam.FORACID) from gam,smt where gam.ACID=smt.ACID and gam.ACID NOT IN(select ACID from imt) and gam.SCHM_TYPE in('SBA','CCA','CAA','ODA') and GAM.ACCT_CLS_FLG='N' and gam.SOL_ID IN(select SOL_ID from IMT) group by gam.SOL_ID /
attached is the explain plan.
in which index on IMT table is not used. And the query is doing a FTS on IMT table. What needs to be done to avoid FTS on IMT table.
How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?
select * from some_table st, other_table ot where st.id = ot.id
I google to find the Table Name and Column Name by having a value(Number/String). And my where clauses are
where owner NOT IN ('SYS','SYSTEM') and data_type IN ('CHAR','VARCHAR2','NUMBER')
My query as follows
select a.owner, c.column_name, c.data_type, c.owner, c.table_namefrom dba_objects a, all_tab_cols c where a.owner NOT IN ('SYS','SYSTEM') and where c.owner NOT IN ('SYS','SYSTEM') and where c.data_type IN ('CHAR','VARCHAR2')order by a.owner
sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.
I'm trying to generate count of the number of entries in a table for each day.The problem is the date column is of datatype timestamp and looks like this "2006-12-30 18:42:03.0"
How would I generate a report of number of entries in the table for each date (I'm not intrested in the "time" only the "date" i.e YYYY-MM-DD)?
SELECT COUNT(*) FROM my_table_name WHERE my_date_column LIKE '2006-12-30%' GO
It returns zero rows ( and I kno there are rows in the table) I'm using Oracle 10g.
