SQL & PL/SQL :: Selecting First Row From Multiple Joins?

Apr 2, 2010

I'm putting together a path to select a revision of a particular novel:

SELECT e.documentname, e.Revision, e.VersionNumber
FROM Catalog, BookInCatalog
INNER JOIN NovelMaster
INNER JOIN HasNovelRevision
INNER JOIN NovelRevision e
LEFT JOIN NovelRevision s

[code]...

My goal here is to select the earliest revision from the set of Novel revision. The revision field is a string.

When I run the query for Novels that have multiple revisions I get multiple records. If there is just one record I only get one row. If there are two I get four (two for each revision). As the number of revision increases it looks like it just mushrooms from there.

One other challenge is the format of the revision- a revision sequence could look like this:

A
B
C1
C2
C
D
E1
E

So there are "intermediate" revision referred to by a number. In this case I would select revision A, but if I had:

A1
A
B1
B

I would want to select B. I am pretty sure that all the revision are stored in the db in order.Notice that the comparison operator ">" is used in e.Revision > s.Revision. I initially though it should have been "<" because we want to select the initial but the other way gives me the right order (though the wrong results).

View 12 Replies


ADVERTISEMENT

SQL & PL/SQL :: Replacing Multiple Calls To Function By Table Joins

Dec 15, 2010

The following query calls the function get_meter_desc 8 times.

SELECT iu.instd_unit_id, iu.fk_cust_id, bill_cd,
iu.mfg_prod_cd, iu.mfg_prod_seq_no, ccequip.fk_mkt_cd,
eff_tmstmp, cust_ord_id, ord_dt, iu.xnac_co_cd,
iu.xnac_div_cd, smmr_wvr_cd, warr_expn_dt,
iu.auto_replen_ind, iu.ms_stat_cd,
inst_dist_id, instn_dt, iu.last_auto_dt,
iu.replen_freq_vlu, cpc_pln_cd, std_sply_ind,
emcv_total_qty, tot_actl_cpy_qty, init_emcv_tot_qty, ms_tran_cd,
[code]...

How can I replace the function call by the join in the main query?

View 19 Replies View Related

Selecting Via Multiple Tables

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]...

What I get is in green (services are calculated more than 2 times BUT in the same report).

What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]...

One person (zmo_kodas) can have a lot of reports (ats_nr).

Every report can have one or more services (pas_kodas).

View 1 Replies View Related

SQL & PL/SQL :: Selecting Via Multiple Tables?

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]

What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]

One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).

View 2 Replies View Related

Inserting Into A Table By Selecting Multiple Rows Into A Single Row

Jul 27, 2012

I have a flat file as source wherein I am getting values like

Comp_id, Comp_name, ISIN, column_name, column_value

The structure is like this may contain multiple records like Comp_id, Comp_name, ISIN will be same, but column_name will contain the column_name to which its corresponding column_value needs to be populated to.

E.g. of Feed File -

Comp_id, Comp_name, column_name, column_value

1,HSBC,branch_name,HSBC-DELHI
1,HSBC,branch_add,24-Lajpat Nagar
1,HSBC,branch_phone,2322322
2,HSBC,branch_name,HSBC-MUMBAI
2,HSBC,branch_add,24Andheri
2,HSBC,branch_phone,4445221
2,HSBC,branch_postalcode,400023

Target table structure

Comp_id, Comp_name, branch_name, branch_add, branch_phone, branch_postalcode

I need to insert the above data to a table by selecting data from above scenario.

View 10 Replies View Related

Precompilers, OCI & OCCI :: Using Pointer To Structure For Selecting Multiple Rows From Table

May 11, 2010

I am working on Pro*C and i have a requirement where i need to select all the rows from a table into a c - structure variable. Since i get to know the no of rows in the table which is getting selected only at run time, i need to create a pointer variable to the structure and i'll allocate the size to it based on the count of rows in the table using malloc or calloc.I tried allocating memory using calloc and it does not show any error. But when i when the exec select statement run it shows an error.

Statements i have used:
struct common *comp;
struct common_ind *comp_i;

comp = (struct common*) calloc(rowcount, sizeof(struct common));
comp_i = (struct common_ind*) calloc(rowcount, sizeof(struct common_ind));

exec sql at db1 select * into :comp indicator :comp_i from tab1;

Error i get :

Stop Error: -2112
Stop Error: -1012
Stop Error: -1012

View 2 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.

View 11 Replies View Related

PL/SQL :: Selecting Single Record From Multiple Record Based On Date?

Aug 26, 2013

I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name  

Null Type  ------ ---- ------------ ID_P        NUMBER       NAME_P      VARCHAR2(12) DATE_P      TIMESTAMP(6) Records---------------------1 loosi     22-AUG-13 01.27.48.000000000 PM1 nammi  26-AUG-13 01.28.10.000000000 PM2 kk        22-AUG-13 01.28.26.000000000 PM2 thej      26-AUG-13 01.28.42.000000000 PM 

now i have to select below 2 rows how can write select qurie for this?

1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej  26-AUG-13 01.28.42.000000000 PM

View 4 Replies View Related

Forms :: How To Open Multiple Forms Selecting At Once

Apr 26, 2011

OS: WinXP
Developer Suite 10g Rel.2

its a very basic query, i want to open multiple .fmb / .rdf files at once ( like pressing Shift or Ctrl key when selecting multiple files ), but its not allowing me

View 14 Replies View Related

Self Joins Getting Rid Of Duplicates?

Jun 17, 2008

I have a table1:
userid, name, town.

Now i want to do a self join like:

select a.name, b.name, a.town
from table1 a
inner join table1 b
on a.town = b.town
where a.first_name <> b.first_name AND
a.last_name <> b.last_name

i added the where clause to limit duplicates i would get but i still get duplicates eg. A B london, B A london etc.

View 9 Replies View Related

Using Subqueries In JOINS

Sep 30, 2011

In mys store procedure I am using a subquery with INNER JOIN. This subquery calls a user defined function which takes main query fields as parameter values. But i am facing issue for accessing main query fields. my query is like below:

SELECT
ID, Name, sub.Desc, sub.Date
FROM MainTable main
INNER JOIN
(
SELECT * FROM RMF_GetData(main.ID)
) sub
ON main.ContactID = sub.ContactID

I need data from a function in a table format based on some case conditions. Hence i need to join it with main table. But here oracle gives error as "invalid identifier" for main.ID parameter.

View 1 Replies View Related

Query To Use Joins

Mar 11, 2009

SELECT so.* FROM shipping_order so WHERE (so.submitter = 20)
OR (so.requestor_id IN (SELECT poc.objid FROM point_of_contact poc WHERE poc.ain = 20))
OR so.objid IN (SELECT ats.shipping_order_id FROM ac_to_so ats WHERE (ats.access_control_id IN (selectac. objid FROM access_control ac WHERE ac.ain = 20
OR ac.group IN ('buyers', 'managers'))))

rewrite this query to use joins. That would greatly simplify my sql query building code. The ids, objids, submitter, ain are numeric and group is a varchar.

View 1 Replies View Related

SQL & PL/SQL :: Joins With COUNT

Nov 16, 2011

I pulled in 1121 SSN's into a table and am using that table as the basis for returning data from other tables...including how many documents a user has in their folder.

My query; however, is only returning 655 rows...it is returning only those rows that have documents in their folders. I want to return ALL rows...WHETHER OR NOT THEY HAVE A DOCUMENT COUNT (count(*)). How can I get all 1,121 rows to return? I would like the output to look like:

SSN LOCATION EMP_STATUS FOL_STATUS COUNT(*)
-- For those folders containing documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER 12

-- For those folders containing 0 documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER NULL

Here is the query in it's current state:

-- Get User/Folder/Doc Count Information
SELECT b.ssn,
b.location,
b.emp_status,
c.fol_status,
COUNT (*)

[Code]....

So again, my problem here is that...not all FOLDERS contain DOCUMENTS...but I still want the folder data lised...I just need it listed with either a zero count (0), or a NULL in the COUNT(*) column.

I'm trying the various joins, but none of them seem to be working.

I've tried the old 8i (+) join as follows:

AND c.fol_id = d.doc_fol_id(+)

I've tried the inner join:

AND c.fol_id(+) = d.doc_fol_id

...and I've tried the 9i method (left outer and full outer) using the following types of notations:

folder c full outer join documend d on c.fol_id = d.doc_fol_id

...so far, no luck. I'm still having only 655 rows returned (the 655 are those folders that HAVE document count > 0. Any folder that has zero documents in the document table just aren't being returned in the query.)

View 9 Replies View Related

Can't Get Joins On Tables Properly

May 2, 2008

why how ever way i try i cant get the joins on the tables properly.... well i know i have to work hard....if join is not proper the data i extract is also not proper.Well now i have 3 tables...

ps_operations

Name Null? Type
----------------------------------------- -------- --------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
OPERATION_NBR NOT NULL VARCHAR2(10)
EFFECTIVE_FROM_DT NOT NULL DATE
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
OPERATION_DESC NOT NULL VARCHAR2(70)
HOURS_PER_PIECE_QTY NOT NULL NUMBER(9,6)
PIECES_PER_HOUR_RATE_QTY NOT NULL NUMBER(15,7)
EFFECTIVE_TO_DT DATE
EXTRACT_IND VARCHAR2(1)
[code]...

I have never worked on CPK and UK....so i dont know how to use them to join the tables,.

View 11 Replies View Related

Remote DB Query With Joins?

Feb 23, 2012

We have access to a remote Oracle database in Germany and need to insert selected data to our local Oracle database. Our problem is that we have to do several joins (7 tables) on the remote database as well as using one where clause (always the same: P.T_LIEF_LFNT_1='12803193').

Unfortunately we do not have rights to create a view on the remote database. Is there another way to send the entire query to the remote database for processing? Also, the query returns approximately 34,000 rows.

Here is our current query:

INSERT INTO PRIMUS(PARTNO,
SORT_FORMAT,
TP_WORKSPACE,

[Code].....

View 3 Replies View Related

SQL Tuning - String Of One-to-one Joins

Oct 1, 2013

I have a query I am trying to tune. It presently takes anywhere from 15 minutes to two hours to run, depending on how many records the client has. But it needs to run several hundred times, and finish over the course of a weekend. When it runs over, we have problems.

Here's the basic structure of the query:

CODESELECT ...
  FROM main_tab,
       tab_a,
       tab_b,
       tab_c,
       ...
       tab_z
WHERE main_tab.client_id = :1
   AND main_tab.unique_id = tab_a.unique_id(+)
   AND main_tab.unique_id = tab_b.unique_id(+)
   AND main_tab.unique_id = tab_c.unique_id(+)
   ...
   AND main_tab.unique_id = tab_z.unique_id(+);

All of the tables are indexed (and statistics are gathered) on the field unique_idMain_tab has an index on client_id.There is a one-to-one join (sometimes one-to-zero, thus the outer join) from the main_tab table to all the other tables.These are static tables, they're wiped and recreated - no changes, inserts, deletes.

By default, the optimizer does a full table scan and then a hash join on every single of the 26 tab_a through tab_z tables, only using the index on main_tab.

By the way I can add indexes, possibly even to the point of adding an index on some tables that would include all the fields found in the select clause on that table. But I cannot change the table structure (by, say, combining these tables together).

View 2 Replies View Related

SQL & PL/SQL :: Using NVL2 To Control Joins

Jan 5, 2012

I am trying to control which tables are joined based on a null value, so I figured I could use the NVL2.

Here is the code as it stands

NVL2(TBL_TRANSACTIONS.EQUIPSVCSEQ, TBL_EQUIPANDSERVICES.LASTWORKORDERSEQ, TBL_TRANSACTIONS.WORKORDERSEQ)
= TBL_WORKORDERS.WORKORDERSEQ (+)

To explain, if first value is null, then TBL_EQUIPANDSERVICES.LastWOSeq = tbl_workorders.workorderseq (+), otherwise join the transaction.workorderseq.

When I try and execute this code, I'm given "ORA-01417: a table may be outer joined to at most one other table." I've double checked, TBL_Workorders is not joined with any other table in any select.

Now, the EquipandServices.workorderseq is joined (+) with another table.

View 5 Replies View Related

SQL & PL/SQL :: Table Partition Using Joins

Mar 21, 2011

can i able to partition the table based on the column which is in another table ??

For example Table X need to be partitioned based on the column in The Table Y . and Table X and table Y has some relation.

View 2 Replies View Related

SQL & PL/SQL :: Group Functions In Joins

Jul 29, 2013

i want to get SUM(salary) by combining both employee and employees table.Look my table structure below:

SQL> select * from employee;

EMPNO ENAME HIREDATE ORIG_SALARY SALARY R MGR DEPTNO
---------- --------------- --------- ----------- ---------- - ---------- ----------
1 Jason 25-JUL-96 1234 8767 E 2 10
2 John 15-JUL-97 2341 3456 W 3 20
3 Joe 25-JAN-86 4321 5654 E 3 10
4 Tom 13-SEP-06 2413 6787 W 4 20
5 Jane 17-APR-05 7654 4345 E 4 10
6 James 18-JUL-04 5679 6546 W 5 20
7 Jodd 20-JUL-03 5438 7658 E 6 10
8 Joke 01-JAN-02 8765 4543 W 20
9 Jack 29-AUG-01 7896 1232 E 10
[code]....

Above, i used separate queries to get the result of SUM(salary) by deptno.Here, I want a single query to get SUM(salary) with deptno.

deptno Sum(salary)
----------------------------
10 30056
20 27132
30 6300
40 4300

View 4 Replies View Related

Inline Query And Joins

Jul 31, 2012

When to use inline query and to use join.

View 3 Replies View Related

SQL & PL/SQL :: Primary Key / Foreign Key Joins

Sep 29, 2010

I am working on a new project in OBIEE. I am asked to do the data modeling in the database using oracle sql developer. I have to create the joins based on the requirements. I have the tables created already. But the primary keys for few tables are not defined for few tables. PK-FK joins are also not done properly.

My questions are
(1) If I have to define the primary key for the existing tables can I do that using the alter table command or should I create the table all over again and then define it?
(2) If I have to make the changes in the existing PK-FK joins how do i go about doing that?

View 11 Replies View Related

SQL & PL/SQL :: Purpose Of Using Joins Over Where Clause

Feb 29, 2012

If a query can be written using where clause, what is the purpose of using joins over where clause?

View 11 Replies View Related

SQL & PL/SQL :: NULL In Left Joins

Jun 11, 2013

I am observing some skewed results for Left outer join where the main table has NULL in the field we are joining against with another table.

Just wondering if there are some tricks to get over it. I am currently using NVL(tab1.col1,'X') = NVL(tab2.col3,'X') and am just wondering if there is a better way to handle this.

View 1 Replies View Related

PL/SQL :: Query Using Subquery And Joins

Feb 7, 2013

I want to display first joined and last joined employee name(first_name) in each department like this

department_name | first joined employee | last joined employee

I think we have to use joins and sub query.

employee table attributes -
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: Convert Oracle Joins To Ansi?

Apr 11, 2011

the below merge statements has outer join.

merge into merge_st t
using (select * from merge_st1) src
on (t.id=src.id(+))
when matched then update set name=src.name;

I need to convert oracle joins to ANSI joins. I have tried below

update (select t1.name as t1_name,t2.name as t2_name
from merge_st t1 lef outer join merge_st t2
on(t1.id=t2.id))
set t1_name=t2_name;

It statements shows error like cannot modify the non key preserved tables.I have cheked these table has contains whether primary key or not.there is no constraints for these tables.Our application, constaints handle in front end. so we cannot create any constraints in oracel database.how to convert oracle joins to ansi join?

View 4 Replies View Related

SQL & PL/SQL :: Convert Scalar Queries To Joins?

Jun 20, 2012

I have the scenario like below:

create table test_a (id number, b varchar2 (20));
create table test_b (id number, a number, b number, c number, d number, e number, f number);
insert into test_a values (1,'Manu');
insert into test_a values (2,'Tanu');
insert into test_a values (3,'Anu');

[code].....

convert the query above using joins instead of scalar queries, as scalar queries decreasing the performance.

View 7 Replies View Related

SQL & PL/SQL :: Joins On Two Tables With 10 Million Rows Each

Jun 11, 2010

We have two tables, TableA and TableB that contain list of accounts and balances.The requirement is to compare the balances of accounts in both the tables, and if there is a difference, then record that difference with account number in another table.

Both TableA and TableB contain more than 10 million rows.What is the best way to do this task in PL/SQL? A join on TableA and TableB to know the differences has become very slow due to large volume.

View 20 Replies View Related

SQL & PL/SQL :: Use Only SQL Joins Not Minus Or Subquery Or Any Function

Aug 2, 2011

i have two tables having some fields .In table table1 there is 2 lakh rows and in table table2 there is 3 lakh rows.In both table 2 lakh rows are common.I have to find those 1 lakh rows which are distinct but the condition for this i have to use only sql joins not minus or subquery or any function.

when i doing this.select t2.id,t2.name,t2.loc from table1 t1,table2 t2 where t1.id <>t2.id;

View 8 Replies View Related

PL/SQL :: Nested Loops On Outer Joins

Apr 10, 2013

I have a select query that was working with no problems. The results are used to insert data into a temp table.

Recently, it would not complete executing. The explain plan shows a cartesian. But, there could be problems with using nested loops on the outer join.

Interestingly, when I copy production code and rename the temp table and rename the view, it works.

CREATE TABLE "CT"
( "TN" VARCHAR2(30) NOT NULL ENABLE,
"COL_NAME" VARCHAR2(30) NOT NULL ENABLE,
"CDE" VARCHAR2(5) NOT NULL ENABLE,
"CDE_DESC" VARCHAR2(80) NOT NULL ENABLE,

[Code]....

View 1 Replies View Related

Massive Delete (2 Joins Required) With No Partitioning

Oct 25, 2011

I'm experiencing some infinite loop for my delete. I tried many way to deal with this problem but still take too much time. I will try to be clear as possible.

I have 4 implicated table in this problem.

The deletion is done depending of the pool_id given

Table 1 contain the pool_id
Table 2 the ticket_id foreign join ticket_pool_id with the pool_id
Table 3 ticket_child_id foreign join ticket_id with the ticket_id
Table 4 ticket_grand_child_id foreign ticket_child_id join with the ticket_child_id

Concerned count for each
table 1---->1
table 2---->1 200 000
table 3---->6 300 000
table 4---->6 300 000

So in fact it`s 6.3M+6.3M+1.2M+1 row to be deleted

Here`s the constraint :
-No partintionning
-Oracle version 9
-Online all the time so no downtime neither CTAS
-We cannot use cascade constraint
-The normalization is very important

Here`s what I tried:
-Bulk delete
-Delete with statement (In and Exists clause)
-temp table for each level and 1 level join
-procedure and commit each 20k

None of those worked in a decent time frame like less then one hour. The fact that we cannot base a delete on one of the column value is not working. Is there a way I'm getting desperate now

View 3 Replies View Related







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