little help to build a query in Firebird
lazarus / 16-Databases

----------------------------------------------------------------------
Ronan · 2011-09-05 17:22

hi ,

I've managed to comprise the intended query to some extended but,
either I'm so tired or missing something obvious, I couldn't succeed
to form the final shape of query.

This code returns a dataset aliased as new_tbl
Code: [Select]set generator gen_new to 0 ;
select gen_id(gen_new,1) as num, new_tbl.elm_prof, new_tbl.elm_mat
from (select distinct elm_prof , elm_mat from ELEMENTS) new_tbl
For futher I need to do the following
Code: [Select]Update elements as e set e.prop_type=new_tbl.num where
????
As a result I couldn't place a dataset after the where command so how
to merge those 2 queries to form the final shape?

Any clue will be appreciated.

----------------------------------------------------------------------
Lacak2 · 2011-09-06 05:48

I doubt, that it can be done in simple SQL statement.

Try use stored procedure, or execute block.

If you use Firebird 2.1 then you can also try merge statement

----------------------------------------------------------------------
Ronan · 2011-09-06 06:09

Thanks in advance

I'll drill into that block statement and stored proc, I always thought
that these topics are intended for web related Client_server model.

Ok I've remedy the situtation,  I'm using FLame Robin editor
Code: [Select]set generator gen_new to 0 ;
merge into elements e using
(select gen_id(gen_new,1) as num, new_tbl.elm_prof, new_tbl.elm_mat
from (select distinct elm_prof , elm_mat from ELEMENTS )  new_tbl)
on new_tbl.elm_prof = e.elm_prof
when matched then update set e.prop_type = new_tbl.num
SQL doesn't recognize the derived tables "new_tbl" and "e" so it says
there is not column named new_tbl.
Something wrong with my syntax ?

Code: [Select]I doubt, that it can be done in simple SQL
statementReally, to me seems like a paradox

----------------------------------------------------------------------
Lacak2 · 2011-09-06 13:00

I have no practical experience with MERGE statement, so read here:
http://www.firebirdsql.org/file/documentation/release_notes/html/rlsno
tes210.html#rnfb210-merge

Try:
Code: [Select]merge into elements as e
using (select gen_id(gen_new,1) as num, new_tbl.elm_prof,
new_tbl.elm_mat from (select distinct elm_prof , elm_mat from ELEMENTS
) as new_tbl) as a
on (e.elm_prof = a.elm_prof)
when matched then update set e.prop_type = a.num

----------------------------------------------------------------------
Ronan · 2011-09-06 16:41

thanks in advance ,

One final question now it works but it seems that generator is
producing the wrong num values for derived table. It's supposed to
stop generating numbers before updating event and should use the
derived tables num fields for updating.
Quoteselect gen_id(gen_new,1) as num, new_tbl.elm_prof,
new_tbl.elm_mat from (select distinct elm_prof , elm_mat from ELEMENTS
) as new_tbl) as a
As an alternative I tried this with no success..
Quoteset generator gen_new to 0;
with CTE as
(
  select gen_id(gen_new,1) as num, new_tbl.elm_prof, new_tbl.elm_mat
from (select distinct elm_prof , elm_mat from ELEMENTS ) as new_tbl
)
update elements e set e.PROP_TYPE = cte.num where e.ELM_MAT =
cte.elm_mat

----------------------------------------------------------------------
SteveF · 2011-09-06 20:58

Quote from: Ronan on September 05, 2011, 07:22:48 pmhi ,

I've managed to comprise the intended query to some extended but,
either I'm so tired or missing something obvious, I couldn't succeed
to form the final shape of query.

This code returns a dataset aliased as new_tbl
Code: [Select]set generator gen_new to 0 ;
select gen_id(gen_new,1) as num, new_tbl.elm_prof, new_tbl.elm_mat
from (select distinct elm_prof , elm_mat from ELEMENTS) new_tbl
For futher I need to do the following
Code: [Select]Update elements as e set e.prop_type=new_tbl.num where
????
As a result I couldn't place a dataset after the where command so how
to merge those 2 queries to form the final shape?

