Joining in only "one of many" rows

[thrybergh]

Hello.

I have a query which is pulling out several columns, using about 6 tables to produce an asset inventory list. (I'm using Oracle Financials tables actually).

I would like to join in a new column ("Purchase order number&quot from a new table ("ASSET_INVOICES&quot. I need to join using the "Asset ID" since this is the only link between the "ASSET_INVOICES" table and the others. The problem is that in the "ASSET_INVOICES" table there are usually 4 rows per asset and the "purchase order number" is not always there.

I am having difficulty joining in the table without multiplying the value of the assets by this number of rows.

What I am trying to achieve is to select the po_number once for the asset, if one exists, and just return a null/blank if it doesn't.

If I select the max(po_number) with a sub-select statement then it fails to select the rows which do not have a po_number at all in them. Am I joining incorrectly, and should I use some other outer join or something?


This is the bit in question:

                ainv.po_number = 
                (SELECT max(ainv2.po_number)
                FROM fa_asset_invoices ainv2
                WHERE ainv2.asset_id = ad.asset_id)

I would be grateful if anyone can offer advice! [smiley]

SELECT 
                cc.segment2,
	ad.asset_id,     
	ad.asset_number,       
	ad.asset_key_ccid,     
	ad.asset_type,         
	SUBSTR(ad.description, 1, 60) DESCRIP,
	ad.manufacturer_name,  
	ad.serial_number,      
	ad.tag_number,
	loc.segment1||'.'||
	loc.segment2||'.'||
	loc.segment3||'.'||
	loc.segment4 LOCATION,
        SUM(DECODE(dd.deprn_source_code, 'B', dd.addition_cost_to_clear, dd.cost)) COST,
        (SUM(DECODE(dd.deprn_source_code, 'B', dd.addition_cost_to_clear, dd.cost)) - sum(dd.deprn_reserve)) NBV,
        emp.full_name OWNER,
        books.date_placed_in_service DATE_USE,
        ainv.po_number po_number
FROM
        fa_additions            ad,
        fa_locations            loc,
        gl_code_combinations    cc,
        fa_distribution_history dh,
        fa_deprn_detail         dd,
        per_people_f            emp,
        fa_books books,
        fa_asset_invoices ainv
WHERE
        dh.asset_id            = ad.asset_id
AND    
        dh.location_id         = loc.location_id
AND
        dh.code_combination_id = cc.code_combination_id
AND
	dd.asset_id = dh.asset_id
AND
	dh.assigned_to         = emp.person_id(+)
AND
	dd.asset_id = dh.asset_id             
AND
	dd.book_type_code = dh.book_type_code      
AND
	dd.distribution_id = dh.distribution_id
AND
	books.asset_id = dh.asset_id
AND
                ainv.po_number = 
                (SELECT min(ainv2.po_number)
                FROM fa_asset_invoices ainv2
                WHERE ainv2.asset_id = ad.asset_id)
AND
	dd.period_counter = 
   (SELECT    max (dd2.period_counter)
    FROM    fa_deprn_detail dd2
    WHERE dd2.book_type_code = books.book_type_code and
        	dd2.asset_id = books.asset_id and
	dd2.distribution_id = dd.distribution_id)
AND
	cc.segment2 >= :P1 AND cc.segment2 <= :P2
GROUP BY
                cc.segment2, 
	ad.asset_id,     
	ad.asset_number,       
	ad.asset_key_ccid,     
	ad.asset_type,         
	SUBSTR(ad.description, 1, 60),
	ad.manufacturer_name,  
	ad.serial_number,      
	ad.tag_number,
	loc.segment1||'.'||
	loc.segment2||'.'||
	loc.segment3||'.'||
	loc.segment4,
        emp.full_name,
        books.date_placed_in_service,
        ainv.po_number
ORDER BY 2

 

[nread]

You need to use an outer join.....

table1.col = table2.col(+) -- This will force a result if the table is deficient in information i.e. it will return Null....

 

[thrybergh]

Thanks but I'm still stuck...

I am "joining" (I'm really using a subselect) to extract a value for PO_NUMBER from ASSET_INVOICES. The ASSET_ID always exists in both the ASSET_INVOICES and ADDITIONS tables.

I don't understand how to work in the outer join so it gives me what I want:

AND ainv.po_number =
(SELECT max(ainv2.po_number)
FROM fa_asset_invoices ainv2
WHERE ainv2.asset_id = ad.asset_id)

AINV.PO_NUMBER may be null or it may have a value. I want one row to be returned only.

 

[nread]

Use NVL(max(ainv2.po_number),'NULL')

This will return a Null in that field if it does not exist......

 

[Dagon]

Why don't you use an inline view and outer join to that e.g.

select
..
FROM
fa_additions ad,
fa_locations loc,
gl_code_combinations cc,
fa_distribution_history dh,
fa_deprn_detail dd,
per_people_f emp,
fa_books books,
(SELECT asset_id, min(po_number)
FROM fa_asset_invoices
group by asset_id) ainv
where ad.asset_id = ainv.asset_id (+)
...

 

[thrybergh]

I tried that "inline view" - never heard of that before, but it complained about not being able to add the table "SELECT". I'm actually using 7.3.4 so I wonder if it's a version problem.

Thanks anyway.

 

[jee]

Yes - you can't do that in 7.3.4

BTW - if you are using 7.3.4, why did you post this question in the Oracle8/8i forum?