Home » SQL & PL/SQL » SQL & PL/SQL » pls help (oracle 9i)
pls help [message #357772] Thu, 06 November 2008 08:58 Go to next message
Srinandini
Messages: 13
Registered: November 2008
Junior Member
Hi All

I have 2 tables po and po_hist
i need to pick up the po numbers from po table that have the highest
po version numbers that is stored in po_hist table

Along with the above fields some more cols need to be fetched
I have used a join

Can u pls help me with this?

Select 
 DPO.logical_business_unit ,
 DPO.po_number,
 SWS.upc,
 GPR.prim_size_desc,
 GSS.sec_size_desc,
 DPO.accepted_launch_order_qty,
 SWS.last_full_selling_price
from 
cms_db_purchase_order DPO,
cms_season_ways SWS,
cms_grd_primary_sizes GPR,
cms_grd_secondary_sizes GSS,
cms_db_intl_hubs DIH,
cms_db_purchase_order_hist poh ,
 ( select    max(poh.po_version_num)  as  max_po_version_num 
   from      cms_db_purchase_order_hist poh, 
             cms_db_purchase_order dpo 
   where     dpo.po_number=poh.po_number 
   and       poh.po_status='A'
   group by  poh.po_number having count(*)>1) sq
where DPO.year              = SWS.year
and  DPO.season_type        = SWS.season_type
and  DPO.Dept_num           = SWS.dept_num
and  DPO.stroke_num         = SWS.stroke_num
and  DPO.colour_code        = SWS.colour_code
and  SWS.prim_size_index    = GPR.prim_size_index
and  SWS.sec_size_index     = GSS.sec_size_index
and  intl_hub_flag          = 'Y’ 
and  DIH.hub_code           = DPO.final_warehouse_code
and  (DPO.po_status         = 'A' or 
      DPO.has_been_accepted = 'Y')
and  DPO.po_status         != 'C'
and  dpo.po_number          = poh.po_number
and  DPO.Version_num        in (sq.max_po_version_num)
and  DPO.Number_launch_packs= 0
and  DPO.Accepted_launch_order_qty is NULL


[ Formatted by Raj ; Please do it from next time ]

thanks in advance
Sri Smile

[Updated on: Thu, 06 November 2008 22:52] by Moderator

Report message to a moderator

Re: pls help [message #357779 is a reply to message #357772] Thu, 06 November 2008 09:32 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Srinandini,

Please read OraFAQ Forum Guide especially on "How To Format Your Post?".

I didn't understand your requirement properly. Infact the query you gave bit confused me. I will have another look though.Can you please post a test case (create table and insert statements) with the expected outputs?

Regards,
Jo
Re: pls help [message #357843 is a reply to message #357779] Thu, 06 November 2008 22:07 Go to previous messageGo to next message
Srinandini
Messages: 13
Registered: November 2008
Junior Member
Am extremely sorry Jo

Am very much new to these forums

Pls ignore the other fields in the query

pls consider 2 tables

CMS_DB_PURCHASE_ORDER

Po_number ->PK
...

CMS_DB_PURCHASE_ORDER_HISt

po_number
po_version_num

Requirement isto pick all the Po's from cms_db_purchase_order table that have the highest version numbner in the history table


Pls let me knw if additional information is required

thanks in advance
Sri
Re: pls help [message #357847 is a reply to message #357772] Thu, 06 November 2008 22:43 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Do you thing information provided is enough to solve the issue as you have some more table in the query . Also pleas format the query from next time using code tag.


Btw, what is the error you are getting ?

Smile
Rajuvan.
Re: pls help [message #357877 is a reply to message #357843] Fri, 07 November 2008 00:28 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Srinandini,

As rajavu1 mentioned where are you getting the error? Also please provide some test case as I have already mentioned. I can't make out much from your query. Anyways hope the following example gives some help:

SQL> WITH TEST_TAB AS
  2  (SELECT 'A' Col_1, 1 Col_2 FROM Dual
  3  UNION ALL
  4  SELECT 'B' , 2 FROM Dual
  5  UNION ALL
  6  SELECT 'C', 3 FROM Dual
  7  UNION ALL
  8  SELECT 'D', 3 FROM Dual
  9  )
 10  SELECT * from TEST_TAB;

C      COL_2
- ----------
A          1
B          2
C          3
D          3

SQL> ed
Wrote file afiedt.buf

  1  WITH TEST_TAB AS
  2  (SELECT 'A' col_1, 1 col_2 FROM Dual
  3  UNION ALL
  4  SELECT 'B' , 2 FROM Dual
  5  UNION ALL
  6  SELECT 'C', 3 FROM Dual
  7  UNION ALL
  8  SELECT 'D', 3 FROM Dual
  9  )
 10  SELECT * from TEST_TAB
 11* WHERE col_2 = (SELECT MAX(col_2) FROM TEST_TAB)
 12  ;

C      COL_2
- ----------
C          3
D          3


Regards,
Jo
Re: pls help [message #357896 is a reply to message #357772] Fri, 07 November 2008 01:18 Go to previous message
shobanasree
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
Hi Srinandini,

Please tell me if below is your requirement:

create table CMS_DB_PURCHASE_ORDER ( po_number number primary key);

insert into cms_db_purchase_order values (1001);
insert into cms_db_purchase_order values (1002);
insert into cms_db_purchase_order values (1003);

create table CMS_DB_PURCHASE_ORDER_HISt (po_number number reference by CMS_DB_PURCHASE_ORDER ( po_number),
po_version_num);

insert into CMS_DB_PURCHASE_ORDER_HISt values (1001, 1);
insert into CMS_DB_PURCHASE_ORDER_HISt values (1001, 2);
insert into CMS_DB_PURCHASE_ORDER_HISt values (1001, 3);

insert into CMS_DB_PURCHASE_ORDER_HISt values (1002, 1);
insert into CMS_DB_PURCHASE_ORDER_HISt values (1002, 2);

insert into CMS_DB_PURCHASE_ORDER_HISt values (1003, 1);
insert into CMS_DB_PURCHASE_ORDER_HISt values (1003, 2);
insert into CMS_DB_PURCHASE_ORDER_HISt values (1003, 3);
insert into CMS_DB_PURCHASE_ORDER_HISt values (1003, 4);

select po_number
from cms_db_purchase_order
where po_number in (select po_number
from CMS_DB_PURCHASE_ORDER_HISt
where po_version_num = (select max
(po_version_num)
from
CMS_DB_PURCHASE_ORDER_HISt);

Take it as an example. Test your code based on this example and tell me is it OK.
Previous Topic: buffer overflow
Next Topic: ORA-39775: direct path API commit not allowed due to previous fatal error
Goto Forum:
  


Current Time: Sat Dec 10 14:28:31 CST 2016

Total time taken to generate the page: 0.06380 seconds