Any clue will be appreciated.

PMFJI, but what is it you wish to do?  The syntax in the SQL
statements is unclear.

Please step back from SQL and explain in plain language what the SQL
is supposed to do.

One does not usually include a gen_id() in a SELECT statement; they're
more typically used in INSERT statements.

Thanks,
Steve

----------------------------------------------------------------------
Ronan · 2011-09-06 21:26

Just only the one table is questioned.

This is what I've in hand.
table: ELEMENTS
http://tinypic.com/r/1ytorb/7

This is what I achieve with generator , simply row numbering for
selected fields
Table : DERIVED TABLE
http://tinypic.com/r/ddf7lw/7

So based on the data from DERIVED TABLE how to fill/update the
ELEMENTS table prop_type field with num field from DERIVED TABLE where
matching occurs.

----------------------------------------------------------------------
BigChimp · 2011-09-07 06:40

Hi Ronan,

My browser couldn't load the images.

Couple of remarks:
1. Why don't you head over to Firebirdsql.org and get help through the
mailing list? Might give a higher chance of working help.
2. Seems like you're trying to get some kind of order in the fields,
maybe some kind of autonumber/autoinc like functionality? If that is
the case, it might be easier to have a trigger on the Firebird side
insert a generated value for you (see e.g. how that is done in the
employee.fdb sample database, customer table)
3. It's still not clear what you want exactly (without the pictures):
"simply row numbering for selected fields": does that mean all records
in a table, the fields in a select statement, etc? Could you explain a
bit more what you actually want to achieve? Perhaps an easier solution
is not in SQL but in Lazarus?

----------------------------------------------------------------------
Ronan · 2011-09-07 11:44

Code: [Select]1. Why don't you head over to Firebirdsql.org and get
help through the mailing list? Might give a higher chance of working
help.For 2 days I didn't get any acceptable answers from that
forementioned forum, even though its in my native language   that
doesn't always normally means that they'll give me a clue how to hit
the jackpot ( so now I'm trying with English firebird sql support
yahoo groups as well.) and  decided to post here.

What I want to achieve is: Self-update-table based on its specific
fields,
That is, assigning same numbers(let's say to its num_field) starting
from 1 to rows where they have specific fields(actually two elm_mat
and elm_mat) in common.

Hope its well versed,

----------------------------------------------------------------------
SteveF · 2011-09-07 21:39

Quote from: Ronan on September 07, 2011, 01:44:11 pmCode: [Select]1.
Why don't you head over to Firebirdsql.org and get help through the
mailing list? Might give a higher chance of working help.For 2 days I
didn't get any acceptable answers from that forementioned forum, even
though its in my native language   that doesn't always normally means
that they'll give me a clue how to hit the jackpot ( so now I'm trying
with English firebird sql support yahoo groups as well.) and  decided
to post here.

What I want to achieve is: Self-update-table based on its specific
fields,
That is, assigning same numbers(let's say to its num_field) starting
from 1 to rows where they have specific fields(actually two elm_mat
and elm_mat) in common.

Hope its well versed,

Based on the data and what you say you're trying to do, you need
something more like:
UPDATE ELEMENTS INNER JOIN "DERIVED TABLE" ON (ELEMENTS.ELM_MAT =
"DERIVED TABLE".ELM_MAT AND
ELEMENTS.ELM_PROF = "DERIVED TABLE".ELM_PROF) SET ELEMENTS.PROP_TYPE =
"DERIVED TABLE".NUM;

If your table called DERIVED TABLE truly does have a space as part of
the name, you'll need to wrap it in the double quotes as above; you
may need to do the same for the field names which have an underscore
in them ("_").  NOTE: This is untested, and may not work as intended.

Steve