Manually Configuring Metadata

The following example represents when you can use Manually Configure Metadata option.

If you enter the following SQL statement in the Query field, an error is displayed though the query is valid:

SELECT * FROM CUSTOMERS LIMIT 2, 3;

The following error is displayed:

query parsing failed: LIMIT #,# syntax is not supported

Because of the error, database table metadata is not auto fetched in the Fields table. To fetch the database table metadata, set Manually Configure Metadata to True.

When the field is set to True, one more Query field at the bottom of this option is displayed. To retrieve and configure the database schema fields, you must first enter a simple schema query in this field and click Fetch. For example, you can enter the following schema query to fetch the metadata:

SELECT * FROM CUSTOMERS;

After successful processing of the query, the table field at the bottom is populated with the column metadata information. You can add, edit, and remove fields in the table. The fields marked Selected in the table are available on the Output tab of the activity. The fields selected in the Parameter column are available on the Input tab of the activity. The fields selected in the Value column are available on the Output tab of the activity. You can modify these settings by selecting or clearing the check boxes.

SELECT BookNumber,BookName,Author,Price,
case
    WHEN Price = 0 THEN 'No books'
    WHEN Price <250 THEN 'Low price'
    WHEN Price >250 and Price <400 THEN 'Average price'
    ELSE 'Expensive'
end
FROM BookStore;

select 2!;

select nullif(classroom.building, department.building), department.building
from classroom, department WHERE department.dept_name='Athletics' order by 1, 2 desc;

update t1, t2 set t1.Id=260, t2.pattern='twosixzero' where t1.pattern='HOLA' and t2.id='A';

SELECT CAST ('100' AS INTEGER);