sequence number
Moderators: chulett, rschirm, roy
sequence number
Hi All,
How to check if,The numbers are missing a sequential numbers between 1 and 10
EX: if 1 2 3 4 6 7 8 10--> skip record(reason is they are not in sequence)
if 1 2 3 4 5 6 7 8 9 10---> accept record.
thanks in advance
How to check if,The numbers are missing a sequential numbers between 1 and 10
EX: if 1 2 3 4 6 7 8 10--> skip record(reason is they are not in sequence)
if 1 2 3 4 5 6 7 8 9 10---> accept record.
thanks in advance
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Are these numbers in a single field (and, if so, how are they delimited) in the same record, in separate fields in the same record, or in the same field in multiple records (and, if so, how is the input sorted)?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sequence
Hi DSguru,
I have 8 fields in my source table.
For each book i have different version.(book and versions are my field names)
for example
book version
1-100 0
1-100 2
1-100 4
1-200 0
1-200 1
1-100 2
i need to check the version number for a book are missing a sequential version
between min and max.
How can i implement this.
thanks in advance.
I have 8 fields in my source table.
For each book i have different version.(book and versions are my field names)
for example
book version
1-100 0
1-100 2
1-100 4
1-200 0
1-200 1
1-100 2
i need to check the version number for a book are missing a sequential version
between min and max.
How can i implement this.
thanks in advance.
So let me make sure i got this straight!
for 1-100 you are missing version 1 and 3 and for book 1-200 you are not missing any version. So you have to output
1-100 0
1-100 3
for the data sample that you gave. Correct?
Or you just have to raise a red flag if they are out of sequence.
for 1-100 you are missing version 1 and 3 and for book 1-200 you are not missing any version. So you have to output
1-100 0
1-100 3
for the data sample that you gave. Correct?
Or you just have to raise a red flag if they are out of sequence.
Last edited by DSguru2B on Fri Jun 16, 2006 3:16 pm, edited 3 times in total.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
sequence
Hi DSguru,
If there are out of sequence then i need to bypass them( i mean i need to throw of the entire 1-100)
if it is in sequence i need to accept( i.e like 1-200)
Thanks in advance.
If there are out of sequence then i need to bypass them( i mean i need to throw of the entire 1-100)
if it is in sequence i need to accept( i.e like 1-200)
Thanks in advance.
DSguru2B wrote:So let me make sure i got this straight!
for 1-100 you are missing version 1 and 3 and for book 1-200 you are not missing any version. So you have to output
1-100 0
1-100 3
for the data sample that you gave. Correct?
Or you just have to raise a red flag if they are out of sequence ?
Ok for that you cannot do it in a single transformer. You need to determine what books are in order, flag them and then in the second transformer only let them pass through.
Use the conditional checks in the first transformer to build a hashed file with only one column 'book'.
So basically in the first transformer you will have the following stage variables.
In the constraint of the output link put SeqChk = @TRUE. This link will go to a hashed file with only one column 'Book'. Define that as a key.
Now that your hashed file is ready. Let you input come in from the source table, do a lookup on this hashed file (key will be book only) and in the constraint of the output link you should have
where lkup will be the name of your reference link to the hashed file.
This solution will work because the hashed file has only those books which are in sequence.
Have a great weekend![Laughing :lol:](./images/smilies/icon_lol.gif)
Use the conditional checks in the first transformer to build a hashed file with only one column 'book'.
So basically in the first transformer you will have the following stage variables.
Code: Select all
VerChk: if in.book = prevBook or @INROWNUM = 1 then @TRUE else @FALSE
SeqChk: if VerChk = @TRUE AND in.Version = prevVer + 1 then @TRUE else @FALSE
prevBook: in.book
prevVer : in.Version
Now that your hashed file is ready. Let you input come in from the source table, do a lookup on this hashed file (key will be book only) and in the constraint of the output link you should have
Code: Select all
NOT(lkup.NOTFOUND)
This solution will work because the hashed file has only those books which are in sequence.
Have a great weekend
![Laughing :lol:](./images/smilies/icon_lol.gif)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
sequence
Hi DSguru,
how to get prevBook and preversion.
Thanks in advance
how to get prevBook and preversion.
Thanks in advance
DSguru2B wrote:So let me make sure i got this straight!
for 1-100 you are missing version 1 and 3 and for book 1-200 you are not missing any version. So you have to output
1-100 0
1-100 3
for the data sample that you gave. Correct?
Or you just have to raise a red flag if they are out of sequence.
Re: sequence
Abracadabra hocus focus
assuming that table name is books and that the column version is an integer.
:D
![Exclamation :!:](./images/smilies/icon_exclaim.gif)
![Question :?:](./images/smilies/icon_question.gif)
![Idea :idea:](./images/smilies/icon_idea.gif)
![Arrow :arrow:](./images/smilies/icon_arrow.gif)
Code: Select all
select * from books where book in (select t.book countversion from books t
where t.version between #start_version# and #end_version#
group by t.book having count(distinct t.version) = #end_version# - #start_version# + 1)
:D
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
Re: sequence
i have shown it in my stage variables.adams06 wrote: how to get prevBook and preversion.
when i say in.book, that means your column 'book' and 'in' is for input, so in other words, your input column 'book', similarly for your version.
From your input just drag and drop the book and version to these two stage variables.
Last edited by DSguru2B on Fri Jun 16, 2006 3:32 pm, edited 1 time in total.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Re: sequence
Nice. I like that. How is he going to pass the start_version and end_version to the sqlsud wrote:Abracadabra hocus focus![]()
![]()
![]()
![]()
assuming that table name is books and that the column version is an integer.Code: Select all
select * from books where book in (select t.book countversion from books t where t.version between #start_version# and #end_version# group by t.book having count(distinct t.version) = #end_version# - #start_version# + 1)
:D
![Laughing :lol:](./images/smilies/icon_lol.gif)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Re: sequence
Those will be job parameters isnt it. :D
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.