Oracle-L: Help sorting out SQL statement

From: Gamini Karunaratne <Gamini.Karunaratne_at_alphawest.com.au>
Date: Thu, 30 Oct 2003 17:14:25 -0800
Message-ID: <F001.005D51C5.20031030171425@fatcity.com>

I need help in sorting out a tricky sql statement as follows: Statement:
SELECT /*+RULE */ distinct rfx.rfx_id , rfx.type_id , supp.supplier_id ,

decode ( nvl(rrfx.state_id, 0) , 1, 'Responded', 3, 'Responded', 6, 'Responded', 7, 'Responded', supp.read_state_id) as full_read_state,

rfx.subject , TO_CHAR(LOG.date_changed, 'DD/MM/YY') , TO_CHAR(LOG.date_changed, 'HH24:MI') ,

rfx.buyer_document_id , rfx.buyer_org_guid , rfx.buyer_org_unit_display_name , NULL , supp.supplier_guid , supp.supplier_id ,

(SELECT COUNT(rrfx.rrfx_id) FROM tx_rrfx_document rrfx WHERE rrfx.rfx_id
= rfx.rfx_id AND rrfx.state_id >= 3 and rrfx.state_id <> 999),

STATE.ORDER_ID , LOG.date_changed , (SELECT COUNT(*) FROM TX_RFX_ATTACHMENT WHERE RFX_ID = rfx.RFX_ID)

FROM tx_rfx_document rfx , tx_rfx_supplier supp , tx_rfx_state_log log , TX_RFX_VIEWER_STATES STATE, tx_rrfx_document rrfx

WHERE rfx.rfx_id = supp.rfx_id

AND supp.READ_STATE_ID = STATE.READ_STATE_ID

AND LOG.rfx_id = rfx.rfx_id

AND supp.supplier_id = rrfx.supplier_id (+)

AND log.log_id =(select MAX(log3.log_id) from tx_rfx_state_log log3 where log3.rfx_id = rfx.rfx_id and LOG3.to_state_id IN (SELECT MAX(log2.to_state_id) FROM tx_rfx_state_log log2 WHERE log2.rfx_id = rfx.rfx_id AND (log2.to_state_id = 9 OR log2.to_state_id = 12)))

AND supp.read_state_id <> 'Deleted'

AND nvl(rrfx.state_id, 0) <> 999

and rfx.rfx_id = 12619

ORDER BY STATE.ORDER_ID, full_read_state;

The output I get is as follows:

RFX_ID TYPE_ID SUPPLIER_ID FULL_READ_STATE SUBJECT etc etc

12619             1                1207                   Full

homing

12619              1               1205                   Normal

homing

12619              1               1209                   Normal

hourly

How is if possible for me to change the script so that my output is a unique rfx_id and only the maximum supplier_id is output (ie supplier_id 1209)

Any help much appreciated.

Gamini

Alphawest Disclaimer

If this communication is not intended for you and you are not an authorised recipient of this email you are prohibited by law from dealing with or relying on the email or any file attachments. This prohibition includes reading, printing, copying, re-transmitting, disseminating, storing or in any other way dealing or acting in reliance on the information. If you have received this email in error, we request you contact Alphawest immediately by returning the email to postmaster_at_alphawest.com.au and destroy the original. This email is confidential and may contain privileged client information. Alphawest has taken reasonable steps to ensure the accuracy and integrity of all its communications, including electronic communications, but accepts no liability for materials transmitted.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gamini Karunaratne
  INET: Gamini.Karunaratne_at_alphawest.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Thu Oct 30 2003 - 19:14:25 CST