And I just need to display only the T's that would meet the condition Col2 in (15,18) and Col3 in (20,19).
I try with a condition where col2 in (15,18) and col3 in (20,19) and that works, but I don't need to display T1, because T1 has a Col2=5 and Col3=20, I just have to display T2 that just satisfies all my conditions, Col2 in (15,18) and Col3 in (20,19). It's something like a vertical search.
Tables same column names but diffrenet index structures and traget one to be partitioned hence only want to import the content Each table on source datbaase hascolumn seq number and only want to extract the last few months of data.
TABLES:table1,table2... DUMPFILE=dump_dir CONTENT=data_only QUERY= table1:"WHERE seq_num >100 "want to use expdp but not sure about how to ensure all tables have the WHERE seq_num >100 condition, if leave table1: out and just have QUERY= "WHERE seq_num >100 " will this condition be applied to all tables which is what we want.
I'm assuming also can use impdp CONTENT=data_only?
EMPNO ENAME DEPTNO SAL ---------- ---------- ---------- ---------- 1 RN 10 10 3 C 20 40 4 A 10 20 5 B 20 100 6 D 10 11 7 S 30 300 6 rows selected
Now I need to update the table as for all employees will get sal 100 for depetno=10 and employees of deptno=20 will get sal=200. I need to update this with a single query.
CREATE TABLE test1 (strt_num NUMBER , end_num NUMBER , des VARCHAR2(5), CONSTRAINT pk_strt_num PRIMARY KEY (strt_num) ); INSERT INTO test1 VALUES (5, 8, 'GC'); INSERT INTO test1 VALUES (10, 25, 'AB'); INSERT INTO test1 VALUES (12, 35, 'PC'); INSERT INTO test1 VALUES (22, 65, 'LJ');
SELECT * FROM test1
STRT_NUM END_NUM DES -------------- ------------ ------------- 5 8 GC 10 25 AB 12 35 PC 22 65 LJ
The requirement is the records should be split based on below conditions
1. Split only those records WHERE (end_num - strt_num) > 10 2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record 3. While splitting the records, -> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10 -> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10 And this should continue for all records except the last record -> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM
This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.
Expected Output.
STRT_NUM END_NUM DES ------------- -------- --------- 5 8 GC -- No chage, END_NUM - STRT_NUM < 10
I have made a travel booking system which comprises of 3 forms
1)Travel Booking form 2)Reservation Form 3)Cancellation Form
Under one booking number i can add multiple users in which they can have there multiple travels.
Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.
What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.
For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.
I have EMPLOYEE table that have 3 records with EMP_ID 1, 2, 3. Now I want to run below query
select emp_id from employee where emp_id in (1, 2, 3, 4, 5);
It will return only 3 records but i want those records also which is not available in employee table. Is this possible without using another table or creating another table. Actually I don't have enough privileges to create table.
& want output like below
EMP_ID 1 2 3 4 Not Found 5 Not Found
Here emp_id 4, 5 is not available in employee table, but query should return those value also with comments like "Not Found"
How to use outer join condition in my below query. In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more records in the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records.
I want all the records from APPS_JP.GEDIS_OFFER_HEADER goh including other conditions. I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result.
The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.
how can i define the following condition in query definitions tab to define query condition for the value which is linked from previous tab/report ,:P2_SR_ BUSINESS_UNIT is the Item which are getting value from another report Value.
BUSINESS_UNIT=DECODE(:P2_SR_BUSINESS_UNIT,'BS&I','Business Sales And Information','WS','Whole Sale')
I am new in apex and havent had any chance to create a simple report.Version Application Express 4.1.1.00.23
when am trying to use nvl for one condition it is taking lot of time to execute but when am removing nvl function then the query executing in 2 min. condition is given below
I also have a query that queries a SQL Server database:
Select Agent, SUM([acd calls which have rung the agent])As CallsRung FROM Dashboard_stats Where Date = DATEADD(DAY,DATEDIFF(DAY,'20000101',GETDATE())-1,'20000101') Group by Agent
ID Status description Tracking ID 1 Strat Frog 1 2 Start Dog 2 3 Process Frog 1 4 Completed Dog 2 5 Start Rabbit 3 6 Error Frog 1 7 Stop Rabbit 3 8 Start Elephant 4 9 process Elephant 4 10 Start Human 5 11 Stop Human 5 12 Start Butterfly 6 13 completed Butterfly 6 14 start lion 7 15 error lion 8 16 complted lion 8 17 start tiger 9 18 error tiger 9
select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss') And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));
Result should be " Frog and Elephant and tiger"
Start of every record(descrpition --status is Start) End of every record ( status is stop or done or completed) status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )
I've tried for pivot query feature of Oracle 11g, but I'm trying for pivot result on multiple column.
Herewith I'm displaying my try on single column pivot query.
SQL> select * from 2 (select deptno,job,sal 3 --,comm 4 from emp) 5 pivot (sum(sal) as payment for job in('CLERK','SALESMAN','MANAGER')) 6 order by 1;
[code]....
I've tried this one also, but it didn't seems to be working.
SQL> select * from 2 (select deptno,job,sal,comm 3 from emp) 4 pivot (sum(sal) as payment_sal,sum(comm) as payment_comm for job in('CLERK','SALESMAN','MANAGER')) 5 order by 1;
I need to eliminate the blank spaces based on below conditions Consider name column with a value as
Input : "sa c h in Te nd ulka r" where "Sachin" is first name and "Tendulkar" is last name. there is more than 1 space between sachin and tendulkar (here its not displaying properly)
Condition :Second name is seperated from first name with more than 1 spaces and others are with 1 black space. I need to get result as Output:"sachin Tendulkar" ( there should be 1 blank space between first and last name in result.)
how does this query execute? what kind of a query is this called?
mysql> select ename,(select dname from dept where deptno=e.deptno ) as dname -> from emp e;
+--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.00 sec)
#select name from v$database; #select log_mode from v$database; #select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID'; #select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID'; #select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID'; #select count(*) "Broken Jobs" from dba_jobs where broken!='Y'; #select count(*) "Block Corruption" from v$database_block_corruption;
i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-
I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)
select order_number, (select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null) from hz_contact_points, hz_parties hz WHERE hz.party_id=hcp.owner_table_id) Email FROM oe_order_headers_all h WHERE h.order_number='102' .................... ..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT * FROM (SELECT Field1, ROW_NUMBER() OVER (ORDER BY field1 desc ) R FROM table WHERE date >= TO_DATE ('20110215', 'YYYYMMDD') AND date < TO_DATE ('20110216', 'YYYYMMDD') ) WHERE R = 1227 --Note: 1227 = (count(field1)/100)-1
I have been given a task to produce an ad-hoc report based on the following conditions (I will give you the structure of the table and details in the table below the requirements)
Requirements: Adhoc Report for Audit on CIT Income Allocation 1. Select from the RETURNS table: •Id > 3600000 and •Prog_program_cd = '01' and
[code]...
3.If there are multiple entries from the same account and tax year, only retain the latest record: From the records selected in step 2, if there are multiple records with the same Acct_Id and Period_end_date, only retain the record with the most recent Status_date (i.e. MAX value on the date).
4. Using the results from step 3, link to the applicable RETURN_LINE_ITEMS table where: •RETURNS.Id = RETURN_LINE_ITEMS.Rtrn_Id
5. From the selected return on RETURN_LINE_ITEMS table, retrieve records where (value on Sch 000 Line 062 > 500,000) and (value on Sch 000 Line 066 < value on Sch 000 Line 062) : •Sched_nbr = '000', and Litm_line_item_nbr = '062', and Active_ind = 'Y', get Revise_val_amt as 'ab_taxable_income' •Sched_nbr = '000', and Litm_line_item_nbr = '066', and Active_ind = 'Y', get Revise_val_amt as 'amt_taxable_in_ab'
[code]...
So when you run the scripts above, the tables are created (I have tested it in TOAD) Now let's feed data into both the tables
