Example 3: Date and Time Duration

Data View Builder supports a set of functions that operate on date and time. For more information on date and time functions see Date and Time Functions in the "Functions Reference."

The Problem

Determine if a Broadband customer has any open orders in the Broadband database before a specified date.

The Solution

For each Broadband order that matches the given Customer ID, you need to set these conditions:

• The order status is "OPEN"
• The ship date for a given customer_id is earlier than or equal to the date (date1) provided. (customer_id and date1 are a variables that you define as query parameters to be submitted at query runtime).

To create the solution, follow these steps:

• View a Demo
• Ex 3: Step 1. Verify the Target Schema is Saved in Repository
• Ex 3: Step 2. Open Source and Target Schemas
• Ex 3: Step 3. Map Source to Target Nodes to Project the Output
• Ex 3: Step 4. Create Joins
• Ex 3: Step 5. Create Two Query Parameters for Customer ID and Date to be Provided at Query Runtime
• Ex 3: Step 6. Set a Condition Using the Customer ID
• Ex 3: Step 7. Set a Condition to Determine if Order Ship Date is Earlier or Equal to a Date Submitted at Query Runtime
• Ex 3: Step 8. Set a Condition to Include Only "Open" Orders in the Result
• Ex 3: Step 9. View the XQuery and Run the Query to Test it
• Ex 3: Step 9. Verify the Result

View a Demo

Date and Time Duration Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example. This demo previews the steps described in detail in the following sections. The demo assumes you already have the target schema in the server Repository.

Ex 3: Step 1. Verify the Target Schema is Saved in Repository

For this example, we will use a target schema called customerLineItems.xsd. This schema is available in the Samples server repository.

ld_repository/schemas/

See Example directories for information on how example directory names are used.

Just in case you want to verify that you have the right schema file, the following code listing shows the XML for this schema.

Listing 9-8 XML Source for customerLineItems.xsd Target Schema File

<?xml version = "1.0" encoding = "UTF-8"?>

<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema">
	<xsd:element name = "customers">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "customer" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "customer">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "first_name"/>
				<xsd:element ref = "last_name"/>
				<xsd:element ref = "orders" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
			<xsd:attribute name = "id" use = "required" type = "xsd:string"/>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "first_name" type = "xsd:string"/>
	<xsd:element name = "last_name" type = "xsd:string"/>
	<xsd:element name = "orders">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "order" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "order">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "line_item" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
			<xsd:attribute name = "id" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "date" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "amount" use = "required" type = "xsd:string"/>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "line_item">
		<xsd:complexType>
			<xsd:attribute name = "id" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "product" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "status" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "expected_ship_date" use = "required" type = "xsd:string"/>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

 

---

Ex 3: Step 2. Open Source and Target Schemas

1. In the Data View Builder, choose File—>New Project to clear your desktop and reset all default values.

2. On the Builder Design—>Sources tab, click Relational Databases and open one data source:

   • Double-click on the PB-BB (Broadband) relational database to open the schema for this data source.

3. Choose the menu option File—>Set Target Schema.

   Navigate to the server Repository or to the location where you saved the customerLineItems.xsd schema. Choose customerLineItems.xsd and click Open.

   customerLineItems.xsd appears as the target schema.

   This target schema is displayed as a docked schema window on the right side of the design area.

4. Click the plus (+) sign (or right-mouse click and choose Expand) to expand the nodes in each source schema and in the target schema.

Ex 3: Step 3. Map Source to Target Nodes to Project the Output

Project the output values as follows.

1. Drag and drop [PB-BB]/db/CUSTOMER/FIRST_NAME from the source schema onto [customerLineItems.xsd]/customers/customer/first_name in the target schema.

2. Drag and drop [PB-BB]/db/CUSTOMER/LAST_NAME from the source schema onto [customerLineItems.xsd]/customers/customer/last_name in the target schema.

3. Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/LINE_ID from the source schema onto [customerLineItems.xsd]/customers/customer/orders/order/line_item/id in the target schema (id is an attribute of line_item).

4. Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/PRODUCT_
   NAME from the source schema onto [customerLineItems.xsd]/customers/customer/orders/order/line_item/product in the target schema (product is an attribute of line_item).

5. Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/STATUS from the source schema [customerLineItems.xsd]/customers/customer/orders/order/line_item/status in the target schema (status is an attribute of line_item).

6. Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_
   SHIP_DATE from the source schema [customerLineItems.xsd]/customers/customer/orders/order/line_item/expected_
   ship_date in the target schema (expected_ship_date is an attribute of line_item).

At this point, the following mappings should be displayed on the Mappings tab. (Getting the mappings in the same order as shown is not as important as verifying that the relationships between source and target nodes are the same. The @ symbols indicate attributes.)

Source	Target
[PB-BB]/db/CUSTOMER/FIRST_NAME	[customerLineItems.xsd]/customers/customer/ first_name
[PB-BB]/db/CUSTOMER/LAST_NAME	[customerLineItems.xsd]/customers/customer/ last_name
[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/LINE_ID	[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@id
[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/PRODUCT_NAME	[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@product
[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/STATUS	[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@status
[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/EXPECTED_SHIP_ DATE	[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@expected_ship_date

 

Ex 3: Step 4. Create Joins

Join customer with corresponding line-item data. This requires two joins, one to find the customer's Order IDs, and another that uses the Order IDs and finds the corresponding line-item information:

1. Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ID onto [PB-BB]/db/CUSTOMER_ORDER/CUSTOMER_ID.

2. Drag and drop [PB-BB]/db/CUSTOMER_ORDER/ORDER_ID onto [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/ORDER_ID.

Ex 3: Step 5. Create Two Query Parameters for Customer ID and Date to be Provided at Query Runtime

Create two Query Parameter variables: customer_id and date1, that you can use to insert as variable values when the query runs. Create both variables as type xs:string. Do this as follows:

1. On the Builder Toolbar, click Toolbox and then click Query Parameter.

2. From the "Type" drop-down menu, choose xs:string.

3. In Parameter Name field, enter customer_id and click Add.

   The new parameter is displayed in the Query Parameters tree.

4. Repeat steps 2 and 3 to create the date1 variable.

   You should now see both parameters displayed in the Query Parameters tree.

Ex 3: Step 6. Set a Condition Using the Customer ID

1. On the Builder Toolbar, click Toolbox and then click Functions.

2. Drag and drop the equals (eq) function (under Operators) onto the next empty row in the Conditions tab.

   The Functions Editor pops up and displays a statement with placeholder variables for you to fill in.

3. On the Builder Toolbar, click on Query Parameter, then drag customer_id onto anyValue1 onto the left side of the equation.

4. Drag [PB-BB]/db/CUSTOMER/CUSTOMER_ID onto the right side of the equation.

   The function should look like this:

   (customer_id eq [PB-BB]/db/CUSTOMER/CUSTOMER_ID)

5. Close the Functions Editor.

Ex 3: Step 7. Set a Condition to Determine if Order Ship Date is Earlier or Equal to a Date Submitted at Query Runtime

1. Click on Functions, and drag and drop the Operator function le (less than or equal) onto the next empty row on the Conditions tab.

   The Functions Editor pops up and displays a statement with placeholder variables for you to fill in.

2. Drag and drop
   [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_SHIP_DATE onto anyValue1 on the left side of the equation.

3. Click on Functions, and drag and drop the date-from-string-with-format function onto anyValue2 on the right side of the equation.

   At this point, the expression in the Functions Editor should look like this:

([PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_SHIP_DATE  le  xfext:date-from-string-with-format(pattern,srcval))

4. Click Constants, enter the following in the String field:

   yyyy-MM-dd

   Now drag it (via the Constant icon next to the field) onto pattern (first placeholder parameter to the date function).

5. Click on Query Parameter, and drag and drop date1 from the Query Parameters panel onto srcval (the second placeholder parameter to the date function).

   The completed expression should look like this:

([PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_SHIP_DATE  le  xfext:date-from-string-with-format("yyyy-MM-dd",date1))

6. Close the Functions Editor.

   The condition you created is displayed on the Conditions tab in the Source column.

Ex 3: Step 8. Set a Condition to Include Only "Open" Orders in the Result

Set the second condition to an Open Order status.

1. Click on Functions, and drag and drop the Operator function eq (equal) onto the Conditions tab.

   The Functions Editor pops up and displays a statement with placeholder variables for you to fill in.

2. For the left parameter (anyValue1), drag and drop [PB-BB]/db/Customer_Order_Line_Item/Status on to anyValue1.

3. For the right parameter (anyValue2), create a constant String with a value of OPEN, and drop it (via the Constant icon next to the field) onto anyValue2.

   The completed expression should look like this:

   ([PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/STATUS eq "OPEN")

   Close the Functions Editor.

Ex 3: Step 9. View the XQuery and Run the Query to Test it

1. Click on the Test tab.

   The generated XQuery for this query is shown in the following code listing.

Listing 9-9 XQuery for Example 3: Date and Time Duration

{--	Generated by Data View Builder 1.0 	--}

<customers>
	{
	for $PB-BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	where ($#customer_id of type xs:string  eq  $PB-BB.CUSTOMER_1/CUSTOMER_ID)
	return
	<customer>
		<first_name>{ xf:data($PB-BB.CUSTOMER_1/FIRST_NAME) }</first_name>
		<last_name>{ xf:data($PB-BB.CUSTOMER_1/LAST_NAME) }</last_name>
		<orders>
			<order>
				{
				for $PB-BB.CUSTOMER_ORDER_2 in document("PB-BB")/db/CUSTOMER_ORDER
				for $PB-BB.CUSTOMER_ORDER_LINE_ITEM_3 in document("PB-BB")/db/CUSTOMER_ORDER_LINE_ITEM
				where ($PB-BB.CUSTOMER_ORDER_2/ORDER_ID  eq  $PB-BB.CUSTOMER_ORDER_LINE_ITEM_3/ORDER_ID)
				  and ($PB-BB.CUSTOMER_ORDER_LINE_ITEM_3/EXPECTED_SHIP_DATE  le  xfext:date-from-string-with-format("yyyy-MM-dd",$#date1 of type xs:string))
				  and ($PB-BB.CUSTOMER_ORDER_LINE_ITEM_3/STATUS  eq  "OPEN")
				  and ($PB-BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB-BB.CUSTOMER_ORDER_2/CUSTOMER_ID)
				return
				<line_item id={$PB-BB.CUSTOMER_ORDER_LINE_ITEM_3/LINE_ID} product={$PB-BB.CUSTOMER_ORDER_LINE_ITEM_3/PRODUCT_NAME} status={$PB-BB.CUSTOMER_ORDER_LINE_ITEM_3/STATUS} expected_ship_date={$PB-BB.CUSTOMER_ORDER_LINE_ITEM_3/EXPECTED_SHIP_DATE}>
</line_item>
				}
			</order>
		</orders>
	</customer>
	}
</customers>

 

---

2. In the Query Parameter panel on the Test tab, set the variable values for customer_id and date1 to submit to the query when the query runs.

   For example:

   • customer_id: CUSTOMER_1 (CUSTOMER_1 through CUSTOMER_10 are available in the data source.)
   • date1: 2002-08-01 (You can enter any date in the form yyyy-MM-dd.)

3. Click the "Run query" button to run the query against the data sources.

Ex 3: Step 9. Verify the Result

Running this query with customer_id set to "CUSTOMER_1" and date1 set to "2002-08-01" produces the following XML query result.

Listing 9-10 Result for Example 3: Date and Time Duration

<customers>
  <customer>
    <first_name>JOHN_B_1</first_name>
    <last_name>KAY_1</last_name>
    <orders>
      <order>
        <line_item expected_ship_date="2002-03-06-08:00" id="LINE_ID_1" product="RBBC01" status="OPEN"/>
        <line_item expected_ship_date="2002-03-06-08:00" id="LINE_ID_3" product="BN16" status="OPEN"/>
        <line_item expected_ship_date="2002-03-06-08:00" id="LINE_ID_5" product="CS100" status="OPEN"/>
        <line_item expected_ship_date="2002-03-06-08:00" id="LINE_ID_1" product="RBBC01" status="OPEN"/>
        <line_item expected_ship_date="2002-03-06-08:00" id="LINE_ID_3" product="BN16" status="OPEN"/>
        <line_item expected_ship_date="2002-03-06-08:00" id="LINE_ID_5" product="CS100" status="OPEN"/>
      </order>
    </orders>
  </customer>
</customers>

 

---