PL/SQL :: Convert Normal Subquery Into Join

Nov 1, 2012

Convert normal subquery into join.

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Normal Join And Outer Join

Oct 19, 2013

Lets say I have three tables t1 and t2 and t3.

SELECT * FROM T1;

Id
____
1
2
3
4

SELECT * FROM T2;

Id
____
1

SELECT * FROM T3;

Id
____
1

Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join

select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id

However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4

One way of doing that is using the not exists clause

select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)

Is there a better way of doing this in sql ?

View 5 Replies View Related

SQL & PL/SQL :: Transform NOT EXIST Subquery In LEFT OUTER JOIN With Nested Tables?

Mar 22, 2012

these are the sample data :

CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
CREATE TABLE department (
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;
INSERT INTO department (courses)VALUES (CourseList('1','2','3'));

[code]....

The query returns the correct data, CourseList that are not subset of any other CourseList of the table.

I am trying to convert this not exists in a left outer join query to check if the performance is better, but I don't know how to do it.

I was making some variations of this code :

select d1.courses c_1, d2.courses c_2
from department d1,department d2
where d1.courses<>d2.courses(+);

but it is now working.

View 3 Replies View Related

SQL & PL/SQL :: Convert Join Sub Query

Sep 10, 2012

I want to convert join to subquery. how cna i do this.

FROM
TABLE_1 A
INNER JOIN TABLE_2 B
ON ( A.COL_1 = B.COL_1)

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Cursor And Normal For / While Loop?

Mar 7, 2011

what is the difference between using a cursor and using a normal for/while loop to retrieve and process the result set.

View 3 Replies View Related

PL/SQL :: Difference Between Materialized And Normal View?

Aug 29, 2012

the difference in detail with an example.

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Normal And Cluster Index On Table?

Aug 4, 2010

Difference between Normal Index on Table and Cluster Index on Table?

View -1 Replies View Related

SQL & PL/SQL :: PL/PDF - Create Bold And Normal Text In Same Paragraph?

Nov 17, 2010

I'm trying to create bold and normal text in the same paragraph with the library PL/PDF, but I can't. This is a small example:, this is an example to the library PL/SQL, adding text in bold and normal text." This text should be justified on the margins I can set.

View 3 Replies View Related

Select Statement Gives Different Results Using Bitmap Or Normal Index

Jan 10, 2012

we have a strange symptom in a database Oracle 11.2.0.2 EE. Following question comes from our application developers.

The following SQL statement:

SELECT
v.reporting_month,
sum(v.f_s) "REV_S",
sum(v.f_f) "REV_F",
sum(v.f_c) "REV_C",
[code].......

gives different result when we exchange the index ksr_valid_until_i on table kreditkarten_sets_rs. For some reasons we changed the index from bitmap to normal and are getting different results. Switching back gives us the same results as before. When we avoid the usage of this index in the statement than we are getting the same results as when we are using the normal index.

View 4 Replies View Related

SQL & PL/SQL :: Create Normal Primary Constraint During Table Creation

Mar 23, 2010

i'm new to oracle environment.how can i specify NONCLUSTERD INDEX on Primary cloumn during table creation.By default it will create clusterd index but i need non-clusterd index on it.

I'm using following stmt to create normal primary constarint during table creation,

CONSTRAINT PKFORM_PROPS PRIMARY KEY (FORM_PROPS_PK) USING INDEX TABLESPACE DB123_INDEX

how can i change the above query, so that it should create NONCLUSTERED INDEX on Primary key column.

View 5 Replies View Related

Server Administration :: Oracle Normal And Application DB In Single Machine

Apr 26, 2012

I have already installed oracle 10g version in my Desktop. Now i want to install APPLICATION SERVER 10g version.

Should I Un install the Normal Database.
Or else can I have both in a single machine.

View 1 Replies View Related

Forms :: Login As SYSDBA In Oracle From Normal Logon Procedure?

Apr 24, 2012

is it possible to login as SYSDBA in oracle forms from normal logon procedure?

logon(sys_acc, sys_pwd||'@'||:GLOBAL.db_conn);

The reason behind this is to make a form which will alter user's password. But all the users are in SYS account and it is necessary to login as SYSDBA in forms to execute alter statement.

View 4 Replies View Related

PL/SQL :: Normal Insert Proc - Bulk Collect Taking More Time

Feb 4, 2013

I am working on oracle 11g...I have one normal insert proc

CREATE OR REPLACE PROCEDURE test2
AS
     BEGIN
     INSERT INTO first_table
     (citiversion, financialcollectionid,
     dataitemid, dataitemvalue,
 [code]....

I am processing 1 lakh rows.tell me the reason why bulk collect is taking more time. ? According to my knowledge it should take less time. do i need to check any parameter?

View 5 Replies View Related

Automatic Storage Management :: RAC 11gR2 - Normal Or External Redundancy

May 20, 2013

My comp is running with RAC 11gR2 with ASM on external redundancy. we are planning to move on normal redundancy.

which will be better normal redundancy or external redundancy.

View 7 Replies View Related

Automatic Storage Management :: Normal Redundancy Disk Group

Oct 3, 2013

One of my collegues created an ASM disk group with normal redundancy using 2 disks. But the disks are not of same size, one disk is 100GB and another one is 10GB. Now the usable space out of diskgroup is showing 55GB. When I checked the diskgroup properties it is showing 2 fail groups one is DATA1_ 0000 with 10GB and other is DATA_0001 with 100GB.  My question is why it is showing 55GB as usable space? My assumption is as it is having 2 fail groups with different disks of different sizes. For the failgroup 2 even it is of 100GB size, in order to maintain the redundancy with other small FG(10G) it will consider only 10GB out of 100GB. So, the 2nd FG size also should be 10GB. So that the usable space should show as 10GB as opposed to 55GB (not (100+10)/2). 

View 12 Replies View Related

Performance Tuning :: Creating Normal / Composite Indexes On Fields

Feb 14, 2013

I Want to tune the attached query. I have tried by creating the normal indexes and composite indexes on the fields . I feel that , Only normal index is required for this instead of composite index?

11:15:19 SQL> @slot.sql
11:16:03 SQL>
11:16:03 SQL> drop table slot purge;

Table dropped.

Elapsed: 00:00:00.05
11:16:03 SQL>
11:16:03 SQL> create table slot
11:16:03 2 (
11:16:03 3 id varchar2 (40) not null,
[code]....

- dynamic sampling used for this statement
22 rows selected.

View 8 Replies View Related

Server Utilities :: Sqlldr - Change The Load Type From Bulk To Normal

Jan 25, 2012

sqlldr works fine. but, i have primary keys defined in the table. So the SQL loader throws error that

SQL*Loader-951: Error calling once/load initialization
ORA-26002: Table WD has index defined upon it.

How should i change this load type to "normal" in sqlldr ? I think sqlldr made for bulk load purpose only.

View 3 Replies View Related

Automatic Storage Management :: Oracle ASM Normal / High And External Redundancy?

Jun 27, 2013

, I have a few doubts in Normal,High and External redundancy levels in ASM concept. External - No failure group. No mirroring. Normal - Two Way Mirroring. One failure group. High - Three Way Mirroring. Two failure group. 1.

above mentioned 3 types in correct.2. My main question is what is the minimum number of disks needed in Disk group creation in Normal and External redundancy??

View 3 Replies View Related

Automatic Storage Management :: Diskgroup With Normal Redundancy One Disk Is Failed Then 2nd Instance Down

Mar 27, 2013

My cluster have two node.Data diskgroup uses normal redundancy.Priamry disks are located in one storage.Fail group disks are located in another storage.When we close the failgroup disks' storage the 2nd instance restart.After automatically restarted everything goes well.I want to know why does the 2nd instance restart?Is it normal?

View 2 Replies View Related

Forms :: ORA-00000 Normal Successful Completion Error While Populating Data From Master Block To Detail

May 21, 2012

ora-00000 normal successful completion error while populating data from Master block to Detail block in Oracle Forms 11g.

View 6 Replies View Related

SQL & PL/SQL :: From Analytical To Subquery

Jun 10, 2010

I was reading a tutorial for analytical function and i found something like this

sum(princial) keep(dense_rank first order by d_date) over partition by (userid, alias, sec_id, flow, p_date)

How to translate this into simple queries / subquery? i am aware that analytical function are faster but i would like to know how this can translate to using query without analytical function.

View 12 Replies View Related

SQL Subquery And Aggregation

Nov 6, 2010

I have a database containing the following after entering the following sql command

SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
TITLES.ROYALTY_RATE AS ROYALTLY_RATE,
AUTHOR_TITLES.ROYALTY_SHARE AS ROYALTY_SHARE,
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID

TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T01 12446,34 ,05 1 Sarah Buchman
T02 190841,7 ,06 1 Sarah Buchman
T03 1025396,65 ,07 1 Christian Kells
T04 168882,99 ,08 ,6 Hallie Hull
T04 168882,99 ,08 ,4 Klee Hull
T05 1400008 ,09 1 Klee Hull
T06 225834 ,08 1 Wendy Heydemark
T07 35929790 ,11 ,5 Wendy Heydemark
T07 35929790 ,11 ,5 Klee Hull
T08 40950 ,04 1 Kellsey
T09 69750 ,05 1 Kellsey

TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T10 1 Wendy Heydemark
T11 752042,77 ,07 ,3 Hallie Hull
T11 752042,77 ,07 ,3 Klee Hull
T11 752042,77 ,07 ,4 Kellsey
T12 1299012,99 ,09 1 Wendy Heydemark
T13 313905,33 ,06 1 Sarah Buchman

17 rows selected.

What I need to do is create a subquery and use Aggregation to list the author receiving the greatest royalties on revenue. so i used the command to get the royalties

SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME,
((PRICE * SALES) * TITLES.ROYALTY_RATE * AUTHOR_TITLES.ROYALTY_SHARE) AS ROYALTIES
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID

TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T01 12446,34 Sarah Buchman 622,317
T02 190841,7 Sarah Buchman 11450,502
T03 1025396,65 Christian Kells 71777,7655
T04 168882,99 Hallie Hull 8106,38352
T04 168882,99 Klee Hull 5404,25568
T05 1400008 Klee Hull 126000,72
T06 225834 Wendy Heydemark 18066,72
T07 35929790 Wendy Heydemark 1976138,45
T07 35929790 Klee Hull 1976138,45
T08 40950 Kellsey 1638
T09 69750 Kellsey 3487,5

TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T10 Wendy Heydemark
T11 752042,77 Hallie Hull 15792,8982
T11 752042,77 Klee Hull 15792,8982
T11 752042,77 Kellsey 21057,1976
T12 1299012,99 Wendy Heydemark 116911,169
T13 313905,33 Sarah Buchman 18834,3198

17 rows selected.

So how do I add up the royalties values associated with each author and find the max? for example I add klee hulls's royalties from each book and get 2,123,336.32(doing it by hand on calculator) what is the sql to find the max royalties for each author? P.S the answer should be KLEE HULL with 2,123,336.32

View 6 Replies View Related

Single Row Subquery Returns More Than One Row

Dec 6, 2012

I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.

select b.value , b.name, p.value ......
(case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
and c.ad_column_id = 4216 ) end) as oldtradeName
from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us
where a.c_bpartner_id = b.c_bpartner_id
and a.zz_receipt_id = r.zz_receipt_id
and us.ad_user_id = r.createdby
and p.m_product_id = a.m_product_id

View 2 Replies View Related

Single-row Subquery Returns More Than One Row?

Dec 7, 2009

Just trying to update a table in which the sales amount is inserted when the sales amount is null.

I have

UPDATE ph2_customer_temp
SET sales_amount = (
SELECT sl.sales_amount
FROM PH2_CUSTOMER_TEMP pct
join
sales_limit sl
on substr(pct.site_code,1,2) = sl.state
where pct.credit_limit is null )

View 2 Replies View Related

Limiting Subquery To First Record?

Oct 1, 2007

restricting a subquery's results to the first record. If I use the following:

SELECT WU.DISCREP, M.PART, M.ETA
FROM DB.MICAP M
LEFT JOIN DB.WRITEUPS WU ON M.WRITEUPID=WU.WRITEUPID
LEFT JOIN DB.WUC WUC ON WU.WUCID=WUC.WUCID
WHERE (WU.AIRCRAFTID=205) AND (WU.CORRECTED=0)
ORDER BY WU.PACER DESC, WUC.WUCCODE

I get

-------------------------------------------------------
#5 GEN KICKED OFFLINECABLE02-MAR-08
#5 IDG HARNESS REQUIRES WIREWIRE24-MAY-08
#1 GEN WENT OFFLINECABLE02-MAR-08
AERIAL REFUEL DOORS W/N OPENLINEAR DEL29-SEP-07
AERIAL REFUEL DOORS W/N OPENCHECK VLV30-SEP-07
WEATHER RADAR TILT KNOB HASRADAR COMP05-OCT-07

But I need the highlighted line eliminated. I've tried DISTINCT subqueries in the WHERE M.WRITEUPID IN (SELECT DISTINCT... various experiments with joins, etc.

View 4 Replies View Related

Single-row Subquery Returns More Than One Row

Jan 10, 2012

I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.

 UPDATE ps_ntsr_gf_stufile a
    SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR')
    FROM PS_CLASS_TBL b
      WHERE a.CLASS_NBR = b.CLASS_NBR
      AND a.STRM = '1118'
      AND a.INSTITUTION = 'NT752')
      WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL); 

