Subject Re: [firebird-support] Digest Number 9396
Author Helen Borrie
> And how should I do my queries ? Is there some documentation for this ?


> I need to compile data from several tables, scan the records,
> update , delete some and then prepare the data for a report. For me the

> with query as(
> select
> )
> select * from query

> is not enough.

Of course it is not enough. SELECT * is for one-armed paperhangers.

from produse
inner join sortiment on (sortiment.id_sortiment + 0 = produse.id_sortiment)
denumire_sortiment <> 'N

will return the data set you want: two columns and just the rows that
match the WHERE clause. It's called Data Manipulation Language (DML)
and it is the REAL nuts and bolts of a database management system.

About domains:
You do NOT need to create a domain for every data type. For example,
if a column is simply INTEGER then declare it as INTEGER when you
define the table. Sure, Firebird will create an internal domain for
*that column* in RDB$FIELDS but you do not need to know about it.

Create domains for repeating column types that have specific
attributes, such as NOT NULL or CHECK constraints. For convenience,
if you like, keep a printed list of all your domains for reference at
design time.

And you do not need to query RDB$FIELDS to check whether a domain
-- if you try to create a domain that already exists, you will simply
get a duplicate index error.
-- if you try to create a column using a domain that doesn't exist,
you will get another kind of error, something like "invalid data

Avoid creating ANY objects on the fly - tables, GTTs, views, stored
procedures, etc. Don't do DDL (data definition language) operations
in user run-time. Organise your database so that everything you need
for queries is right there. Doing it your way is like every time you
go out in your car, you have to wait for engineers to come and build a
road. Learn about DML - you will be amazed.


> Tiberiu


Kind regards,
Helen Borrie