Home » Other » Test » Which is easier to read?
Which is easier to read? [message #434908] Fri, 11 December 2009 10:57
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT BATCH_REC_TYPE1(batch_guid,xml_filename,subscriber_id) batch_rec BULK COLLECT INTO BATCH_TABLE_TYPE_TAB
FROM acknowledgement_queue a
WHERE batch_guid IN (SELECT v.batch_guid FROM
(SELECT batch_guid,
min(next_dttm) next_dttm
FROM acknowledgement_queue aq
WHERE next_dttm <= sys_extract_utc(current_timestamp)
AND((status IN ('P', 'R') and process_name IS NULL and host_name is null)
OR
(status='I' and host_name=i_host_name and process_name=i_process_name)
)
AND retry_count <=
(SELECT max_retry_count
FROM acknowledgement_parameters)
AND EXISTS (SELECT '1'
FROM acknowledgement_subscribers
WHERE subscriber_id = aq.subscriber_id
AND ack_send_flag = 'Y'
AND business_unit_code = i_business_unit_code)
GROUP BY batch_guid
ORDER BY next_dttm
) v
WHERE ROWNUM <= NVL(i_set_size,0)
)
AND EXISTS (SELECT '1' FROM acknowledgement_subscribers
WHERE subscriber_id = a.subscriber_id
AND ack_send_flag = 'Y'
AND business_unit_code = i_business_unit_code)
AND a.status IN ('P','R','I')
ORDER BY next_dttm,batch_guid
FOR UPDATE NOWAIT;

SELECT   Batch_rec_type1(batch_guid,xml_filename,subscriber_id) batch_rec
BULK COLLECT INTO batch_table_type_tab
FROM     acknowledgement_queue a
WHERE    batch_guid IN (SELECT v.batch_guid
                        FROM   (SELECT   batch_guid,
                                         Min(next_dttm) next_dttm
                                FROM     acknowledgement_queue aq
                                WHERE    next_dttm <= Sys_extract_utc(current_timestamp)
                                         AND ((status IN ('P','R')
                                               AND process_name IS NULL
                                               AND host_name IS NULL)
                                               OR (status = 'I'
                                                   AND host_name = i_host_name
                                                   AND process_name = i_process_name))
                                         AND retry_count <= (SELECT max_retry_count
                                                             FROM   acknowledgement_parameters)
                                         AND EXISTS (SELECT '1'
                                                     FROM   acknowledgement_subscribers
                                                     WHERE  subscriber_id = aq.subscriber_id
                                                            AND ack_send_flag = 'Y'
                                                            AND business_unit_code = i_business_unit_code)
                                GROUP BY batch_guid
                                ORDER BY next_dttm) v
                        WHERE  ROWNUM <= Nvl(i_set_size,0))
         AND EXISTS (SELECT '1'
                     FROM   acknowledgement_subscribers
                     WHERE  subscriber_id = a.subscriber_id
                            AND ack_send_flag = 'Y'
                            AND business_unit_code = i_business_unit_code)
         AND a.status IN ('P','R','I')
ORDER BY next_dttm,
         batch_guid
FOR UPDATE NOWAIT; 
Previous Topic: Which is easier to read?
Next Topic: test code
Goto Forum:
  


Current Time: Thu Mar 28 10:57:48 CDT 2024