How to perform order by clause in the temp table before inserting into main target table

Database

How to perform order by clause in the temp table before inserting into main target table

Hi All,

This is my requirement.

I am creating a temp_dumy table from two tables ind and rep:

CREATE MULTISET TABLE test.temp_dummy2 (facility_num, utilization_sequence_num, source_system_id, source_system_facility_id,
source_system_utilization_num, allocator_facility_num, credit_system_id, loan_system_id,
customer_SOR_bank_num, orig_transaction_code, transaction_code, transaction_amt, booking_unit,
uca_line_num, obligor_num, obligation_id, credit_taker_gci, post_date, effective_date, period_date,
business_date, as_of_date, last_update_event_ts, last_update_user_id, FacilityKey, currencyCode,
oldBACpostingUnit, oldBACuca) AS
(
select
COALESCE(s.facilityNum, 0),
COALESCE(s.utilizationSequenceNum,0),
'CORE', /* source_system_id */
COALESCE(s.sourceSystemFacilityID,' '),
COALESCE(s.sourceSystemUtilizationNum,(COALESCE(i.sourceSystemUtilizationNum,' '))),
0, /* allocator facility num */
COALESCE(s.sourceSystemID,' '),
COALESCE(s.loanSystem,' '),
COALESCE(s.postingBank,' '),
COALESCE(s.nativeTransactionCode,' '),
COALESCE(s.transactionCode,' '),
COALESCE(s.coreAmount,0.00),
COALESCE(s.postingBank ,' '),
COALESCE(s.glAccount,' '),
COALESCE(s.obligorNumber,' '),
COALESCE(s.obligationNumber,' '),
COALESCE( i.borrowerGCI,(COALESCE(s.borrowerGCI,' '))),
COALESCE(s.postingDate ,s.closeDate), /* post_date */
COALESCE(s.transactionDate, s.closeDate), /* effective_date */
s.closeDate, /* period_date */
'2006-06-12', /* business_date */
'2006-06-12', /* as_of_date */
s.lastEventTS,
'LOAD',
s.FacilityKey,
s.currencyCode,
s.oldBACpostingUnit,
s.oldBACuca
from t.ind i left outer join .rep s on
s.linkIndicatives = i.linkIndicatives
and s.dateInactive is null
and s.closeDate >= '1999-12-31'
and i.dateInactive is null

After that I need to insert the data to my main table BASE
but before that I need to order by sourceSystemUtilizationNum .

If I do :

insert into test.base
select * from test.temp_dumy order by sourceSystemUtilizationNum

Error comes: order by not allowed in subqueries.

Can anybody tell me how to order by before inserting into target table.

1 REPLY
Enthusiast

Re: How to perform order by clause in the temp table before inserting into main target table

Mahek,

Why do you want to order the rows before inseting into the BASE table. How does it matter whether you order the rows while/before inserting them into the target table. When inserted, the rows will anyway be ordered in the order of Hash of PI column rather than order of column values..can you explain your requirement correctly.