Subject Re: [Firebird-Java] Select LIMIT skip count and Select tempA.* from (select...) as tempA support?
Author Helen Borrie
At 06:46 PM 18/04/2004 +0000, you wrote:
>[win2k, FB1.5 v1.5.0.4290]
>Two simple queries, but cant get to work. Is it supported to run such
>queries as described in 1 and 2 topics?
>Create table customer(id integer, name varchar(50), code varchar(5));
>Insert Into customer values(1, 'aaa', 'a');
>Insert Into customer values(2, 'bbb', 'b');
>Insert Into customer values(3, 'ccc', 'c');
>Insert Into customer values(102, 'aa2', 'a');
>Insert Into customer values(103, 'aa3', 'a');
>Insert Into customer values(202, 'bb2', 'b');
>Insert Into customer values(203, 'bb3', 'b');
>(1) Select given range of rows (limit and/or top query)
>select LIMIT 30 10 * from customer;
>-> syntax error. I need to select a given range of rows. Im able to do
>this with hsqldb, postgre and mysql.

select FIRST m SKIP n * from customer

>(2) Selection from derived table
>select tempA.* from (select code from customer where code='a') as tempA;
>-> syntax error. Example is a simplified version, but should use a
>on-the-fly derived table within 'from' block. Works on in mssql,
>hsqldb and oracle. Dont know about postgre and mysql.

Embedded derived tables are not supported in Firebird.

If you want multiple columns, ou have the options to use either a view or a
selectable stored procedure. If you want only a single column, you can use
a subquery:

(select aField from customer
where code = 'a') tempA,
fld 3
from AnotherTable

You can also correlate the subquery:

(select c.aField from customer c
where c.code = a.something) tempA,
a.fld 3
from AnotherTable a
where a.aField = ...

>- - - -
>Then a batch queries give me troubles as well. I've figured it out
>already that it's not supported in FB. I mean, I want to run in one go:
>Insert Into customer values(123, 'abc', 'a');
>Insert Into customer values(345, 'cde', 'd');
>Insert Into log values('added customer 123');
>Insert Into log values('added customer 345');
>-> I must split this at _clientside_ and run one by one.

Yes, SQL doesn't support multiple statements in one query. You can submit
DML scripts (batches of statements) but someone else will have to explain
the mechanism for this with Jaybird.

You can't use replaceable parameters in scripts.