Recent content by MCubitt

Its ok, distinct did the trick, thx Applications Support UK

I have a query: select substr(ipis.contract,1,3) Company, ipis.contract, ip.type_designation, ipis.part_no, ipis.lot_batch_no, ipis.serial_no, ipis.qty_onhand, ipc.inventory_value Std_Cost, pol.buy_unit_price, (select currency_rate from currency_rate_tab cr where...

So final : [/code] select currency_rate from currency_rate_tab cr where (cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM) in (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, max(cr1.VALID_FROM) from currency_rate_tab cr1 where cr1.COMPANY='OKM' and...

Actually, it does not work! It should be ...max(cr1.valid_from)... not min. Also, should be <= not >= The way it was working was to get the rate AFTER the current. Applications Support UK

Sorry, I tried that too: 1 select currency_rate 2 from currency_rate_tab cr where 3 (cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM) 4 in 5 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, min(cr1.VALID_FROM) 6 from currency_rate_tab cr1 where 7...

Almost!!! 1 select currency_rate 2 from currency_rate_tab cr where 3 (cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM) 4 in 5 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, min(cr1.VALID_FROM) 6 from currency_rate_tab cr1 where 7 cr1.COMPANY='OKM'...

Thank you very much.. I had used brackets when it was = but not when it was IN. Thank you Applications Support UK

Yes, that looks more right, but still fails: 1 select currency_rate 2 from currency_rate_tab cr where 3 cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM 4 in 5 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, min(cr1.VALID_FROM) 6 from currency_rate_tab...

A few errors but stuck on this one: 1 select currency_rate 2 from currency_rate_tab cr where 3 cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM 4 = 5 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, min(cr1.VALID_FROM) 6 from currency_rate_tab cr1 where 7...

Dima, Thank you very much for that, I am amazed it is possible! I will try to implement.. Applications Support UK

I have a long SQL query with a number of tables: select substr(ipis.contract,1,3) Company, ipis.contract, ip.type_designation, ipis.part_no, ipis.lot_batch_no, ipis.serial_no, ipis.qty_onhand, ipc.inventory_value Std_Cost, ith.dated Receipt_Date, pol.currency_type, pol.currency_code...

...but you should not assume ALL valid_from dates are monthly. The table could be: VALID_FROM RATE 2005-01-05 10 2005-01-19 10.5 2005-02-03 9.8 2005-02-22 10.1 2005-02-28 10.2 2005-03-06 9.9 Applications Support UK

We have a table which holds exchange rates for various currencies: SQL> desc currency_rate_tab Name Null? Type ----------------------------------------- -------- ----------------- COMPANY NOT NULL VARCHAR2(20)...

I think I managed it after all! select ipis.contract, ipis.part_no, ipis.lot_batch_no, ipis.serial_no, ipis.qty_onhand, nvl( ( select ( select pol.buy_unit_price from ifsapp.purchase_order_line_tab pol where rownum = 1 and pol.order_no=ith.order_no and...

I have 4 queries and need to amalgamate them to create one set of results. The first: select ipis.part_no, ipis.contract, ipis.lot_batch_no, ipis.serial_no, ipis.qty_onhand from ifsapp.inventory_part_in_stock_tab ipis where ipis.qty_onhand > 0 This is the primary query which should use...