SQL & PL/SQL :: How To Write Correlated Sub Query

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


ADVERTISEMENT

SQL & PL/SQL :: Correlated Query And Inline View

Apr 9, 2010

which one is better in performance point of view and why between inline view and correlated query.

View 5 Replies View Related

SQL & PL/SQL :: How To Write Column To Row In A Query

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

Forms :: How To Write Query For ADD Button

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

SQL & PL/SQL :: How To Write Query To Convert XML To Table

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

SQL & PL/SQL :: Write Query To Show Monthly Salary?

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

SQL & PL/SQL :: Write A Query To See How Many (no) Employees Getting Salary 3000?

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

SQL & PL/SQL :: Write A Query In Order To Receive A New Table?

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

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 View Related

SQL & PL/SQL :: Write Select Query To Get Sum Of Quantity For Each Type Of Item?

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

Client Tools :: Possible To Write Select Query O/p In HTML Tag?

Jan 24, 2011

is it possible to write select query o/p in html tag?

View 6 Replies View Related

SQL & PL/SQL :: Write A Query Which Shows Data Of One Table Not Present In Other?

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

SQL & PL/SQL :: Write Outer Query Which Will Bring Individual Records Which Don't Sum Up To Zero

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

Client Tools :: Write Sql Query O / P In HTML Page?

May 31, 2012

is it possible to write sql query o/p in HTML page?

View 3 Replies View Related

SQL & PL/SQL :: Write Single Row Sub-query Which Returns ID_2 Value Of Row With Del_flag As N

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

PL/SQL :: Write Dynamic SQL To Query Unknown Number Of Columns

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

Export/Import/SQL Loader :: How To Write The Query Option In Expdp

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

Write A Query Based On Inventory On Hand Packed Quantity

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

SQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables

Jul 17, 2012

write a query to delete similar records in particular fields(columns) in different tables.

View 2 Replies View Related

SQL & PL/SQL :: Using Correlated Values In Nested Subquery

Sep 1, 2010

select rtrim(xmlagg(xmlelement(e, table_name||',')).extract('//text()'),',')
from (
select distinct table_name from tr_products tr join all_instruments v
on tr.PROD_COA_ID = v.PROD_COA_ID
where tr.LEVEL_06_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_05_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_04_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_03_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_02_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_01_VALUE like t1.COLUMN_VALUE||'%')

This is an excerpt of a code of a huge query, I didn't want to write it all here because there is no point in doing that since this is the part of the query that I'm actually having problem with, and I think you will be able to understand my question just by looking at this piece of code.

Anyway, this here is actually a subquery in a select-clause of a main query, and apparently it has a (nested) subquery of its own (as seen in the code). I want to reach some values ("COLUMN_VALUE") that are correlated to the parent query (t1 is alias of a table in the from-clause of the main query), and as you can see, I'm trying to reach it from this level 2 subquery. As I have recently found out, Oracle does not allow this, it only tollerates correlation down to level 1.

Here is a scheme of my query:

select something1,
(select something2 from
(select something3 from some_table2 t2
where t1.value = t2.value))
from some_table t1

can this be overridden somehow, is there a workaround for this limitation?

View 3 Replies View Related

PL/SQL :: Correlated Update (Inline View)

Jan 21, 2013

Can I update inline view like below?

update

(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55) a,
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) b
where a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2))

set b.answer = a.answer;

My second question is: The following query is giving error. What can I do?

update answers ans set (ans.answer) = (with a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55),
b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3)
select a.answer from a join b on a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2) and b.answer_id = ans.answer_id) where ans.main_group_id = 3;

SQL Error: ORA-00904: "ANS"."ANSWER_ID":

View 8 Replies View Related

Refreshing Logically Correlated Snapshots Data?

Nov 29, 2010

Simplifying the data structure that my problem concern, let's say there are two materialized views between whose data there's a one-to-many relationship [the relationship can be logical without a need of creating any foreing keys].

The data should be as actual as possible respecting the content of the master tables, let's say it shoul be refreshed every 5 minutes.

As far as I know, the jobs related to each snapshot, even if they have a START WITH and NEXT parameter set to the same value, work independently...So: what would be the best manner to synchronize the jobs so as to make sure the data of both snapshots are coherent?

View 3 Replies View Related

SQL & PL/SQL :: Advance Sub-queries (Exists With Correlated Subquery)?

Jul 18, 2013

concept of Exists, With and Correlated Subquery in Sql plus 10g from begining, what are they how do they work with step by step example from basic.

View -1 Replies View Related

Identifier Functions UID Correlated With Column Dba_users.user_id

Jul 6, 2012

Is the identifier functions UID always correlated with the column dba_users.user_id ?

View 4 Replies View Related

SQL & PL/SQL :: Building Correlated Subquery Select Statement By Aggregating Timestamps To Months?

Jun 11, 2010

I have 3 tables, user_login_event, person and resource_viewed_event. What I want to do have a report for each month, users logged in our application and then show for each month, how many records were created in table person and how many resource views events were logged in resource_viewed_event.

Lets only worry about the timestamp fields in these tables now as I want to use them to join the tables together or at least build correlated subqueries along the months. I have tried several options, all not leading to a desired result:

Left outer join. Works but its incredibly slow:

SELECT
distinct to_char(ule.TIMESTAMP,'YYYY-MM') as "YYYY-MM",
count(distinct ule.id) as "User Logins",
count(distinct ule.user_id) as "Users logged on",
count(distinct p2.id) as "Existing Users",
count(distinct p1.id) as "New Users",
count(distinct r1.id) as "Resources created"

[code]....

Tried the same with left outer joins of temporary tables created through select statements:

select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"

[code]....

Tried the same with left outer joins of temporary tables created through select statements:

select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"

[code]....

another approach is to create my own temporary tables using select statements and create fixed Month values which I can use to directly link the sets together.

select
distinct ule.loginday as "Month",
count(distinct ule.id) as "Logins",
count(distinct ule.user_id) as "Users logged in",
count(distinct p1.user_id) as "Users created",
count(distinct p2.user_id) as "Existing users1"

[code]....

performance is OK with 2 tables but the example above takes forever to execute.

Tried an approach with union but this creates new rows for each table

SELECT DISTINCT p1.MONTH AS "Month",
COUNT(DISTINCT p1.user_id) AS "Users created",
NULL AS "Logins",
NULL AS "Users Logged in",
NULL AS "Resource views",
NULL AS "Resources viewed"
FROM (SELECT To_char(person.created_on_date, 'YYYY-MM') AS MONTH,

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Write A Query To Combine Amount1 And Amount1 Into Single Column Amount?

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

How To Write Exception Handler For PL / SQL

Jan 22, 2007

How to write an exception handler for the error "PL/SQL: ORA-01031: insufficient privilege"

View 2 Replies View Related

Can't Write SQL Scripts On Oracle G10

Apr 8, 2009

I just install the oracle g10 on my laptop, which runs windows XP and it seems to work fine BUT it will not allow me to write Scripts I go to the Script Editor, i can write on the "Script Name" but below i cant ever put the cursor to write the script!

View 3 Replies View Related

How To Write Audit Script

Oct 14, 2008

To write a audit script, that will analyze SQL usage and the performance of both individual statements and the overall memory utilization.

View 1 Replies View Related

SQL & PL/SQL :: How To Write Package For Deletion

Apr 21, 2012

how to write the package for deletion?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved