SQL & PL/SQL :: Getting Latest Record From Different Tables?

Feb 4, 2011

I tried to post this issue earlier but it was not very clear. Well. Let me try to put in more better way.I have four tables storing order & customer information.

For given order number and cust _id I need to display latest record. here are my four tables.

SQL> select * from so;
ORD_NO ORD_DATE CUST_ID
---------- --------- ----------
1 01-JAN-10 10
2 02-JAN-09 20
3 03-FEB-11 30

SQL> select * from sol;

FK_ORD_NO FK_CUST_ID CUST_NAME LOCATION
---------- ---------- ------------------------- ----------
1 10 abc MA
2 20 xyz CA
3 30 ijk LA

[code]...

There will be a stored procedure that will take order_no and cust_id as input paremeters and return out ref cursor.

create procedure P1( in_ord_no in number,
in_cust_id in varchar2,
out_cur out sys_refcursor);

Here is the expected output.

Case 1 - in_ord_no =4 , in_cust_id = 10
Expected output - Empty cursor. Because bothe tables does not have matching record.
Case 2- in_ord_no = 3 in_cust_id = 30
Expected output =

[code]...

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Updating Table With Latest Record

Mar 17, 2011

DROP TABLE TESTING CASCADE CONSTRAINTS;
CREATE TABLE TESTING
(
DATAPERIOD DATE,
EMPLID VARCHAR2(20 BYTE),
B_OS_REASON VARCHAR2(9 BYTE)
)
TABLESPACE USERS;

[Code] ........

SQL> select * from testing order by 1;

DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972

In the above result, We need to go from bottom up and when we hit some value we need to update with the lastest record as below.("Blank" space are considered as null.)

DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972 XXXXXX

View 5 Replies View Related

SQL & PL/SQL :: How To Get Latest Record In Embedded Select Statement

Mar 12, 2013

I am trying to run an Oracle report with a query that has an embeded sql. this sql is returning more than 1 row, and the report is failing.

I need to pick the latest record entered that this sql return.

I tried rownnum and it works but only i can get the rown num I specify, not the latest record. I try to order, but I am getting an error back.

select w.emp_no, (select t.timestamp
from tob.work_unit t
where t.work_date = to_date('20130312', 'YYYYMMDD')
and rownum = 1
order by t.timestamp desc)
,w. spare_type
from work.work_unit w
where w.work_date = to_date('20130312', 'YYYYMMDD')

I am getting missing right parenthesis at the order by keyword My report is much complex than this, but I am tring to see if I can get the row that I want.

View 10 Replies View Related

SQL & PL/SQL :: How To Select Latest Updated Record From Table

Sep 21, 2011

I want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0

View 8 Replies View Related

Reports & Discoverer :: Pick Latest Record Rank And Count?

Sep 1, 2010

Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this

name semester status year
A 1 enrol 2010
A 2 withdraw 2010
A 3 enrol 2010
B 1 enrol 2010
B 2 withdraw 2010

I want to count their latest status only. It should come up with
Total Enrol - 2
Total Withdrawn - 1

For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work?
Here's my calculation:(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name)) It tells me that 'Aggregation of Analytic function not allowed'

View 3 Replies View Related

SQL & PL/SQL :: To Retrieve Only Latest Repair Information Based On Latest Date

Apr 3, 2012

I would like to retrieve only the latest repair information based on the latest date regardless of the other information, so I would like to query only items 3 and 5 in the following example.

drop table a;
create table a(
seq number,
custom_id number,
repair_id number,
repair_date date);
[code]........

View 10 Replies View Related

SQL & PL/SQL :: Joining Tables And Getting Record With Least / Min Date Value?

Jan 16, 2013

We have two tables tab1 and tab2 same structures and empid and id are keys.

We need to query for records that doesnt exist in tab2 and exist in tab1 based on keys above and then insert into t2.And also we have a date column in tab1...

if we have two records..that match empid and id and if dates are different we have to take one record with the least date like min(Date) and insert that one record in to tab2.

View 11 Replies View Related

SQL & PL/SQL :: List All Tables In Database With Particular Record Values Combination

Jul 1, 2013

I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.

empidenamejob
7369, 'SMITH', 'CLERK'

I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?

View 3 Replies View Related

Get Latest Records?

Jun 9, 2011

I have a table that contains history for vehicle positions. In order to find the latest positions quickly, I've included a LATEST column that is 1 if the record is the latest position and 0 otherwise. The table is maintained via a stored procedure. The procedure first sets the latest record for the vehicle to history...

UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = <vehicle ID> AND latest = 1

It then inserts the new latest record...

INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (<vehicle_id>, <x pos>, <y pos>, SYSDATE, 1)

Is it possible for me to end up with 2 latest records?Consider this scenario...

Session #1: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #2: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #1: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8533, -117.1180, SYSDATE, 1)
Session #2: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8534, -117.1178, SYSDATE, 1)

I'd end up with 2 latest records. How can I protect against this? I considered using SELECT FOR UPDATE, but seems like there are too many negatives going that route

View 4 Replies View Related

SQL & PL/SQL :: Having Latest Date

Aug 9, 2013

I have a query where i need to pull back the latest dated record for a tariff

my data looks like

Col_A Col_B Col_C
1| 100 29-Sep-11 Tariff_1
2| 200 24-Apr-12 Tariff_2
3| 300 17-Oct-12 Tariff_3

and i need to add a subquery which pull back the col_c with the larges Col_B i.e i need to pull back tariff_3 only

i tried using a max but dont think that will work.
...
and c.Col_c in ( select col_c where max(col_b))

View 21 Replies View Related

SQL & PL/SQL :: How To Find The Latest Date

Jul 31, 2010

SQL> select * from emp;

SINO BOOK UPDATION_
---------- ---------- ---------
1 UB 01-MAR-10
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 12-JUN-10
4 MB 31-JUL-10

6 rows selected.

SQL> SELECT sino, book, updation_date
2 FROM emp
3 WHERE updation_date IN (SELECT MAX (updation_date)
4 FROM emp
5 GROUP BY book);

SINO BOOK UPDATION_
---------- ---------- ---------
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 31-JUL-10

I would like to know, how to find out the latest date from above query without using group functions like max, min,order by and group by.

View 10 Replies View Related

SQL & PL/SQL :: How To Find Last Or Latest Inserted Row

Apr 22, 2013

i have a table in that every month i insert rows and my table doesn't have primary key,index and date filed.for example:-table is like this

name salary Id
john5000101
brat4500102
smith4600103
john5500101
brat4600102
smith4800103

i think one cannot tell in above table whether "john" salary is 5000 or 5500(last insert row is 5000 then john salary is 5000)

when it comes to retrieve i have to pick the latest(last) insert row of particular Name.Is there any auto generated row_numbers in Oracle.

View 1 Replies View Related

SQL & PL/SQL :: Selecting Latest Date

Aug 23, 2012

have a bit of a SQL trouble. I have a simple table (pcuk_BG_alloc_TAB) which stores Parts, Quantities and Applied dates

PART_NO QUANTITY APPLIED
PartA 100 10/8/2012
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 50 10/8/2012
PartC 75 15/8/2012
PartC 80 21/8/2012

I am only interested the latest applied date for each part and am looking for this to be returned in a select statement (as below)

PART_NO QUANTITY APPLIED
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 80 21/8/2012

I have tried using the max function (select part_no, quantity, max(applied) from pcuk_BG_alloc_TAB group by part_no, quantity) but seems as the records have different quantities it treats them separately.

View 21 Replies View Related

Latest Critical Patch Update?

Oct 14, 2010

identify the latest Critical Patch Update for 10g RAC. My DB server is 10g RAC 2 node cluster running on IBM Aix Pseries servers. The version is 10.2.0.3.

View 1 Replies View Related

Download Latest Critical Patches?

May 3, 2011

I want download latest critical patches of 11.1.0.7.0, 11.1.0.6.0. how can i download

View 1 Replies View Related

How To Export Latest N Records From A Table

Jul 2, 2012

How to export latest n records from a table using the below query(latest n records)

select * from (select empid,ename,sal,joining_date from emp order by joining_date desc) where rownum<n;

View 3 Replies View Related

Installation :: Latest Patchset For 11.2.0.3.0 Windows 64 Bit

Apr 11, 2013

Platform: windows 64 bit
Oracle database 11.2.0.3

I successfully upgraded my database from oracle 11.2.0.1.0 to 11.2.0.3.0 and the upgrade worked fine. I installed the 11.2.0.3.0 in a new oracle home.

Now my task is to install the latest patch set release on the new oracle home(Make database upto date with latest fixes indeed). I am confused with the available documentations in my oracle support.

As CPU is now considered as a subset of PSU patch, i believe i have to apply the latest PSU only.Is this correct? Is there a step by step guide for applying latest patch?I saw there are 5 PSUs released (latest 11.2.0.3.5), do i need to apply each of them or just this final one?

Give me the latest available patchset/patch(Still not clear) install guide and latest available patch download links.

View 3 Replies View Related

PL/SQL :: Latest Salary (last 2 Updated Records)

Jun 18, 2012

I wanted to know the latest salary(last 2 updated records ) getting by emp- id 252 based on below mentioned information

source desti sal emp id MGR
1-Jan 1-Feb 1000 252 venkat
2-Jan 2-Feb 4000 252 venkat
2-Feb 2-Feb 5000 252 venkat

View 4 Replies View Related

Query To Get Rows Of Latest Date From Each Group

Aug 9, 2012

I want to get rows by latest date for each group pf rows.

say my data is :

TYPE1 TYPE2 quantity DATE-ORDERED
sweets chocolate 10 05-FEB-2012
sweets chocolate 10 04-DEC-2012
sweets chocolate 10 08-FEB-2012
pastries chocolate 20 08-AUG-2012
[Code] ..........

I want to get results by latest date,
sweets chocolate 10 04-DEC-2012
pastries chocolate 20 08-AUG-2012
sweets vanilla 10 05-DEC-2012
pastries vanilla 20 05-NOV-2012

