Insider Scoop From the Tutorial Divas

« November 2009
Sun	Mon	Tue	Wed	Thu	Fri	Sat
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Today

« November 2009

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Today

The tutorials show how to display a database column in a drop-down list. But what if you want to show multiple database columns in the list. For example, the VIR database table that comes bundled with the product has a FIRSTNAME column and a LASTNAME column. You might want the drop-down list to show both fields, such as "Jane Doe".

One solution is to add an alias to the database query. Using the VIR database table as an example, you drop the VIR database table on the page, and use the Query Editor to add VIR.EMPLOYEE.FIRSTNAME || ' ' || VIR.EMPLOYEE.LASTNAME AS FULLNAME to the query statement, as shown below.

SELECT ALL VIR.EMPLOYEE.ID,
                    VIR.EMPLOYEE.FIRSTNAME,
                    VIR.EMPLOYEE.LASTNAME,
                    VIR.EMPLOYEE.EMAIL,
VIR.EMPLOYEE.FIRSTNAME || ' ' || VIR.EMPLOYEE.LASTNAME AS FULLNAME
FROM VIR.EMPLOYEE

To test the statement, right-click in the query pane of the Query Editor and choose Run Query. A dialog pops up explaining that the the IDE's parser only knows a small subset of common SQL syntax. Click continue and the Query Editor should output the query results.

You will also see that the the visual panes in the Query Editor for this particular rowset no longer work. This is because of the same reason that the IDE's parser only knows a small subset of common SQL syntax. This problem only affects the Query Editor.

To see the query in action, drop a Drop Down List component on the page, right-click the component and choose Bind to Data. Click the Bind to Data Provider tab, choose employeeDataProvider, select EMPLOYEE.ID for the Value Field, and select FULLNAME for the Display Field.

Click OK. Test and Run.

The above query works with the bundled database. You have to adjust based on the syntax of your database. For example, for MySQL, use something like select concat(member.lastname, ", ", member.firstname).