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
View 4 Replies
ADVERTISEMENT
Dec 8, 2005
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.
View 6 Replies
View Related
Jun 11, 2013
I have a small doubt on CLOB reading and not able to do it in the query because it has very large strings in it.
create table TEST
(
col1 VARCHAR2(100),
col2 VARCHAR2(100),
col3 VARCHAR2(100),
col4 DATE,
col5 CLOB,
col6 BLOB,
[code]....
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
[code]....
View 19 Replies
View Related
Jun 13, 2010
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.
View 8 Replies
View Related
Apr 7, 2011
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
View 14 Replies
View Related
Jul 31, 2013
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),-->
How to achieve this by SQL query.
View 3 Replies
View Related
Nov 27, 2010
i have a single form three database field
i want when i open a form in exceute mode then only a single field it does not show any data from database
means query not allowed in that particular field
how it is posible
View 1 Replies
View Related
Mar 8, 2010
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.
View 2 Replies
View Related
Dec 6, 2011
I have a table:
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
View 1 Replies
View Related
Mar 17, 2010
i have a doubt in building a query.
I have a table with fields
job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010
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.
View 12 Replies
View Related
Nov 26, 2012
We are using APEX 4.2, 10GR2, RedHat5.
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.
View 4 Replies
View Related
Oct 12, 2012
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.
View 5 Replies
View Related
Feb 5, 2010
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 .
View 6 Replies
View Related
Sep 22, 2012
I have a form that based on a table,
Col1: text field
Col2: text field with Disabled =Yes and Save session state = Yes
I create a dynamic action on Col1 to set value for Col2, when I submit page an error raised: Session state protection violation
visit: [URL]...
My purpose is: Col2 is set value by Col1 via Dynamic action, and is read-only, user cannot modify.
View 7 Replies
View Related
Jan 9, 2012
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.
jobname varchar2(100)
controldate (date)
controlflag varchar2(1)
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]....
View 5 Replies
View Related
Apr 21, 2010
I have the following case to solve:
Example Table:
Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00
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:
Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00
View 8 Replies
View Related
Dec 5, 2012
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.
View 1 Replies
View Related
Apr 26, 2013
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
XML File
<e_id>
143
876
So, I want the output to be:
e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100
View 2 Replies
View Related
Aug 7, 2009
I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
View 8 Replies
View Related
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)
Now i have to join the next table C with table B
Table C structure is
ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer
I have the common column such as ColD2 and colB4 to get the Col D3
how do i join the existing query + join between table b and table c?
View 4 Replies
View Related
Jul 17, 2011
how to achieve F11(Query mode) and Execute Query in Oracle Forms?
View 1 Replies
View Related
Apr 6, 2010
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.
View 10 Replies
View Related
Mar 10, 2012
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
emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333
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')
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333
Or similarly with Top N concept:
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
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
Or really what I want in my export:
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.
View 3 Replies
View Related
Sep 18, 2012
This query is written in inner join, can any one try to write using sub query.
SELECT B.CNO
FROM CUSTEN A
INNER JOIN ORDS B
ON A.CNO = B.CNO
AND A.PRNO = B.PRNO
[Code]...
View 4 Replies
View Related
May 24, 2010
I have the folloiwng two queries:
Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;
I need to compute the following:
var:=(yy/zz)*100
How can I achieve this in a single query?
View 3 Replies
View Related
Apr 23, 2010
Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.
View 13 Replies
View Related
May 3, 2013
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.
--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)
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]......
View 5 Replies
View Related
May 9, 2013
I am New to Apex. Im using Apex Version 4.2.
I have a tab named APPROPRIATIONS it has 10 fields. Among which is PROJECT NUMBER, AUTHORIZAION DATE (CALENDER TYPE POPUP), And FUNDS CHECK FLAG.
The Scenario is depending upon PROJECT NUMBER the Fields are Populated including FUNDS CHECK FLAG .
But the AUTHORIZAION DATE is a MANDATORY field has to be filled by User
The Requirement is to make AUTHORIZAION DATE as MANDATORY only when the FUNDS CHECK FLAG is *"BLANK "* or *“Y”*
If the FUNDS CHECK FLAG is *“N”* then the AUTHORIZAION DATE to be made OPTIONAL.
View 2 Replies
View Related
Feb 23, 2012
I have a a table like with columns ( date_field, client_id(c_id), transaction_id(trx_id), mobile, amount )
table example data like
date_fieldc_idtrx_idmobileamount
24-JAN-1215100100120111111100100
24-JAN-1217100100220111111112150
24-JAN-1215100100320111111113100
24-JAN-1216100100420111111114200
24-JAN-1215100100520111111115100
24-JAN-1216100100620111111116100
24-JAN-1218100100720111111117100
24-JAN-1216100100820111111118100
24-JAN-1215100100920111111119200
24-JAN-1216100101020111111110100
24-JAN-1215100101120111111111100
24-JAN-1216100101220111111112100
24-JAN-1215100101320111111113100
Now using the unique index (Trx_id) I need to get max 3 records for each client (c_id).
Expecting result should be
date_fieldc_idtrx_idmobileamount
24-JAN-1217100100220111111112150
24-JAN-1218100100720111111117100
24-JAN-1216100100820111111118100
24-JAN-1216100101020111111110100
24-JAN-1216100101220111111112100
24-JAN-1215100100920111111119200
24-JAN-1215100101120111111111100
24-JAN-1215100101320111111113100
View 4 Replies
View Related
Apr 23, 2009
I need to update a field with the 1st 9 characters of another field in the same record.
View 1 Replies
View Related
