I have the following sample Oracle APEX report by where the Origin ID and Origin Name are retrieved from the origin_tab(id,origin_id,origin_name)
Based on these two column values, I need to generate the Temporary Origin ID value on the fly as part of the selection retrieval from the origin_tab
.
The rule behind this is – where the Origin Name is the same against the Origin ID, take the Origin ID value before the dash and append a 3-digit sequence for each Orgin ID as per example below.
Since the origin name (AAA) is the same for the first two records, ‘001
‘ is appended to the origin id 1111
. The same for BBB, ‘002’ is appended to those three records and so forth.
Please note that the Temporary Origin ID
here is a string.
Origin ID Origin Name Temporary Origin ID ----------------- ------------------ -------------------- 1111-1 AAA 1111001 1111-2 AAA 1111001 1111-3 BBB 1111002 1111-4 BBB 1111002 1111-5 BBB 1111002 1111-6 CCC 1111003 1111-7 DDD 1111004 1111-8 DDD 1111004
What approach should I take in my query to achieve the above result, for the Temporary Origin ID?
Advertisement
Answer
Use dense_rank()
:
select t.*, (substr(origin_id, 1, 4) || lpad(dense_rank() over (order by origin_name), 4, '0') ) as temp_origin_id from t;
Here is a db<>fiddle.