I have tried Queries with Max(DATE-ORDERED) and grouping it..its not showing me results because I don't have indexes in my data.

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Data For Latest Date For Some Purchases?

Mar 13, 2012

I need a query to get data for the latest date for some purchases.

My expected output would be

Date - Item - Price - Qty
10/6/2011 10129 .5 1
6/8/2011 10130 13.33 9
2/6/2011 10131 21.74 24

Below are the scripts for this.I have also attached it.

CREATE TABLE XX_PO_LINES_ALL
( CREATION_DATE DATE,
ITEM_ID NUMBER,
UNIT_PRICE NUMBER,
QUANTITY NUMBER )

[code]......

View 4 Replies View Related

Developer Tools :: Can Use Latest Features In Oracle 12c

Oct 2, 2013

I read previous post "Can I use the latest features in Oracle Developer Tools 11g and still use ODP.NET version 10.x or 9.x in my application?".In this the solution was: "Solution: Yes, you can use the newest 11g version of Oracle Developer Tools while developing code that relies on ODP.NET version 10g or 9i. In almost all cases it will work fine. The only exception is with the automatic .NET code generation feature. Here is what you need to do: 

a) Install both ODT 11g  *and* ODP.NET 10g or 9i. They must be placed in different Oracle Homes!
b) You will now be able to use the 11g Oracle Developer Tools for Visual Studio and all of the newest features. To ensure the correct version of ODP.NET is used by your application continue to the next steps:
c)  Make sure that in the "References" section in your solution, the Oracle.DataAccess.dll that is referenced is coming from the Oracle 10 or Oracle 9 Oracle home.
d) Finally, you must "ungac" (gacutil /u) the ODP.NET 11g policy DLL's or else your application will silently begin using the 11g version of ODP.NET even though the reference points to the correct DLL. There are several Oracle policy DLLs with varying names, for example: "Policy.2.102.Oracle.DataAccess.dll". Ungac them all." 

Are the steps the same if i have Oracle Developer Tools 12c and still use ODP.NET version 11g R2 in my application? 

View 1 Replies View Related

SQL & PL/SQL :: Taking Latest Current Balance Using Analytical Query

Jan 22, 2013

I am having a table with 5 lakhs transactions. I want to fetch the last balance for a particular date. So i have have returned a query like below.

SELECT curr_balance
FROM transaction_details
WHERE acct_num = '10'

[Code]...

This has to be executed for incrementing of 12 months to find the last balance for each particular month. But this query is having more cpu cost, 12 times it is taking huge time. how to remodify athe above query to get the results in faster way using analytical query. Whether this can be broken into two part in PL/SQL to achive the performance. ?

View 9 Replies View Related

Forms :: Updating Skills From Oracle Developer 6 To Latest

Oct 17, 2012

I have not used Oracle Forms and Reports for some time. The last version I used was Developer version 6.

Has there been major changes between Oracle Developer version 6 and Oracle Developer version 11 or is Oracle Developer basically the same with some incremental improvements? What would be a good way to update my skills from Oracle Developer version 6 and Oracle Developer version 11?

View 1 Replies View Related

SQL & PL/SQL :: Retrieving Latest Create_date And Pay_total For Each Act_id From Table?

Apr 19, 2010

I am trying to retrieving the latest create_date and pay_total for each act_id from the table. Table is having more than 5000000 records. I prepared the below query but it didn't the data even after 6 hours.

select a.act_id,a.create_DATE,a.Pay_TOTAL
from payment_tab a
where 1 = (select count(distinct b.create_DATE)
from payment_tab b
where b.act_id=a.act_id
and
b.create_DATE >= a.create_DATE)

i cannot create the indexes on this table.

View 11 Replies View Related

Server Administration :: Information Regarding Latest Patch Installed For ORACLE_HOME

Feb 23, 2010

How do i find out the information regarding the latest patch installed for ORACLE_HOME..

OR

If given a patch number, how do i find out whether it's applied to ORACLE_HOME

View 1 Replies View Related

Installation :: Install Latest Release Of Oracle Database On A Solaris 11 Zone

May 11, 2013

which is really a HA zone clustered in VCS(not important for installation of the database).

What parameters do I need to set or change in the Solaris zone, there seems to be no /etc/system file.

View 3 Replies View Related

Forms :: Populating Values To Record Group From Multi Record Datablock

Jul 26, 2012

My procedure proc_ex is in when_validate_item trigger

I have one Multi Record data block in my form with values in its items

I need to Populate multi record block values to one Record Group using

add_group_row,
add_group_column,
set_group_char_cell to populate values to record group

Let us suppose my multi record data block looks like

item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80

how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies

View 3 Replies View Related

Server Utilities :: How To Associate Header Record To Its Corresponding Detail Record

May 16, 2012

I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the

LOAD DATA
APPEND
INTO TABLE post1.thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(file_type POSITION(1:5)CHAR,

[code]....

A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).

THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002

What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?

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 ::create History Record For Each Record Whether Updated Or Not

Sep 13, 2011

I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.

No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.

Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.

View 4 Replies View Related







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