I have a table, which contains relations between, let's call them "symbols". Looks like this:
I want to prevent user from saving data when inputs 2 records like marked on the screen (symbol => symbol_to, and in another symbol_to => symbol). We ignoring type column, i want to search only symbol/symbol_to.
My only idea is to loop over every record, and for every record loop over all records and check for column values, BUT if this table grows too much, that could be not efficient - f.e. for 500 records is 500x500...
Mayby someone has another idea?
EDIT: another idea i came up with - get data from DW, put into temporary table in oracle, run select which is simple, and retrieve it. But mayby is a way to do this in PB?
You can loop through all rows and for each row use Find function to try to find a "duplicate" row. I think you need to begin search only after the row you are running on. This should be faster then looping over all records for each record.
FOR ll_row = 1 TO ll_rows - 1 ll_find = dw_1.Find ("symbol='" + dw_1.GetItemString (ll_row, "symbol_to") + "' AND symbol_to = ' + dw_1.GetItemString (ll_row, "symbol") + '", ll_row + 1, ll_rows) IF ll_find > 0 THEN // duplicate found END IF NEXT
If you only need to know whether there are such duplicates you can do it with a computed field (or evaluate expression) using distinct count. This only works well if you put the symbols in a fixed order. The expression could be
count (symbol for all distinct if (symbol < symbol_to, symbol + "$$" + symbol_to, symbol_to + "$$" + symbol))
If the count is different from rowcount you have duplicates (but you don't know what).
You can use this also with dw grouping functionality. You could order and group (e.g. in a second dw) by
if (symbol < symbol_to, symbol + "$$" + symbol_to, symbol_to + "$$" + symbol)
and find the groups that have more then one row. In this case you also know the duplicates.
This should also work without grouping. Sort by the same expression and then find by
if (symbol < symbol_to, symbol + "$$" + symbol_to, symbol_to + "$$" + symbol) = if (symbol[-1] < symbol_to[-1], symbol[-1] + "$$" + symbol_to[-1], symbol_to[-1] + "$$" + symbol[-1])
- Michał Misijuk
- Tuesday, 19 April 2022 12:19 PM UTC
1.count (symbol for all distinct if (symbol < symbol_to, symbol + "$$" + symbol_to, symbol_to + "$$" + symbol)) - this should be put directly in the DW?
2. dw grouping functionality - I'm not failmiar with such functionality. Is it also in the DW ?
- René Ullrich
- Tuesday, 19 April 2022 12:54 PM UTC
Grouping is a datawindow functionality. It is a special datawindow style but you can also create groups for tabular or grid datawindow (menu Rows->Create group) You can group by columns, computed fields or expression. All rows that match the group specification are in one group. The next not matching row starts a new group. Because of this it is importent that you correctly set the datawindow sort! With grouping you can use aggregate functions like count only for groups, e.g count (mycol for group 1) counts the number of rows in each group of level 1 (you can have different group levels - level 2 is a sublevel of level 1).
Let's say we have a datawindow with the following columns:
- issue_flag (with default value 0)
I do add a computed field (compute4check) with the folowing expression:
if( symbol_from > symbol_to , symbol_to + '-' +symbol_from , symbol_from + '-' +symbol_to )
This will create a concatenation of symbol_from and symbol_to always placing the "smaller" one to the beginning.
So ABC -> DEF and DEF -> ABC will both become "ABC-DEF". This run on the fly.
When I have all my records, all I have to do is to sort my data based on computed field.
After that I can loop to all the rows. If I find any row where computed field for [row] = computed field for [row + 1], I know it is wrong. Whenever I find a row where this condition is valid I do put value 1 to issue_flag.
All rows with issue_flag = 1 are to be reviewed.
- René Ullrich
- Tuesday, 19 April 2022 14:18 PM UTC
PowerBuilder can also do a find (find is much more faster) to check for difference of one row with the previous row. But I think this only works for columns not for computed fields. See the last example in my post to see how to do this.
FWIW: personally, I've implemented this many times using the Filter() & RowCount() commands on a DataWindow or DataStore. Super fast & easy. Food for thought. ;-)
Regards ... Chris
What is the list of allowed symbols? It's single letters or it could be something more complex?
- Michał Misijuk
- Tuesday, 19 April 2022 11:31 AM UTC
a 1 b
a 2 b
it is a configuration error, so we skip it
- Page :
However, you are not allowed to reply to this question.