Using the Script Wizard
Infor provides the Script Wizard to assist you with scripted data sources.
The script wizard allows you to combine data from two or more sources, and up to six tables and ten joins are allowed. The wizard helps
you populate the Select Statement and Script for your scripted source.
You can use the Script Wizard for an existing script by selecting the
script and clicking the Wizard button. Make the changes you want, then
click Save in the Script Wizard dialog box.
To create a scripted data source using the Script Wizard
|
1.
|
Go to Admin
- Define Sources - Data Flow. |
Alternatively, from the Manage Sources page
click the gear icon at the top of the Data Sources
pane and select Add Script.
|
3.
|
Click Add
Script Based Source. |
|
4.
|
Enter a name
for the new scripted source in the dialog box that opens. |
|
5.
|
To clone an
existing source, check the box and select the source from the dropdown
list. |
|
6.
|
Click OK.
A new data source for your scripted source will appear on the Data Flow
page in light purple, for example:
|
|
7.
|
Right-click
the new scripted source and select Manage Sources. The Modify Data Source
dialog opens. |
|
8.
|
Click the
Script tab, then click Wizard . The Script Wizard dialog
box opens.
|
|
9.
|
If you cloned
a source, that source will be selected in the Primary Source list. If
you did not clone a source, select the source to use as your primary source
from the list. All of the columns in the selected source display at the
bottom of the dialog box.
You can select only one Primary Source. If you change the primary source,
the columns of the source (and any changes you made to them) will be overwritten
by the columns in the newly selected source. |
|
10.
|
Check the
Input box of the columns you want to include in the input query (in the
Select statement on the Script tab). If there are any columns you do not
want to include, uncheck the Input box. |
|
11.
|
Check the
Output box to include the column as an output column (in the Script box
of the Script tab). The columns marked as output will also be added to
the Columns tab of the Modify Data Source dialog box.
You cannot check the Output box unless the Input box is checked. |
|
12.
|
If you would
like to sort on one or more column(s) in your Select statement, click
the Sort value and select the type of sort to use for the column from
the dropdown. In the Select statement you will see the selected columns
listed after an Order By clause. For example:
ORDER BY [RevenueForecast.Start Date] ASC, [Owner.Owner Role Hierarchy Depth] DESC |
|
13.
|
To create
a join definition between the primary source and another source, click
Add Join. By default the first table name in alphabetical order will be
displayed in the Table Name field under Joins. To change the table, click
the name in the Table Name field and click the down arrow to select the
table to join from a dropdown list as shown in the following image.
The columns of the table you select will be added to the list of columns
in the bottom half of the dialog box after the columns of the primary
source.
You can add up to 10 joins. |
|
14.
|
In the Join
Condition field, type the join condition to use as in the example below.
|
|
15.
|
Click the
Join Type field and select Inner, Left Outer, Right Outer, or Full Outer.
If you do not specify a join type, it is set to Inner by default. |
|
16.
|
In the bottom
half of the dialog box, select the columns from the join table that you
want to include in the input and the output. |
|
17.
|
Click Save. The selections you made in the
wizard populate the Select Statement and Script areas
of the Script tab. The columns marked as output are added to
the Columns tab. |
See Also
Creating a Scripted
(ETL) Data Source
ETL Services Overview
Ignoring, Emptying
or Deleting a Source