SQL & PL/SQL :: How To Read Field From Query In Another Query
Sep 19, 2010
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
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.
This data I am inserting from front end into test table via XML but here i just giving insert statement.Now , I have below query to insert the data into different table:
This query is working for col3 ='2' as its small string but for col3 ='1' its not working and giving character string buffer too small.
with indata as
(select 1 sn, (select dbms_lob.substr(col5) from test where col3 = '1') x from dual union all select 2, null x from dual), t1 as
I need to display the parameter and status of DB for listener and Read Only.
I know those value could be get from command line , but could we get the values of Listener and Read only by SQL/PlSQL? So I can get it through the query of DB.
Following query is hanging either with 'Sequential access read' or 'Latch Free' wait event Important thing is the table which is self joined in subquery here does not have any index at all While it was hanged I tried to get trace of it and terminated twice. As such haven't got 'row source generataion' The table has only 120000 records and it shall update 34000 records
UPDATE invoice_header inv SET inv.modified_due_date = (SELECT inv1.btn_due_date FROM invoice_header inv1 WHERE inv.dct_code = inv1.dct_code AND inv1.release = 'A5')
[code]...
During 'sequential read' using p1,p2 values tried to get what the session is reading and found that it is using the table itself.
During lath free I found following SELECT name, 'Child '||child#, gets, misses, sleeps FROM v$latch_children WHERE addr= (select p1raw from v$session_wait where sid=18) UNION
[code]...
However instead of self join when I creaed global temporary table as
create global temporary table t as select * from invoice_header where release='A5'
And used it in the update as
UPDATE invoice_header inv SET inv.modified_due_date = (SELECT t.btn_due_date FROM t WHERE inv.dct_code = t.dct_code AND t.release = 'A5') WHERE inv.release = 'A5' AND inv.btn_due_date >= TRUNC (SYSDATE)
It updated the records in a second!!
Questions are 1) why it is producing 'sequential read' wait event when there is no index access or else why it is doing single block access when FTS is required? 2) Why is the 'latch free' wait event here and what it indicates here with 'cache buffer handles'? Is it because we are reading and updating the same segment?
know in case DDL of table is required. It has all nullable columns and no index at all. Since it is 9i I am unable to use MERGE effectively in this case
In employees table I am having employee_name column data as follows
Aaron, Mrs. Jamie (Jamie)Aaron, Mrs. Jenette (Jenette)Abbott, Ms. Rachel (Rachel) Breton, Mr. Jean Britz, Mrs. Sarie (Sarie) --> Now, I want to display the employee name like "Mrs. Jamie" (with out Surname and with out bracket included text),-->
What is the trigger which should be used to check certain field value after posting the query.
Example:
I have executed the query and the records are fetched. There is one field I want to check if it is null then it should be enabled, else, keep it disabled.
create table FIELDS ( FIELD_NAME VARCHAR2(30) not null, PRG_FIELD NUMBER not null, LENGTH NUMBER );
with
INSERT INTO FIELDS VALUES('FIELD1', 1, 3); INSERT INTO FIELDS VALUES('FIELD2', 2, 3); INSERT INTO FIELDS VALUES('FIELD3', 3, 4); INSERT INTO FIELDS VALUES('FIELD4', 4, 2); INSERT INTO FIELDS VALUES('FIELD5', 5, 1);
I need to insert in a table:
create table STUFF ( FIELD_NAME VARCHAR2(30) not null, FSTART NUMBER not null, LENGTH NUMBER );
And the output I want is:
INSERT INTO STUFF VALUES('FIELD1',0,3); INSERT INTO STUFF VALUES('FIELD2',3,3); INSERT INTO STUFF VALUES('FIELD3',6,4); INSERT INTO STUFF VALUES('FIELD4',10,2); INSERT INTO STUFF VALUES('FIELD5',12,1);
So each field starts where the previous (ordered by PRG_FIELD asc) ends.
I think the query should use both lag and connect by but I haven't had any luck writing it. The problem is that all the examples I've seen around, using connect by prior, utilize 2 fields with different names, es connect by prior emp_id = mgr_id. Instead I should do something like connect by prior prg_field = prg_field-1 but that doesn't seem to work.
PS: I don't necessarily need to do this, I have a guy manually writing the inserts, this is just an exercise I would like to figure out
If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
I have a report that comes from SQL Query (updateable report). I'm using the apex_item.text and apex_item.hidden on fields. I'm using a button to submit and after submit process to add some logic that I need.
There could be 1 - 10 records in the report. There is only 1 field that is needed to enter a value, but the value of this field determines the value of another field. I think that I can do this with a submit button and an after submit process where I loop through all the records. I think I have this handled.
This is the question
When the value of that field is changed then the value of another field in the same row changes immediately. All the examples I've seen so far are for a single record and that doesn't work for us.
I guess this is a MRU process but I haven't seen an example where a dynamic action is possible on a Multi Row Update.
I am having a problem with a popup lov. When I click on the "popup icon" I can select a supplier and it is stored in the field. But when I leave the form and return later, it shows the return value instead of the display value. For example: you select "supplier A" from the popup list with ID 12. "Supplier A" is shown in the text field, when you save the form, it stores ID 12 in it. But when you return to the form, it fetches ID 12 and shows "12" in the text field instead of "Supplier A".
I tried setting the "source" of the popup lov field as an SQL query, but that didn't work for me.
I was looking for application column name corresponding to "Draft Invoice Number" ra_interface_lines_all table.
I tried the below.
/* Get descriptive_flexfield_name for the application table name*/ SELECT * FROM FND_DESCRIPTIVE_FLEXS_VL WHERE application_id = 222 AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all');
/* Get the application column name and end user column name*/ SELECT * FROM FND_DESCR_FLEX_COLUMN_USAGES WHERE application_id = 222 AND descriptive_flexfield_name = 'RA_INTERFACE_LINES';
There are many DESCRIPTIVE_FLEX_CONTEXT_CODEs obtained. I could finally trace out that draft invoice number corresponds to INTERFACE_LINE_ATTRIBUTE2. How can I know what DESCRIPTIVE_FLEX_CONTEXT_CODE should I look for?
I want to build a single query to fetch the application column name and flex field name for a specific table .
The stand alone stored procedure has 2 parameter, an IN and OUT...
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER, tablename OUT VARCHAR2)
This procedure has multiple inner blocks. Intention is to control execution of each of the inner blocks basing on the value of flag field obtained from a "processlogtable". This processlog table has structure as below.
I have the below code snippet at the beginning of each inner block that checks value of the flag and then proceeds with execution of that block. The intent is to avoid redundant call of a block that has successfully executed first time. That is, once a specific inner block fails for some reason, the re-execution of the stored procedure should AVOID re-executing the PRIOR successed steps.
I've the code set up as below, but the prior successed block(s) code gets re-executed again once the procedure is re-executed after a failure.
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER,tablename OUT VARCHAR2) CURSOR c_missingtablename IS SELECT datatablename FROM ( SELECT UPPER(datatablename ) TABLE FROM WHERE datatableName IN ('Aaa','BbB','CcC'); [code]....
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
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 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 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.
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 have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST ( C VARCHAR2(2000 BYTE), B NUMBER, A NUMBER );
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP :M_END_YN := :M_END_ALL; [code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN :M_END_ALL := 'N'; END IF;
--code written on button to update those values which are checked.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP IF :M_END_YN = 'Y' THEN [code]......
