SELECT (AS DATASET)

The SELECT-statement retrieves data from one or more tables in a database.

To loop over a result set without creating a dataset variable, use FOR SELECT.

If your database system supports Common Table Expressions (CTE), you can use these with scripting as well.

Syntax

[WITH <cte> [, <ct> ...]]

SELECT

  [<limit clause>]

  [{ALL | DISTINCT}] <select_list>

FROM <table_reference> [, <table_reference> ... ]

[WHERE <search_condition>]

... other SQL SELECT-statement clauses ...

[ORDER BY <sort_spec> [, <sort_spec> ... ]]

{INTO <variable_list> | AS DATASET dataset_name}

You can write additional clauses if your database system supports it, like a WINDOW clause. The script parser parses the statement up to the INTO or AS DATASET clause.

The INTO or AS DATASET are required.

INTO-clause

The INTO-clause accepts a list of variables, the SELECT should return no row or only one row of data. If no data is returned, the values in the variables won't be modified.

Example

In this example, the query fetches data and returns it via the script output parameters to the caller.

execute udsblock

returns(o1 integer, ovc1 varchar(20), ovc2 varchar(31))

as

begin

  select rdb$relation_id, rdb$relation_name from rdb$relations

  where rdb$relation_name = 'RDB$DATABASE'

  into :o1, :ovc2;

  /* return to script caller */

end

AS DATASET-clause

The AS DATASET-clause creates an implicit dataset-typed variable that can be used with the Dataset-namespace functions to retrieve or modify data, or use in memory datasets that can be saved to file.

Example

execute udsblock()

returns(resultint integer, resultvarchar varchar (2000), resultbool boolean, resultpointer bigint)

as

begin

  /* create the data set */

  select employeeid, emp_name, salary, bonus_perc

  from test_data order by employeeid

  as dataset mydata;

  resultbool = Dataset.Locate(mydata, 'employeeid', 539999, 0);  

  Dataset.Edit(mydata); /* put into edit mode */

  Dataset.SetFieldValue(mydata, 'emp_name', 'Harold');

  Dataset.Post(mydata);

  resultvarchar = Dataset.FieldValue(mydata, 'emp_name');

  Dataset.Insert(mydata); /* insert new row and put in */

  Dataset.SetFieldValue(mydata, 'emp_name', 'Frank');

  Dataset.SetFieldValue(mydata, 'employeeid', 49998);

  Dataset.SetFieldvalue(mydata, 'salary', 100);

  Dataset.SetFieldValue(mydata, 'bonus_perc', 0);

  Dataset.Post(mydata);

  resultvarchar = Dataset.FieldValue(mydata, 'emp_name');

  Dataset.Insert(mydata); /* put into edit mode */

  Dataset.SetFieldValue(mydata, 'emp_name', 'Cancel this change');

  Dataset.SetFieldValue(mydata, 'employeeid', 50000);

  Dataset.SetFieldvalue(mydata, 'salary', 100);

  Dataset.SetFieldValue(mydata, 'bonus_perc', 0);  

  Dataset.Cancel(mydata);

  Dataset.First(mydata);

  resultbool = Dataset.Locate(mydata, 'employeeid', 49998, 0);

  resultint = Dataset.FieldValue(mydata, 'employeeid');

  resultvarchar = Dataset.FieldValue(mydata, 'emp_name');

  Dataset.First(mydata);

  resultbool = Dataset.Locate(mydata, 'employeeid', 539999, 0);

  Dataset.First(mydata);

  resultbool = Dataset.Locate(mydata, 'employeeid', 49998, 0);

  Dataset.Delete(mydata);

  Dataset.Close(mydata);

end