page255
342
Next check you have a single record.
SELECT * FROM v_client_info;
SQL Statement
SQL statements overlay data product objects providing the template for client-specific
data generation into client objects. For this example, you set the
sql_statement
name to
be the same as the
client_object
name in order to later join the data.
Create a table called
sql_statement
along with a sequence called
seq_sql_
statement_id
to generate surrogate keys.
CREATE OR REPLACE TABLE sql_statement
(
sql_statement_id NUMBER PRIMARY KEY,
name VARCHAR(255),
sql_statement VARCHAR(255)
);
CREATE OR REPLACE SEQUENCE seq_sql_statement_id START WITH 100000;
Define a SQL statement with substitution values for a filter and mask, noting you set
the
name
to be
base_customer_order
to join with the client configuration data.
INSERT INTO sql_statement VALUES
( seq_sql_statement_id.NEXTVAL, 'base_customer_order',
'SELECT customer_key, customer_name, [ORDER_KEY_MASK], order_date, nation_
name FROM base_customer_order WHERE region_name = ''[REGION]'' ' );
Client SQL Statement View
With all the entities defined, you now bring them together into a single usable
entitlement generation object.
Create a view called
v_client_sql_statement
.
CREATE OR REPLACE VIEW v_client_sql_statement
AS
SELECT c.client_name,
Chapter 9
Client expeCtations