Embedded Files
How to create
SQL Operation
Create an SQL Operation similar to something like these examples:
example: One Predictive Search textbox.
SELECT *
FROM Schema.Table
WHERE (column_1 LIKE COALESCE( ?<derived_fd_id>, column_1) <!-- 1st parameter -->
OR column_2 LIKE COALESCE( ??1, column_2) <!-- not part of the parameter numbering -->
OR column_3 LIKE COALESCE( ??1, column_3)) <!-- not part of the parameter numbering -->
Note: All columns are searched through the textbox that they are corresponding to. Each column that is represented by the “??<number>” are not considered in the numbering order of parameters. The way the numbering of Parameters works in WOW, is by starting at the top of the SQL statement and start counting which number they are in sequence, this is how you determine what the Predictive Search textbox will be numbered by as well as what number to use for the other columns that will be searched from that textbox as well when setting them as a ??<number>.
example: Two Predictive Search textboxes.
SELECT *
FROM Schema.Table
WHERE ( column_1 LIKE COALESCE( ?<derived_fd_id_1>, column_1) <!-- 1st predictive textbox -->
OR column_2 LIKE COALESCE( ??1, column_2)) <!-- searched for using 1st textbox -->
[[ OR (column_3 LIKE COALESCE( ?<derived_fd_id_2>, column_3 ) <!-- 2nd predictive textbox -->
OR column_4 LIKE COALESCE( ??2, column_4 )) ]] <!-- searched for using 2nd textbox -->
Note: The sample SQL statement above has two separate Predictive Search textboxes. These search textboxes are defined by the question mark (?) followed by the derived_fd_id_<number>.
To get the derived fd id number, go into the FD Manager and scroll down to the bottom of the Additional settings and the id number is all you want, then you would input the derived_fd_id right after the question mark in the WHERE clause of the SQL statement.
example: getting the derived field descriptor id to use in SQL Operation
This creates the Predictive Search textbox and in the sample SQL's above. There are two separate Predictive Search textboxes I am setting up. My column_2, will be able to be searched along with column_1 in that first derived_fd_id_1 that was created.
The second one, derived_fd_id_2, is used to search for columns, 3 and 4 from that Predictive Search textbox.
If you just need one Predictive Search textbox, just set up the first column as shown, then every column thereafter, do something like this example:
SELECT *
FROM pjdata.employees
WHERE (firstname LIKE COALESCE( ?232529, firstname )
OR lastname LIKE COALESCE( ??1, lastname )
OR job LIKE COALESCE( ??1, job )
OR workdept LIKE COALESCE( ??1, workdept ))
Note: “ ?232529 ” is referencing the derived field descriptor named D_NAMES_PS that is being used for the Predictive Search textbox. That is the first parameter and that is why there is a number 1 after the double question marks on the other columns.
The ??1, is called Parameter Parameters and to see more information on that, please click here.
Page updated
Report abuse