Customer and Address Rowset

Customer and Address Rowset
 1# This script shows how to produce rowsets and counts of CUSTOMER data with and without primary ADDRESS details.
 2
 3ot = impact.gui.output_toolbox
 4ot.clear()
 5
 6RowCountOpts = impact.creator.row_count_options()
 7CountOpts = impact.creator.count_items_options()
 8cond = impact.creator.sql_filter_condition()
 9
10# Count customers with name containing wint
11cond.column = "CUSTOMER.CS_NAME"
12cond.operator = "like"
13vals = ["wint"]
14cond.values = vals
15conds = [cond]
16CountOpts.sql_filter_conditions = conds
17RowCountOpts.count_items_options = CountOpts
18RowCountOpts.primary_table_name = "CUSTOMER"
19q_count = impact.active_database.count_total_rows(RowCountOpts)
20ot.add("Count customers CS_NAME like wint: " + str(q_count))
21
22# Count customers with name containing ard
23vals = ["ard"]
24cond.values = vals
25q_count = impact.active_database.count_total_rows(RowCountOpts)
26ot.add("Count customers CS_NAME like ard: " + str(q_count))
27
28# Define the customer primary address column using a relationship.
29# NB without this, the count below will be wrong because the filter on A_CITY will be ignored.
30RowCountOpts.use_relationship_lookup("CUSTOMER.CS_PRIADDR->ADDRESS.A_KEY")
31
32# Count customers with address city containing skip
33cond.column = "ADDRESS.A_CITY"
34vals = ["skip"]
35cond.values = vals
36q_count = impact.active_database.count_total_rows(RowCountOpts)
37ot.add("Count customers A_CITY like skip: " + str(q_count))
38
39# Count customers with address description containing bill
40cond.column = "ADDRESS.A_DESCRPT"
41vals = ["bill"]
42cond.values = vals
43q_count = impact.active_database.count_total_rows(RowCountOpts)
44ot.add("Count customers A_DESCRPT like bill: " + str(q_count))
45
46# Find rowset of CUSTOMER columns with CS_NAME containing wint
47RowsetOpts = impact.creator.rowset_options()
48
49cond.column = "CUSTOMER.CS_NAME"
50vals = ["wint"]
51cond.values = vals
52RowsetOpts.find_items_options.sql_filter_conditions = conds
53RowsetOpts.primary_table_name = "CUSTOMER"
54RowsetOpts.column_names = "CUSTOMER.CS_NAME;CUSTOMER.CS_CODE"
55rowset = impact.active_database.find_rowset(RowsetOpts)
56ot.add("")
57ot.add("Rowset of CUSTOMER columns with CS_NAME like wint : ")
58ot.add(rowset)
59
60# Define the customer primary address column using a relationship.
61# NB without this, the rowsets below will fail with invalid column.
62RowsetOpts.use_relationship_lookup("CUSTOMER.CS_PRIADDR->ADDRESS.A_KEY")
63
64# Find rowset of CUSTOMER and ADDRESS columns with CS_NAME containing wint
65RowsetOpts.column_names = "CUSTOMER.CS_NAME;CUSTOMER.CS_CODE;ADDRESS.A_CITY"
66rowset = impact.active_database.find_rowset(RowsetOpts)
67ot.add("")
68ot.add("Rowset of CUSTOMER and ADDRESS columns with CS_NAME like wint : ")
69ot.add(rowset)
70
71# Find rowset of CUSTOMER and ADDRESS columns with A_CITY containing p ordered by A_CITY asc
72RowsetOpts.column_names = "CUSTOMER.CS_NAME;CUSTOMER.CS_CODE;ADDRESS.A_CITY"
73cond.column = "ADDRESS.A_CITY"
74vals = ["p"]
75cond.values = vals
76RowsetOpts.find_items_options.order_by = "ADDRESS.A_CITY"
77rowset = impact.active_database.find_rowset(RowsetOpts)
78ot.add("")
79ot.add("Rowset of CUSTOMER and ADDRESS columns with A_CITY like p (sort A_CITY asc) : ")
80ot.add(rowset)
81
82# Find rowset of CUSTOMER and ADDRESS columns with A_CITY containing p ordered by A_CITY desc
83RowsetOpts.find_items_options.order_ascending = False
84rowset = impact.active_database.find_rowset(RowsetOpts)
85ot.add("")
86ot.add("Rowset of CUSTOMER and ADDRESS columns with A_CITY like p (sort A_CITY desc) : ")
87ot.add(rowset)