I'm still getting the ORA-01427 error.

View 1 Replies View Related

SQL & PL/SQL :: Error ORA-00904 While Using Subquery?

Jan 8, 2013

I'm facing an issue "ORA-00904". Below is the test case. Both the queries are different. I'm only focussed to find out the reason.

CREATE TABLE ACCT
(
ACCTNBR NUMBER(10)
) ;

[Code].....

In first query I'm able to refer to table alias A but in second query I'm not able to refer it. The only difference is that in second query I've not used outer table at second level. Is it the desired behaviour?

View 8 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row

Mar 20, 2012

/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE
AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND
B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND
RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/

when i am trying to run this query i am getting an error'single row subquery returns more than one row'.

View 2 Replies View Related

SQL & PL/SQL :: Single-row Subquery Returns More Than One Row

Jul 30, 2010

My problem : I Wrote a package(a function), this function just return a varchar2 , but in this function I wrote a cursor , when I call this function in this manner :

SELECT secu.u_menu_id,
secu.menu_id,
menu.prompt,
secu.u_type,
secu.u_id,
[code]....

SQL return : Ora-01427 Single-row subquery returns more than one row

Yes , in this function it has cursor that more than one row. But I let this function return varchar2 ,

View 13 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row?

Apr 28, 2012

I have written the below sql select loc,(select ename from emp where emp.deptno = dept.deptno) from dept

It results in the below error.

[Error] Execution (1: 13): ORA-01427: single-row subquery returns more than one row

I have modified the SQL and got the required output
select em.ename,dep.loc
from
(select loc,deptno from dept) dep ,
(select ename,deptno from emp) em
where dep.deptno=em.deptno(+)

I have written the below sql to fetch all loc for emp which got executed

SELECT ENAME, (SELECT LOC FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO) LOC
FROM EMP

But as i need all locations irrespective of any emp in the locaton so i tried to put emp side (+) which resulted in error.

View 3 Replies View Related







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