Home » SQL & PL/SQL » SQL & PL/SQL » Latest Transaction From Diffrent Table
Latest Transaction From Diffrent Table [message #236401] Wed, 09 May 2007 21:06 Go to next message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

Dear All,

I have three table named Ref_master, Purchase_detail, Sales_return_detail.

In Ref_master table there is opening_stock, Opening_rate, Opening_date field.

In Purchase_detail table there is quantity, pur_rate, pur_date field

In sales_return_detail there is quantity, return_rate, return_date field

I want the latest date's transaction out of this

How to do it?

Thx in adavance

Samit gandhi


Re: Latest Transaction From Diffrent Table [message #236421 is a reply to message #236401] Wed, 09 May 2007 23:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I should have a look at UNION ALL and MAX.

Regards
Michel
Re: Latest Transaction From Diffrent Table [message #236457 is a reply to message #236401] Thu, 10 May 2007 01:05 Go to previous messageGo to next message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

I TRIED BUT CANT GET SUCCESS.

PLS HELP ME

SAMIT GANDHI
Re: Latest Transaction From Diffrent Table [message #236464 is a reply to message #236457] Thu, 10 May 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DON'T SHOUT.

We help you if you shout you'll be on your own.

And post what you tried.
And your Oracle version.
And don't forget to format.
But you know that as you already read the sticky before posting.

Regards
Michel
Re: Latest Transaction From Diffrent Table [message #236473 is a reply to message #236464] Thu, 10 May 2007 01:21 Go to previous message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

Dear Michel,

Sorry if you feel bad but i have tried like this :

SELECT z.rate_usd
     , b.ref_no
FROM   (SELECT t.rate_usd
             , a.ref_no
             , t.company_id
             , RANK () OVER (PARTITION BY t.ref_code ORDER BY SYSDATE
              , t.rate_usd DESC) rnk1
        FROM   (SELECT purchase_detail.ref_code
                     , rate_usd rate_usd
                     , voucher_date
                     , purchase_detail.company_id
                     , ref_no
                     , RANK () OVER (PARTITION BY purchase_detail.ref_code ORDER BY voucher_date
                      , rate_usd DESC) rnk
                FROM   purchase_detail
                     , purchase_master
                     , ref_master
                WHERE  purchase_master.sr_no = purchase_detail.sr_no
                AND    purchase_master.company_id = purchase_detail.company_id
                AND    purchase_detail.ref_code = ref_master.ref_code
                AND    purchase_detail.company_id = ref_master.company_id
                AND    purchase_master.company_id = 1
                AND    purchase_master.voucher_date < '01-apr-07'
                UNION
                SELECT lot_transfer_to.ref_code
                     , lot_transfer_to.rate_usd rate_usd
                     , voucher_date
                     , lot_transfer_to.company_id
                     , ref_master.ref_no
                     , RANK () OVER (PARTITION BY lot_transfer_to.ref_code ORDER BY voucher_date
                      , lot_transfer_to.rate_usd DESC) rnk
                FROM   lot_transfer_to
                     , lot_mixing
                     , ref_master
                WHERE  lot_mixing.voucher_no = lot_transfer_to.voucher_no
                AND    lot_mixing.company_id = lot_transfer_to.company_id
                AND    lot_transfer_to.ref_code = ref_master.ref_code
                AND    lot_transfer_to.company_id = ref_master.company_id
                AND    lot_mixing.company_id = 1
                AND    lot_mixing.voucher_date < '01-apr-07'
                UNION
                SELECT sales_return_detail.ref_code
                     , sales_return_detail.rate_usd rate_usd
                     , voucher_date
                     , sales_return_detail.company_id
                     , ref_master.ref_no
                     , RANK () OVER (PARTITION BY sales_return_detail.ref_code ORDER BY voucher_date
                      , sales_return_detail.rate_usd DESC) rnk
                FROM   sales_return_detail
                     , sales_return
                     , ref_master
                WHERE  sales_return.sr_no = sales_return_detail.sr_no
                AND    sales_return.company_id =
                                                sales_return_detail.company_id
                AND    sales_return_detail.ref_code = ref_master.ref_code
                AND    sales_return_detail.company_id = ref_master.company_id
                AND    sales_return.company_id = 1
                AND    sales_return.voucher_date < '01-apr-07'
                UNION
                SELECT ref_code
                     , NVL (opening_rate_usd, 0) rate_usd
                     , TO_DATE ('01-apr-06', 'dd-mon-yy') voucher_date
                     , TO_NUMBER (ref_master.company_id) company_id
                     , ref_no
                     , RANK () OVER (PARTITION BY ref_code ORDER BY '01-apr-06' DESC)
                                                                          rnk
                FROM   ref_master
                WHERE  ref_master.company_id = 1) t
             , ref_master a
        WHERE  rnk = 1 AND a.ref_no = t.ref_no AND a.company_id = t.company_id) z
     , ref_master b
WHERE  rnk1 = 1 AND b.ref_no = z.ref_no AND b.company_id = z.company_id


I am using the version Oracle 9i

Thanks and once again sorry

Samit Gandhi

[mod-edit]Added code tags. Do so yourself next time.

[Updated on: Thu, 10 May 2007 01:26] by Moderator

Report message to a moderator

Previous Topic: Please help
Next Topic: What is INSTEAD OF TRIGGER
Goto Forum:
  


Current Time: Sat Dec 03 08:05:40 CST 2016

Total time taken to generate the page: 0.19720 seconds