Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to achive the missing sequence id's in a table.
In SQL it can be done using Connect by level.
For example
In My table I have following Data
Seq_id FileName Date
2012 A 2-jan-2013
2013 A 10-oct-2012
2014 A 5-Jun-2013
2016 A 15-May-2013
2018 A 19-Jul-2013
I want the output as:-
Missing Sequence
2015
2017
Please help me in this.
Hi rakhi,
please take a look at the following code:
Seq:
LOAD * Inline [
ID
2011
2012
2014
2016
];
MinMAx:
LOAD Min(ID) AS MinID, Max(ID) AS MaxID Resident Seq;
Let vMin= Peek('MinID');
Let vMax= Peek('MaxID');
RefTab:
LOAD
$(vMin) + IterNo() AS All_IDs
AutoGenerate(1)
While $(vMin) + IterNo() <= $(vMax)
;
Missing:
LOAD
All_IDs AS MissingVal
Resident RefTab
Where not Exists(ID, All_IDs)
;
Regards
Roland