SQL & PL/SQL :: How To Insert Subquery Inside Main Query
Sep 6, 2012
how can i put my query inside my main query.
select *
from (select pa_request_id
,max(status) status
,max(approved_amount) approved_amount
,min(level_id) level_id
,max(req_amount) req_amount
from target_aggregation_attendee
group by pa_request_id)
where status = 'Approved')
My Main Query:
select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
Step# 2 manipulated the tables: Inserted 3 records in Table_C.
Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:
CREATE OR REPLACE TRIGGER TABLE_A_TR after INSERT OR UPDATE OR DELETE ON TABLE_A FOR EACH ROW DECLARE [code]......
Step# 3 compiled the created trigger and I've successfully compiled it. Step# 4 Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A. Insert into TABLE_A values (1,'testa','testb')
I've successfully insert the values into TABLE_A however I've observed that the trigger didn't execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there's an issue in my insert statement but I've successfully populated the values into TABLE_B. So I'm wondering why the trigger didn't execute the insert statement.
I read in a book that you can't use subquery in an insert statement . E.g:
1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.
can we place insert statement in loop inside anonymous block?
CREATE TABLE DEP(DEPTID NUMBER(5) NOT NULL PRIMARY KEY,DNAME VARCHAR2(10),LOCID VARCHAR2(10)); DECLARE I NUMBER(5); BEGIN I := 0; LOOP INSERT INTO DEP VALUES(&DEPTID,'&DNAME',&LOCID); I := I+1; EXIT WHEN I = 5; END LOOP; END;
Below is the block which i am trying to test in scott schema. I dont want to substute IN clause values directly. So i have written cursor and have added in separate variable separeated by comma.But its not working.
declares varchar2(1000);s1 varchar2(1000);v number := 0;v1 varchar2(2000) := 'SCOTT';j number := 0;cursor hhis select ename from emp;beginselect count(*) into v from emp; for i in hh loops := s||''''||i.ename||''''; j := j+1;if j <> vthen s := s||',';end if;s1 := s1||s;s := null; end loop;dbms_output.put_line(S1); case when v1 in (s1) then dbms_output.put_line('Y'); else dbms_output.put_line('N'); end case;end;
To display highest marks,least marks,average marks,total marks of the student name entered.
desc stud; Name Null? Type ----------------------------------------- -------- ---------------------------- SID NUMBER NAME VARCHAR2(20) M1 NUMBER M2 NUMBER
Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual; but i get the following error: ORA-14551: cannot perform a DML operation inside a query.
The intention of the code is to perform an insert into my table based on passing in values via an object into Stored Procedure Apply_Users_Update
Package Definition create or replace PACKAGE OTMP_TCIS_RS AS --1 PROCEDURE Get_UserInfo PROCEDURE Get_UserInfo( o_OutCode OUT INT, i_language IN VARCHAR2); FUNCTION Get_UserInfo( i_language IN VARCHAR2) RETURN NUMBER; [code]....
2. Table with the values for columns as given in table 1 ex:
col1, col2, col3, col4, col5, a , aa , 1 , x1 , p b , ab , 2 , x2 , q c , ac , 3 , x3 , r
I have to select values from table2.col1 do some processing and calculate values and store it in a table then do the same thing with col2 and so. This needs to be done for all the columns that appear in table1.For example in table 1 i have only three columns mentioned thus i have to process col1, col2 and col3 from table2. col4 and col5 will not be processed since they do not appear in the first table.
The problem is i have hundred columns in table 2 and the user can add up to hundred columns in table 1 as and when it is required.
I have created a cursor to first select column name from table 1 where variable is not null.For each value in cursor i put it in a local variable.
Second step is to select values from table2 where instead of column name i am using the local variable.But the problem is instead of choosing values from col1 the query returns the value as col1 (the value of local variable)
Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error
SQL Error : ORA-14551: cannot perform a DML operation inside a query
Below Is the
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS BEGIN INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID) VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1); RETURN 1;
SELECT rp.id r_paper_id, COUNT(*) created, COUNT( CASE WHEN a.approved_yn = 'Y'
[code]....
But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.
generates: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756 ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245
Unfortunately the Body is not accessible to see.The spec of the function is:
FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:
var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar
But this generates: ORA-01008: not all variables bound
Can I use CASE statement Inside a Oracle Report Query. I'm using Oracle Reports Builder 10g.
My Report Query looks like this,
select invh_code, invh_number, invh_date, invh_cm_code, im_description from invoice_head, invoice_det, unit_of_measurement, item_master where invd_invh_code = invh_code and im_code = invd_item_code AND (case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO) else 1 end) order by invh_number
:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition else omit that condition.
But the CASE statement is throwing Error
ORA-00907 :Missing Right Paranthesis (case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) ==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
I turned on ARCHIVELOG in my main database, anticipating GoldenGate replications and standing up a physical standby database. However, during the process I forgot to create a new SPFILE for my database, meaning that the next time the database cycled, the archive log destination would revert to the Oracle default (rather than the mount point I had set up to receive them) $ORACLE_BASE/fast_recovery_area
. I discovered this because my database hung..... In looking at the ALERT LOG, I discovered that the logs were going to fast_recovery_area, and it had run out of room. OK, so I did an alter system set db_recovery_file_dest_size= 5G scope=both; at which point in time I was notified that no active SPFILE existed! I moved out all of the logfiles that were in the fast_recovery_area, but the database seemed to still think that it was full.
I've managed to get everything fixed and put back together, but my remaining question is: After I freed up all sorts of space in the fast_recovery_area, why did/does the database still think that area is full?
I have 3 main tables as projects, tasks, clients. Then I have a history table for each. I created a trigger on update/delete that takes the old records and inserts into the history.
Now, I need to retrieve the history but I have no clue how to do so. I need to join these 3 history tables with:
1. select from projects_history with projectID = 1 2. if no records, then go back to projects and get projectID = 1
3. select from task_history with taskID = 1 4. if no records, then go back to tasks with taskID = 1
5. select from client_history with clientID = 1 6. if no records, then go back to client with clientID = 1
where client.taskID = task.taskId and task.projectID = project.projectID.
on clicking select.show,while running the form data(DATE and APPLICANT_NO) is being accesed from local database but after uploading the form the data is not coming from main database,even if the data is present in the main database.
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.
tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
getting missing comma with the above query.when i tried to give as
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
insert is happening but saving as "select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"
how to avoid this and get the select query to store as
select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij
I have test in the main section of the layout in forms. I want to print the footer on the first page only. If I go to properties and select print object on first page, it still prints the footer in all pages. How can I force the footer on the first page only.
i try to insert Concatenation string to my table,i need that all traps that has 12 length will be insert the new trapnum like this:
for example: 26001005CC45 = 260001005CC0045 ....... 08060027RF05 = 080600027RF0005 ......... and so....
update trap set TrapNum = ( select trim(both from to_char(substr(TrapNum,1,4),'0000'))|| trim(both from to_char(substr(TrapNum,5,1),'00'))|| trim(both from to_char(substr(TrapNum,6,3),'000'))|| substr(TrapNum,9,2)|| trim(both from to_char(substr(TrapNum,11,2),'0000')) from Trap) where length(Trapnum)=12
i have generate a report using report wizard but i want to know how join main section and head section , what ever you do in report wizard impact in main section ,i have done some head section ,when i run report, head section run first and main section in next page .. how to join both sections in single page output..
I have a main procedure in oracle which invokes many procedures inside it. These internal procedures also calls functions and procedures inside it.This continues to many levels.
For ex: Proc A call c call d end............
[code]...
This loop goes on and on . I want to find the names of all procedures invoked at run time when main api is executed. Is it possible to find all of them using toad ? Is there any tool for doing this ?
When i run a script that does a select from a single table (table has 33521868 records)the query is executed in about .094 seconds. I use the exact same query to insert into a temporary table and the query takes 10 minutes and more.
What should I be doing to speed up this process. Also tried using hints and it does not speed up the insert.
We are trying insert records from a select query into temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
