SQL & PL/SQL :: Write A Single Query Showing Number Of Requests
Jan 15, 2011
I need to write a single query showing the number of requests (job_no) in my organization sections (1A,3A,4C,9Z....etc) based on these conditions..Less than 2 months & between 2 and 6 & between 6 and 12month & more than 12 months in my organization sections.
--Table structure
SQL> desc job_transfer
Name
----------------------
JOB_NO (Request number)
IN_SECT (name of entered section)
IN_DATE (date entered the section)
OUT_SECT
OUT_DATE (date went out from section)
I tried using UNION ALL but it didnt work fine.
View 3 Replies
ADVERTISEMENT
Feb 13, 2012
In my main query, There is a single row sub query which returns row with del_flag as N. Now in my table, there are 2 rows getting added from the incoming feed with del_flag as Y & N.
Here is my Table:
ID_1 DEL_F ID_2
-------------------------------
16643162 Y 49696
16643162 N 16643162
16612344 Y 98888
So how can i write a single row sub-query which returns ID_2 value of a row with del_flag as N. If there is no row with DEL_F as N, it should return ID_2 value of row with DEL_F as Y.i have tried below query, but it showing the error.
select
(case
when min(del_f)='N' then to_number(ID_2)
when min(del_f)='Y' then to_number(ID_2)
end ) ID_2 from table where ID_1=?
Here, im passing ID_1 value from main query
View 8 Replies
View Related
Jun 6, 2012
I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:
SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS
If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS';
[code]....
However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....
View 7 Replies
View Related
Aug 3, 2011
How can I write a query to combine amount1 and amount1 into single column amount?
create table a (
id_code varchar(20),
invoice_date date,
invoice_type number,
interest number,
advance number,
invoice number
);
[code]....
View 7 Replies
View Related
Feb 21, 2013
i have a column
NUMBER(13,4)
when i insert 234.000
in show only 234
where as when i insert 234.12
i shows 234.12
is there any setting by which i can show 234.0000
View 13 Replies
View Related
Mar 13, 2012
Can I achieve the merge (update-only) and delete in a single SQL statement?
merge into table_4
using
(select table_3.col1 col1,table_3.col2 col2,
from table_1, table_2, table_3
where
[code].....
delete from table_3 where <records identified in the 'using' clause of above merge>;
i.e.
delete from table_3
where (table_3.col1,table_3.col2)
in ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 );
What I want is roughly as following :
WITH mywith as ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 )
update table_4 set col3='N' where table_4.col1=mywith.col1 and table_4.col2=mywith.col2
delete from table_3 where table_3.col1=mywith.col1 and table_3.col2=mywith.col2;
View 5 Replies
View Related
Jul 31, 2010
We are building a custom form extension with our EBS R12 where against certain parameters we are inserting and updating records in a table through a PL/SQL procedure.This PL/SQL doesn't use a commit exclusively, instead the user must click the Save button after verifying the data (new records are inserted, existing ones are updated and after the insert and update routines, execute_query is fired to fetch the latest data)
Everything works fine, however, when the user clicks on the Save button form status bar says "FRM-40401: No changes to save"
We would like to alter this message with our own custom message stating how many records were inserted and hows many were updated (We have counters counting this information)
Under which particular trigger we should write the code to enhance this requirement?
View 2 Replies
View Related
Oct 21, 2010
I have query in that query i should display the number of row. I have tried to select the row num but when i use it the data will be duplicated. I have found something that related to use temporary table, or subquery but all of them will not work because the query depends on many tables and if i use temporary table filling the table and then reading from it will take too long time.
View 11 Replies
View Related
Sep 13, 2013
how to write this in sql command lineDisplay CUSTOMER number, name, rep, balance, and credit limit for all CUSTOMERs of Rep 20.
In addition, display this information for CUSTOMERs of rep 65 who have a $10,000 credit limit. Arrange the output by credit limit descending within rep ascending. no more than two decimal digits
View 3 Replies
View Related
May 24, 2011
I've got function with combined sql statement (union all), how do I split it into two sql requests? This is to avoid time increasing of sql executing. IMHO good solution is to write two functions, how do you think?
FUNCTION f_LintOVRDUAH
(mydate date,
cid number )
[Code]....
View 18 Replies
View Related
Sep 13, 2010
I have employee, location, city tables, I have written following code to use joins
select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';
The same can be written by using non- correlated sub query as follow..
select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))
I need to implement the same concept using correlated sub query...
View 15 Replies
View Related
Nov 17, 2005
How to write column to row...in a SQL query?
For example..
SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40
The above output to be written in a single row like given below.
7369 20 10 40
1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other
2) It should be in SQL query only..Not in procedures or functions.
View 9 Replies
View Related
Jul 23, 2010
Without using any templates, i created a customized sample form....now i want to add data thru that form....i dont know where to write code and what is code if i want to add data?
View 9 Replies
View Related
Oct 15, 2012
I have table xx_xml_test m which have row single entry
<logentry
revision="3">
<author>MA111300</author>
<date>2012-10-03T12:42:40</date>
<paths>
<path
[code].......
i want convert the table like below
Revision author date kind action path
3 MA111300 2012-10-03 12:42:40 file A /root.txt
3 MA111300 2012-10-03 12:42:40 file A /sample2/test_2.txt
View 2 Replies
View Related
Sep 6, 2012
We have a requirement like, we will get bulk data (2 GB) data file with different languages(Russian, chinese, Japanese, German) etc. and we need to load these data into our databse also we have to translate the same.
We are using Oracle 11g.
We had used Oracle httpuritype - And the below mentioned procedure PROCEDURE TEST.PRC_TRANS_GERMAN will call the function - trans_jef
Input parameter for PRC_TRANS_GERMAN - > To_language and from Language. and this procedure will store the translated word in the seperate table. Input parameter for the function trans_jef - > Word to be translated , from languae, to_language. And this function will return the translated word.
The issue I am facing is when I am passing the table of around 10000 records, around 4000 rows are not getting translated. So when i try to capture the error in exception- the error is
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1819
ORA-29270: too many open HTTP requests
When I try to re execute the missed words, they were translated. But they are not getting translated in one go because of the too many http open request.
how to resolve this ORA-29270 errror.
Translator
/* Formatted on 07-09-2012 09:19:33 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE FUNCTION trans_jef (v_RawText IN VARCHAR2,
v_SrcLang IN VARCHAR2,
[Code]...
View 2 Replies
View Related
Jul 12, 2012
i have table structure (emp_no, emp_sal,emp_startwork)
i want query to show the monthly salary such as jan month salary Feb month salary
View 2 Replies
View Related
Dec 21, 2010
write a query to see how many(no) employees getting salary 3000 & what are their names respectively.
View 2 Replies
View Related
Apr 23, 2012
i need to write a query in order to receive a new table.
select table_name,col1, null col2
from
(SELECT b.table_name, b.col1 col1, a.CountPrimaryKeys
from
(SELECT user_constraints.table_name,
COUNT(user_cons_columns.column_name) CountPrimaryKeys
[code].......
View 21 Replies
View Related
Aug 29, 2013
May any one confirm that the below issue is not only to RAC but also on normal database enterprize edition 11.2.0.3 ? Bug 12865902 - NOWAIT lock requests could hang (like Parallel Queries may hang "enq: TS - contention") in RAC (Doc ID 12865902.8)
currently i am using for update nowait concurrently for 50 processes on similar table with similar query and getting performance issue.
SELECT txn_ID,status_FLAG
INTO txn_id,status_flag
FROM cp_txn_detail WHERE txn_ID = pi_txn_ID FOR UPDATE NOWAIT;
View 2 Replies
View Related
May 26, 2010
I want to write select query to get sum of quantity for each type of item ,in the table I have itemnum,itemtype and quantity ,type have value(A,B,C,....)
the result will be like that ex:
item.No A B C D ....
10 50 60 80 20
--- 50 will be sum(quantity) for item type A
how can I do that, I mean how can I retrieve many column from the same field?
View 17 Replies
View Related
Jan 24, 2011
is it possible to write select query o/p in html tag?
View 6 Replies
View Related
Apr 3, 2011
I am writing following query
SELECT DISTINCT a.list_type_code, a.list_type_name
FROM jls_list_type a, jls_list_control b
WHERE b.jalsa_srl = :jalsa_srl
AND b.list_no != a.list_type_code
ORDER BY list_type_code
I just want to display only those records from JLS_LIST_TYPE which is not present in other table JLS_LIST_CONTROL ... for this i wrote above query but it is not working.
View 9 Replies
View Related
May 9, 2011
I have to write a sub query / build a logic for the below.
There are several accounts which should have a zero balance i.e sum of all the amoutns in that account should be zero. If they are non zero , i have to report which amounts make up non zero balance.
If i have amts as +20 , -20 , -30,-10 i.e the sum is -40 indicating a non zero amount. I need the entire details of the records which makes up non zero sum. So in above case details related to -30 aand -10.
I'm using a sum group clause on several fields at which sum is required to be checked ie. date , account , currency . query that will bring individual records that don't make the sum zero.
Is it possible to write a outer query which will bring individual records which don't sum up to zero.
View 14 Replies
View Related
May 31, 2012
is it possible to write sql query o/p in HTML page?
View 3 Replies
View Related
Jun 5, 2012
how to write the query option in expdp ....in expdp by using query option...
where columnname between '05-May-12 02:57:00.000 AM' and '6-May-12 02:59:59.999 AM';
View 4 Replies
View Related
Jul 18, 2013
which table do I have to use to get the Inventory On Hand Packed Quantity based on Oracle EBS Suite using backend?
View 2 Replies
View Related
Jul 17, 2012
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies
View Related
Mar 9, 2011
I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25
View 4 Replies
View Related
Jun 14, 2012
I've a table with fields:
create table test
( f1 varchar2(10),
f2 varchar2(10),
f3 varchar3(10)
)
insert into test values ('d1','d2','d3');
insert into test values ('d10','d20','d30');
I want to update the fields of the table as per need i.e update only one field leaving all the data of the fields as it is. Suppose I want to update only f1 (from d1 to x1) field leaving f2, and f3 as it is. I've written stored procedure to update all the fields but do not know how to do it?
Quote:CREATE OR REPLACE PROCEDURE UPDATE_TEST
( U_F1 TEST.F1%TYPE,
U_F2 TEST.F2%TYPE,
[Code]....
View 7 Replies
View Related
Oct 3, 2010
I am getting an error "Single row query returns more than one row" in an Exception block.
But in subqueries I am using IN operator not using =.
And I don't have duplicates rows in tables.
View 16 Replies
View Related
