FAQ FirebirdConsultez toutes les FAQ

SELECT FIRST X Champ1, Champ2 .... FROM ....

SELECT FIRST X SKIP Y Champ1, Champ2 .... FROM ....

1
2
3
select distinct RDB$RELATION_NAME from rdb$RELATION_FIELDS 
where RDB$VIEW_CONTEXT is null 
  and RDB$SYSTEM_FLAG = 0;

insert into t1 values(10, CURRENT_TIMESTAMP);

1
2
select current_date, current_timestamp 
from rdb$database;

1
2
3
4
5
6
7
create procedure p1 
as 
BEGIN 
if (current_timestamp > cast('2007-11-03' as timestamp)) then 
exception myerror; 
... 
END

1
2
select cast('now' as timestamp), cast('today' as date) 
from rdb$database

SELECT COUNT(*) FROM ( votre requête );

SELECT COUNT(*) FROM ( select * from employee where emp_no > 8 );

1
2
3
4
5
create view my_employees as select * from employee where emp_no > 8; 
commit; 
SELECT COUNT(*) FROM my_employees; 
commit; 
drop view my_employees;

1
2
3
4
select id, count(*) 
from t1 
group by id 
having count(*) > 1;

1
2
3
4
5
DELETE FROM TABLE1 t1 
WHERE EXISTS ( 
SELECT 1 FROM TABLE1 t2  
WHERE t1.COL1 = t2.COL1 and t1.COL2 = t2.COL2  
  AND t2.RDB$DB_KEY > t1.RDB$DB_KEY );

1
2
3
4
declare variable s varchar(20); 
... 
s = 'Ceci est la ligne un. 
Ceci est la ligne deux.';

c = 'Ceci est la ligne un.'||ASCII_CHAR(13)||ASCII_CHAR(10)||'Ceci est la ligne deux.';

1
2
3
SELECT ep.PROJ_ID, count(*) 
FROM EMPLOYEE_PROJECT ep 
GROUP BY ep.PROJ_ID;

1
2
3
4
SELECT ep.PROJ_ID, p.PROJ_NAME, p.PRODUCT, count(*) 
FROM EMPLOYEE_PROJECT ep 
JOIN PROJECT p on ep.PROJ_ID = p.PROJ_ID 
GROUP BY ep.PROJ_ID, p.PROJ_NAME, p.PRODUCT;

1
2
3
4
SELECT ep.PROJ_ID, MAX(p.PROJ_NAME), MAX(p.PRODUCT), count(*) 
FROM EMPLOYEE_PROJECT ep 
JOIN PROJECT p on ep.PROJ_ID = p.PROJ_ID 
GROUP BY ep.PROJ_ID

1
2
DECLARE VARIABLE v1 VARCHAR(32000); 
SELECT SUBSTRING(nom_colonne_blob1 FROM 1 FOR 32000) FROM table1 INTO v1;

$row = ibase_fetch_assoc($qry, IBASE_TEXT);

1
2
3
SELECT * FROM Employee; 
SELECT * FROM EMPLOYEE; 
SELECT * FROM employee;

1
2
3
SELECT * FROM "Employee"; 
SELECT * FROM "EMPLOYEE"; 
SELECT * FROM "employee";

select first_name||' '||last_name from employee;

1
2
3
4
5
CREATE TABLE t1 ( c1 CHAR(5) ); 
COMMIT; 
  
INSERT INTO t1 VALUES ('abcd'); 
SELECT * FROM t1 WHERE c1 LIKE '%cd';

SELECT * FROM t1 WHERE RTRIM(c1) LIKE '%cd';

1
2
select cast('now' as timestamp) 
from ...

1
2
SELECT CAST('Firebird' AS VARCHAR(20) CHARACTER SET OCTETS) 
FROM RDB$DATABASE

4669726562697264

1
2
ALTER TABLE t1 ADD compteur_relation  
COMPUTED BY ( (SELECT COUNT(*) FROM RDB$RELATIONS) );

1
2
3
4
5
6
UPDATE dest_table t1 
SET  
field1 = (select field1 from src_table t2 where t2.pk = t1.pk), 
field2 = (select field2 from src_table t2 where t2.pk = t1.pk), 
...etc. 
WHERE EXISTS (select 1 from src_table t2 where t2.pk = t1.pk)

1
2
3
4
5
6
7
8
9
10
11
12
EXECUTE BLOCK 
AS 
DECLARE VARIABLE field1 type; 
DECLARE VARIABLE field2 type; 
...etc. 
DECLARE VARIABLE pk type; 
BEGIN 
for select pk, field1, field2, ... from src_table 
into :pk, :field1, :field2, ... 
do update dest_table set field1 = :field1, field2 = :field2, ... 
where pk = :pk; 
END

fbexport -S -Q "select pk, field1, field2, ... from src_table" -F test.fbx

fbexport -If -Q "update dest_table set field1 = :2, field2 = :3, ... where pk = :1" -F test.fbx

1
2
3
4
5
6
7
8
9
10
11
12
13
SET TERM !! ; 
  
CREATE PROCEDURE p1 ( TableName VARCHAR(32)) 
RETURNS ( id integer ) 
AS 
DECLARE VARIABLE stmt VARCHAR(1000); 
BEGIN 
stmt = 'select id from '||:TableName; 
for execute statement :stmt into :id 
do suspend; 
END; 
  
SET TERM ; !!

1
2
DECLARE VARIABLE v1 VARCHAR(32000); 
SELECT SUBSTRING(nom_colonne_blob1 FROM 1 FOR 32000) FROM table1 INTO v1;

$row = ibase_fetch_assoc($qry, IBASE_TEXT);

1
2
3
4
5
6
7
SELECT 
rdb$get_context('USER_TRANSACTION', 'row#') as row_number, 
rdb$set_context('USER_TRANSACTION', 'row#', 
COALESCE(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0) + 1), 
a.rdb$relation_name 
FROM rdb$relations a 
ORDER BY a.rdb$relation_name

1
2
3
4
5
6
7
SELECT 
rdb$get_context('USER_TRANSACTION', 'row#') as row_number, 
rdb$set_context('USER_TRANSACTION', 'row#', 
COALESCE(rdb$get_context('USER_TRANSACTION','row#'),0) + 1), 
a.rdb$relation_name 
FROM rdb$relations a 
ORDER BY a.rdb$relation_name

1
2
3
4
5
CREATE TABLE t1 ( 
v1 VARCHAR(4), 
c2 CHAR(4) 
); 
INSERT INTO t1 (v1,c2) VALUES ('a', 'a');

1
2
.. WHERE v1 = 'a' 
..WHERE c2 = 'a'