Re: [firebird-support] Re: UPDATE OR INSERT in Firebird 3

[Daniel Miller dmil...@amfes.com [firebird-support]]

I experimented with IDENTITY fields myself - based on my experience and 
some older comments here I quit using them. I think they are presently 
suitable for "quick" tables - something that will be append only like a log.


If you're looking for more advanced features, like supporting UPDATE OR 
INSERT (which I use myself), then you're much better off explicitly 
writing the appropriate insert and update triggers. Possibly version 4 
will implement IDENTITY better but I don't use it all now.


Daniel

On 11/20/2020 5:59 AM, 'Walter R. Ojeda Valiente' 
sistemas2000profesio...@gmail.com [firebird-support] wrote:



Hello Herman

I had used UPDATE OR INSERT for several years in several hundred of 
tables. No problem...with Firebird 2.5.x


But with Firebird 3.0.x I can not make it work.

If I write the identity column, doesn't work. If I don't write the 
identity column, doesn't work..


Thank you very much for your answer.

Greetings.

Walter.



On Fri, Nov 20, 2020 at 6:16 AM Herman Viaene herman.via...@edpnet.be 
 [firebird-support] 
> wrote:


Op 20/11/2020 om 01:04 schreef 'Walter R. Ojeda Valiente'
sistemas2000profesio...@gmail.com
 [firebird-support]:

I use firebird3 from libreoffice base, not directly, but in that
way I can make (and have done) such insert stattements.

CREATE TABLE MYTABLE (
  COLUMN1 INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1)
NOT NULL,
  COLUMN2 VARCHAR(20));


ALTER TABLE MYTABLE ADD CONSTRAINT PK_MYTABLE PRIMARY KEY (COLUMN1);


I have similar tables



Then, is I write it:
UPDATE OR INSERT INTO MYTABLE (COLUMN1, COLUMN2) VALUES (NULL,
'TEST')

The message is:
Validation error for column "MYTABLE"."COLUMN1", value "*** null
***".


That is quite normal and correct, since you define column1 as not
null, and now you try to insert a null value in it.



And if I write it:
UPDATE OR INSERT INTO MYTABLE (COLUMN2) VALUES ('TEST')


I have written similar insert statements and they work OK, BUT: I
write either an insert statement OR an update statement. The two
are fundamentally different as to the primary key handling:
in an insert statement you do not give a value to the PK, since it
is generated by the system.
in an update statement you have to define on which rows (all or by
giving a value for the PK or by a select statement) the update has
to apply.

I must confess I've never tried to use "UPDATE OR INSERT", and I
wonder about it.  I googled and checked the syntax, and I think
your statement will try to match your value 'TEST' against the PK,
and that does not work of course. You would have to use the
"matching (column2)" in your statement.

Just my 2c

Herman Viaene





The message is:
UPDATE OR INSERT field list does not match primary key of table
MYTABLE.


There is some solution? Or it is impossible to use an IDENTITY
column with an UPDATE OR INSERT?

Greetings.

Walter.




On Thu, Nov 19, 2020 at 8:55 PM Walter R. Ojeda Valiente
mailto:sistemas2000profesio...@gmail.com>> wrote:

I forget to say that my IDENTITY column is the Primary Key of
MyTable, therefore if I don't write it an error happens
because...the table needs a Primary Key.

On Thu, Nov 19, 2020 at 8:52 PM Walter R. Ojeda Valiente
mailto:sistemas2000profesio...@gmail.com>> wrote:

Hello everybody

A long, long time without writing neither reading this
group, mostly because all that I need about Firebird I knew.

But now, I have a doubt.

With Firebird 2.5.x I can have an auto-incremental column
and use it in an UPDATE OR INSERT, but such thing is not
possible with Firebird 3.

With Firebird 2.5.x a generator and a trigger are created
and the value of the column is put automatically. So, if
I write:
UPDATE OR INSERT INTO MyTable (MyColumn1, MyColumn2)
VALUES (NULL, 12345);

and MyColumn1 is auto-incremental its value is set for
the Firebird engine.

but...if I use the new IDENTITY type in Firebird 3 I
always have an error, writing MyColumn1 in the UPDATE OR
INSERT or not writing.

So, my question is:

Is it possible to use UPDATE OR INSERT with an IDENTITY
column?

Thanks in advance.

Greetings.

Walter.

[firebird-support] Identity vs Before Insert Trigger

[Daniel Miller dmil...@amfes.com [firebird-support]]

The fault or misunderstanding is probably mine, but:

I thought the "identity" definition was functionally the same as the 
before-insert with generator. Particularly with regards to handling null 
values during inserts. However, given:

create table TEST1 (
 TEST_ID integer generated by default as identity primary key
 TEST_NAME varchar(10)
);

Attempting to execute:

update or insert into TEST1 (TEST_ID, TEST_NAME)
   values (null, 'abc') matching (TEST_ID)

results in:
validation error for column "TEST1"."TEST_ID", value "*** null ***".


However, the same query works without issue with a before-insert trigger 
using a generator. My question - is this intended behavior? Or a bug in 
the identity implementation?
-- 
Daniel







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Will we move when yahoo Groups close theirs doors?

[Daniel Miller dmil...@amfes.com [firebird-support]]

I'm curious...why not use Sourceforge mailing lists?

Daniel


On 10/21/2019 8:43 AM, Lester Caine les...@lsces.uk [firebird-support] 
wrote:
> On 21/10/2019 16:05, Kevin Stanton kevin.stan...@rdb-solutions.com
> [firebird-support] wrote:
>> Oregon Delphi Users Groups has the same issue.  Looks like that group is 
>> moving tohttp://groups.io
> 
> Virtually all of the email lists I have used since eGroup days are
> affected by this farce. It would seem that the email messaging side will
> simply be maintained so we can just carry on using it, but the one thing
> *I* don't fancy is having several solutions to the problem depending on
> just what each list decides. Various previous attempts to switch one or
> other list to on alternative has failed since the alternative simply
> never worked but there is nothing that provides the flexibility and I
> switched off groups.io previously and blocked it because of problems
> caused by previous 'upgrade' attempts, so until yahoo actually kills the
> email service I'll probably just stay and eventually not bother
> monitoring any of the 30 odd lists ...
> 

Re: [firebird-support] Re: Off-Topic: Firebird future

[Daniel Miller dmil...@amfes.com [firebird-support]]

On 10/14/2019 11:42 PM, Hamish Moffatt ham...@risingsoftware.com 
[firebird-support] wrote:
> 
> 
> On 11/10/19 10:52 am, Daniel Miller dmil...@amfes.com [firebird-support] 
> wrote:
>>
>>
>> There are a few (Windows-only) applications that are quite powerful -
>> but honestly the primary tool I've used has been FirebirdWebAdmin
>> (formerly IBWebAdmin). I'm generally working from a remote site and
>>
> 
> Thanks for the tip, I didn't know about this tool. It hasn't had a 
> release in a few years though. Does it work with current PHP (7.x)?
> 

Yes.

--
Daniel

Re: [firebird-support] Re: Off-Topic: Firebird future

[Daniel Miller dmil...@amfes.com [firebird-support]]

On 10/9/2019 9:08 AM, pablo sanchez pab...@adinet.com.uy 
[firebird-support] wrote:
> I use Firebird since 0.x version (open interbase?) .
> 
> It's the most versatile db I've found so far : embedded and full server,
> linux(99%) and windows, odbc or jaybird.
> 
> Installs in 5 minutes with db configuration.
> 
> I never had a performance issue that was not my fault and biggest db's
> are 80GB+ with ~300 concurrent sessions .
> 
> The "tooling" is becoming a problem for me as I use less and less
> ms-windows. There are no linux tools I can bare to use daily as
> Flamerobin is, and I must start migrating to v3 ASAP. I'm starting to
> evaluate windows tools within WINE and java based tools .
> 

There are a few (Windows-only) applications that are quite powerful - 
but honestly the primary tool I've used has been FirebirdWebAdmin 
(formerly IBWebAdmin). I'm generally working from a remote site and 
connecting via SSH or HTTP to the server - so having a web-based GUI 
running on the database server is a natural fit for me.

What specific features are you looking for that aren't available in this 
manner?

--
Daniel

Re: [firebird-support] Scaling Firebird - Azure

[Daniel Miller dmil...@amfes.com [firebird-support]]

On 10/6/2019 9:49 AM, Rune Horneland rune.hornel...@kravia.net 
[firebird-support] wrote:
> 
> 
> Hi,
> We are running Firebird 2 in an Azure VM. It can only take so much in 
> terms of concurrent connections.
> What top-level advice would you give to scale this?
> 
> We are connecting to it from a .NET core Middleware using Azure VMs.
> The architecture of the middleware is quite monolithic. We are 
> considering rewriting it with Microservices and Azure functions or 
> similar architecure, but are unsure how we could scale the Firebird DB 
> or connections itself.
> Multiple casehandlers in our company use it via a Delphi-based Windows 
> application at the other end, with a vendor maintaining the Firebird DB 
> and Windows app development, so we are locked into using Firebird.

"Locked into using Firebird"...you say that like it's a bad thing!

Can you provide some more details of your usage? Things like:

1. Current number of current connections
2. Desired number of connections
3. Current database size
4. Details of the virtual host - O/S, RAM, filesystem, HD type.


--
Daniel

Re: [firebird-support] Why I cannot remove FK constraint

[Daniel Miller dmil...@amfes.com [firebird-support]]

On 1/25/2019 7:11 PM, Daniel Miller dmil...@amfes.com [firebird-support] 
wrote:



On 1/24/2019 7:56 AM, respekt...@post.cz [firebird-support] wrote:

Hello

I have problem with part of our databases.
All our databases should have the same structure, but in some of them 
I cannot drop constraint


ALTER TABLE VYKONY_AUT DROP CONSTRAINT FK_VYKONY_AUT_VYKON

chyba: java.sql.SQLSyntaxErrorException: unsuccessful metadata update; ERASE 
RDB$RELATION_CONSTRAINTS failed; action cancelled by trigger (1) to preserve 
data integrity; Cannot delete trigger used by a CHECK Constraint; At trigger 
'RDB$TRIGGER_34' [SQLState:42000, ISC error code:335544351]
java.sql.SQLSyntaxErrorException: unsuccessful metadata update; ERASE 
RDB$RELATION_CONSTRAINTS failed; action cancelled by trigger (1) to preserve 
data integrity; Cannot delete trigger used by a CHECK Constraint; At trigger 
'RDB$TRIGGER_34' [SQLState:42000, ISC error code:335544351]


Try this - see if either of these queries show you what might need to be 
fixed.  Otherwise you'll need to share your database structure:


    select * from RDB$RELATION_CONSTRAINTS where RDB$RELATION_NAME = 
'VYKONY_AUT';


and

    select * from RDB$RELATION_CONSTRAINTS where RDB$RELATION_NAME = 
'FK_VYKONY_AUT_VYKON';


--
Daniel

Re: [firebird-support] Why I cannot remove FK constraint

[Daniel Miller dmil...@amfes.com [firebird-support]]

On 1/24/2019 7:56 AM, respekt...@post.cz [firebird-support] wrote:



Hello

I have problem with part of our databases.
All our databases should have the same structure, but in some of them 
I cannot drop constraint


ALTER TABLE VYKONY_AUT DROP CONSTRAINT FK_VYKONY_AUT_VYKON

chyba: java.sql.SQLSyntaxErrorException: unsuccessful metadata update; ERASE 
RDB$RELATION_CONSTRAINTS failed; action cancelled by trigger (1) to preserve 
data integrity; Cannot delete trigger used by a CHECK Constraint; At trigger 
'RDB$TRIGGER_34' [SQLState:42000, ISC error code:335544351]
java.sql.SQLSyntaxErrorException: unsuccessful metadata update; ERASE 
RDB$RELATION_CONSTRAINTS failed; action cancelled by trigger (1) to preserve 
data integrity; Cannot delete trigger used by a CHECK Constraint; At trigger 
'RDB$TRIGGER_34' [SQLState:42000, ISC error code:335544351]


From the error messages above - are there any triggers defined that are 
looking at this table/field?


--
Daniel

[firebird-support] Altering views

[Daniel Miller dmil...@amfes.com [firebird-support]]

This is a bit of a philosophical question - as such there are probably many 
opinions. But I'll ask anyway.

With relational databases there can be associations between tables that are 
often repeated to where it is desirable to store and re-use them. During 
app development, the use and therefore the structure of the relevant 
database often changes. The problem that prompted this question:

I have several tables linked via primary & foreign keys. Surprise. Their 
relationship is such (one-to-many and one-to-one) that usually joins are 
required. The fact that Views are (or can be) manipulated transparently vs 
tables caused me to setup a number of them. I also have some views that are 
dependent on other views. One of those had a trigger defined to make the 
view updateable. One of those app changes I mentioned above came up - and 
via Flamerobin I attempted to modify the view. The fact that Flamerobin 
seemed to lock up during this attempt, and my trigger being dropped without 
recovery (I guess no rollback for that), is secondary.

>From the usage point of application development and no end user direct 
interaction with the database - preferably with specific arguments as to 
why - I'm asking what is "better" for reusable SQL. And whether the same 
statements/structures should be used for both retrieval and updates. 
Options I can think of:

- SQL statements defined in app - possibly via constants or other central 
config.
- Views
- Stored procedures

--
Daniel

Re: [firebird-support] Is Firebird favor Intel's CPU?

[Daniel Miller dmil...@amfes.com [firebird-support]]

I am not aware of any such bias with Firebird but it certainly can exist 
with other software.


Intel provides what I am told is an excellent compiler - which they 
provide for free - and many software companies utilize it. That compiler 
has long been known to generate executables that are crippled when run 
on non-Intel CPU's.  There have been a quantity of reports, possibly 
some legal action, and I don't know that anything has changed.


However - I believe the binaries provided for Firebird are not generated 
by that compiler so it's not an issue.  In your case, based on your 
description, you have similar systems with similar drives so unless 
there was *significant* calculations required I'm not surprised you have 
similar performance.


Daniel
 


On 11/6/2018 6:39 PM, trsk...@yahoo.com [firebird-support] wrote:



Hello all,

I ran an update query on a database with a computer and a notebook.

CPU's computer is Phenom 1075T, while notebook is i5 2557M.

Query is very simple, something like : update tbl set ln = 9;

Firebird  on Computer is SS 3.0.3, on notebook SS 3.0.2, both runs on 
Windows x64, both using SSD with roughly, same speed.


I thought, query will runs much faster on a computer, but to my 
surprise, it runs almost the same time on both machine.


Query runs in a single thread, on computer, runs around 3-3.5GHz, on 
notebook, runs about 2.7GHz.


I am planning to upgrade my computer to ryzen, but with this result, 
make me wonder if  Firebird favor intel's cpu.


Regards,
Anto

Re: [firebird-support] Show Columns

[Daniel Miller dmil...@amfes.com [firebird-support]]

That...is an unbelievably helpful page and that query should be made 
part of the official docs!


Daniel

On 3/23/2018 9:18 AM, edmende...@gmail.com [firebird-support] wrote:



Daniel,

Not sure if this answers your question but I’ve had situations where I 
needed a way to get column details and table ddl within our 
application. We ended up creating a stored procedure that would return 
that information.


The following linked helped me with understanding what needed to be 
done in order to create that stored procedure.


https://www.alberton.info/firebird_sql_meta_info.html

HTH,

Edward

*From:* firebird-support@yahoogroups.com bird-supp...@yahoogroups.com>

*Sent:* Thursday, March 22, 2018 7:53 PM
*To:* firebird-support@yahoogroups.com
*Subject:* [firebird-support] Show Columns

Using some system table queries, is there a view or stored procedure that
can, in "full", replicate the output of "show columns from table" 
available

from other servers?

Ideally this would be a "drop in" alternative to the "show columns" sql
statement that returns the same data in the same structure.

There are a few examples I can draw from, including firebirdwebadmin, 
but I

don't want to reinvent it if I don't have to.

--
Daniel

[firebird-support] Show Columns

[Daniel Miller dmil...@amfes.com [firebird-support]]

Using some system table queries, is there a view or stored procedure that 
can, in "full", replicate the output of "show columns from table" available 
from other servers?

Ideally this would be a "drop in" alternative to the "show columns" sql 
statement that returns the same data in the same structure.

There are a few examples I can draw from, including firebirdwebadmin, but I 
don't want to reinvent it if I don't have to.

--
Daniel

Re[2]: [firebird-support] How I can speed up this query

['Daniel Miller' dmil...@amfes.com [firebird-support]]

On 10/12/2017 12:24:11 AM, "Luigi Siciliano luigi...@tiscalinet.it 
[firebird-support]"  wrote:

>Hallo,
>
>Il 11/10/2017 20.38, setysvar setys...@gmail.com [firebird-support] ha
>scritto:
>>I think this should give the same result as your query, whether or not
>>it is any quicker, I simply do not know (but I would love to hear if 
>>it
>>made any difference).
>
>Your query is to slow, flamerobin tells 4.360s. :(
>>PLAN (PNC2 NATURAL)
>>
>>seems to be your problem (PNC on the other hand, seems OK).
>>
>>Hence, if PARITA and/or SCADENZA are selective, I would recommend that
>>you create an index for either or both of these fields. That way, I
>>would assume your original query to become a lot quicker.
>Ok, I create an index for PARTITA and SCADENZA fields for PNC and Your
>query speed up, Flamerobin now tells 0.060s :)
>
>And, my query, now, flamerobin tells 0.045s :))
>
>The secondary index are automatically maintained by server, is it right
>to do a periodically manually maintenance for it's?
>
It would be interesting to compare the plans for both your original 
query syntax and Set's suggestion - see where the difference is.

As for index maintenance, see http://www.firebirdfaq.org/faq167/

With a significant amount of deletes database maintenance becomes more 
important.  Static tables, or tables that generally just grow, don't 
experience much fragmentation.
--
Daniel

Re[2]: [firebird-support] select column if it exists

['Daniel Miller' dmil...@amfes.com [firebird-support]]

I don't think you could express it in the SELECT directly (someone else 
will probably build a statement refuting that) but you could do:

SELECT r.RDB$FIELD_NAME FROM RDB$RELATION_FIELDS r where 
r.RDB$RELATION_NAME=

and then process that result set to build your appropriate SELECT for 
the target table.  So I suppose you could build a statement with a 
quantity of derived sets using IIF and WHERE EXISTS - the question is do 
you need to express this as single SELECT, or can you accomplish your 
goal either via your calling program or a stored procedure?
--
Daniel


On 10/9/2017 1:36:20 AM, "Elmar Haneke el...@haneke.de 
[firebird-support]"  wrote:

>
>>Is there a way to include a column in a SELECT but substitute a value 
>>if
>>it doesn't? I need my code to work with different versions of my db 
>>schema.
>>
>>eg I want to SELECT A, B, C, ... but C might not exist.
>>
>>SELECT * would work of course, except it will fetch a bunch of columns 
>>I
>>don't need.
>
>You can read the list of fields available in advance and modify your
>query ommitting missing columns.
>
>
>Elmar
>
>
>
>
>Posted by: Elmar Haneke 
>
>
>++
>
>Visit http://www.firebirdsql.org and click the Documentation item
>on the main (top) menu.  Try FAQ and other links from the left-side 
>menu there.
>
>Also search the knowledgebases at 
>http://www.ibphoenix.com/resources/documents/
>
>++
>
>
>Yahoo Groups Links
>
>
>

Re[2]: {Disarmed} [firebird-support] Using FB2.5 with NFS for Virtualbox VM

['Daniel Miller' dmil...@amfes.com [firebird-support]]

The performance of vboxsf is horrible - that's why there are so many 
recommendations to use NFS or CIFS for guests.  So again I'm asking if 
there's any issue with accessing the .fdb via NFS - given the exclusive 
access I've described.

I don't see why there would be...other than ensuring that the access is 
indeed exclusive.

--
Daniel

On 10/5/2017 1:02:44 AM, "Elmar Haneke el...@haneke.de 
[firebird-support]"  wrote:

>
>>
>>
>>I have a single Linux host server with several Virtualbox guest VM's.
>>  I've been attempting to move everything possible from the host to one
>>of the guests.
>>
>>One of the last holdovers is Firebird.  The reason is I use NFS to
>>mount host folders within the guest for critical data - and the .fdb
>>certainly counts.
>
>NFS is an networking system not part of virtualbox.
>Virtualbox has an mechanism of its own to allow gests access to host
>filesystem you shoulkd try that instead.
>
>>So for my use case - I have a single Firebird server instance.  There
>>will never be more than one copy of Firebird running.  All write and
>>99.99% read access to the .fdb would be through that single Firebird
>>instance (except for automated remote backup operations running on the
>>host).
>
>It's not an good idea to backup database file while firebird-server is
>running on that file. You should use firebirds backup functionality 
>instead.
>
>>Am I "safe", possibly even correct, in using RemoteFileOpenAbility in
>>this use case?
>
>This option is for pepole knowing what they are doing - those pepole
>usually do not ask if it is ok.
>
>
>Elmar
>
>
>
>
>Posted by: Elmar Haneke 
>
>
>++
>
>Visit http://www.firebirdsql.org and click the Documentation item
>on the main (top) menu.  Try FAQ and other links from the left-side 
>menu there.
>
>Also search the knowledgebases at 
>http://www.ibphoenix.com/resources/documents/
>
>++
>
>
>Yahoo Groups Links
>
>
>

[firebird-support] Using FB2.5 with NFS for Virtualbox VM

['Daniel Miller' dmil...@amfes.com [firebird-support]]

I have a single Linux host server with several Virtualbox guest VM's.  
I've been attempting to move everything possible from the host to one of 
the guests.


One of the last holdovers is Firebird.  The reason is I use NFS to mount 
host folders within the guest for critical data - and the .fdb certainly 
counts.  My goal is to have all critical data stored on a host partition 
or folder - not in guest virtual drives - which I (possibly mistakenly) 
believe improves performance and recoverability.


So for my use case - I have a single Firebird server instance.  There 
will never be more than one copy of Firebird running.  All write and 
99.99% read access to the .fdb would be through that single Firebird 
instance (except for automated remote backup operations running on the 
host).


Am I "safe", possibly even correct, in using RemoteFileOpenAbility in 
this use case?


--
Daniel

Re[4]: [firebird-support] Find duplicate multi-row entries

['Daniel Miller' dmil...@amfes.com [firebird-support]]

After further review...I found I goofed. This one actually gives the 
results I was expecting - but I'm still asking about how to join with 
it.


CREATE PROCEDURE CHECK_ROUTE_EXISTS (
CHECK_NODE smallint )
RETURNS (
NODE smallint,
ROUTE_UPDATED timestamp )
AS
declare variable RU1 timestamp;
declare variable RU2 timestamp;
declare variable RU3 timestamp;
begin
  for select RH1.ROUTE_UPDATED
from ROUTE_HISTORY RH1
join ROUTES R1 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
where RH1.NODE = :CHECK_NODE and RH1.ROUTE_INDEX = 1
order by NODE, ROUTE_UPDATED, ROUTE_INDEX
into RU1 do
  for select RH2.ROUTE_UPDATED
  from ROUTE_HISTORY RH2
  join ROUTES R2 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
  where RH2.NODE = :CHECK_NODE and RH2.ROUTE_UPDATED=:RU1 and 
RH2.ROUTE_INDEX = 2

  order by NODE, ROUTE_UPDATED, ROUTE_INDEX
  into RU2 do
for select RH3.ROUTE_UPDATED
from ROUTE_HISTORY RH3
join ROUTES R3 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
where RH3.NODE = :CHECK_NODE and RH3.ROUTE_UPDATED=:RU2 and 
RH3.ROUTE_INDEX = 3

order by NODE, ROUTE_UPDATED, ROUTE_INDEX
into RU3 do
  for select RH4.ROUTE_UPDATED
  from ROUTE_HISTORY RH4
  join ROUTES R4 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
  where RH4.NODE = :CHECK_NODE and RH4.ROUTE_UPDATED=:RU3 and 
RH4.ROUTE_INDEX = 4

  order by NODE, ROUTE_UPDATED, ROUTE_INDEX
  into :ROUTE_UPDATED do begin
NODE = CHECK_NODE;
suspend;
  end
END^

--
Daniel

On 10/1/2017 3:31:57 PM, "'Daniel Miller' dmil...@amfes.com 
[firebird-support]" <firebird-support@yahoogroups.com> wrote:





Maybe a little progress.

This procedure does exactly what I want...but not exactly HOW I want:

CREATE PROCEDURE CHECK_ROUTE_EXISTS (
CHECK_NODE smallint )
RETURNS (
NODE smallint,
ROUTE_UPDATED timestamp )
AS
declare variable PLACEHOLDER timestamp;
begin
  /* Assignment for valid return value */
  NODE = CHECK_NODE;
  for select RH1.ROUTE_UPDATED
from ROUTE_HISTORY RH1
join ROUTES R1 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
where RH1.NODE = :CHECK_NODE and RH1.ROUTE_INDEX = 1
order by NODE, ROUTE_UPDATED, ROUTE_INDEX
into PLACEHOLDER do
  for select RH2.ROUTE_UPDATED
  from ROUTE_HISTORY RH2
  join ROUTES R2 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
  where RH2.NODE = :CHECK_NODE and RH2.ROUTE_INDEX = 2
  order by NODE, ROUTE_UPDATED, ROUTE_INDEX
  into PLACEHOLDER do
for select RH3.ROUTE_UPDATED
from ROUTE_HISTORY RH3
join ROUTES R3 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
where RH3.NODE = :CHECK_NODE and RH3.ROUTE_INDEX = 3
order by NODE, ROUTE_UPDATED, ROUTE_INDEX
into PLACEHOLDER do
  for select RH4.ROUTE_UPDATED
  from ROUTE_HISTORY RH4
  join ROUTES R4 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
  where RH4.NODE = :CHECK_NODE and RH4.ROUTE_INDEX = 4
  order by NODE, ROUTE_UPDATED, ROUTE_INDEX
  into :ROUTE_UPDATED do suspend;
  /* If matched across 4 levels this is a duplicate.
  otherwise ROUTE_UPDATED will be NULL */
END

So...this gives me a list of all duplicates for a given NODE. Since I 
now have something somewhat functional - some optimization questions:


1.  I don't think I would gain anything by tweaking the above into 
using recursion - but if someone thinks so...please share.


2.  I would like to be able to run this against/with other queries.  
Among other reasons - I want to look at doing a bulk delete in my 
history table.  At the moment, I can "manually" run something like:
SELECT p.NODE, p.ROUTE_UPDATED FROM CHECK_ROUTE_EXISTS 
('CHECK_NODE') p;


but I'm not sure how to apply this against my entire NODES table, nor 
how to run a delete with it.  I tried:
delete from ROUTE_HISTORY H where H.NODE=5003 and H.ROUTE_UPDATED 
in (select ROUTE_UPDATED from CHECK_ROUTE_EXISTS(5003))


but that broke my poor server's brain - I had to stop the statement.

--
Daniel

On 9/30/2017 9:39:31 PM, "Daniel Miller" <dmil...@amfes.com> wrote:


Set,

Thank you. This definitely does something...to be honest I'm not 
certain exactly what... I'm definitely going to need to study this for 
a while. I sincerely appreciate the effort - though I think I am going 
to reconsider my table structure.


--
Daniel

On 9/28/2017 1:42:18 PM, "setysvar setys...@gmail.com 
[firebird-support]" <firebird-support@yahoogroups.com> wrote:


Sorry again, Daniel, I had to get to a place where I could test 
things before replying. My problem was that what I'd done before was 
to try to find identical sets, not trying to find unique sets, and 
that made me mess up the logic. I hope this query will get you what 
you want (and this time I've tested before replying):


with tmp(NODE, R

Re[3]: [firebird-support] Find duplicate multi-row entries

['Daniel Miller' dmil...@amfes.com [firebird-support]]

Maybe a little progress.

This procedure does exactly what I want...but not exactly HOW I want:

CREATE PROCEDURE CHECK_ROUTE_EXISTS (
CHECK_NODE smallint )
RETURNS (
NODE smallint,
ROUTE_UPDATED timestamp )
AS
declare variable PLACEHOLDER timestamp;
begin
  /* Assignment for valid return value */
  NODE = CHECK_NODE;
  for select RH1.ROUTE_UPDATED
from ROUTE_HISTORY RH1
join ROUTES R1 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
where RH1.NODE = :CHECK_NODE and RH1.ROUTE_INDEX = 1
order by NODE, ROUTE_UPDATED, ROUTE_INDEX
into PLACEHOLDER do
  for select RH2.ROUTE_UPDATED
  from ROUTE_HISTORY RH2
  join ROUTES R2 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
  where RH2.NODE = :CHECK_NODE and RH2.ROUTE_INDEX = 2
  order by NODE, ROUTE_UPDATED, ROUTE_INDEX
  into PLACEHOLDER do
for select RH3.ROUTE_UPDATED
from ROUTE_HISTORY RH3
join ROUTES R3 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
where RH3.NODE = :CHECK_NODE and RH3.ROUTE_INDEX = 3
order by NODE, ROUTE_UPDATED, ROUTE_INDEX
into PLACEHOLDER do
  for select RH4.ROUTE_UPDATED
  from ROUTE_HISTORY RH4
  join ROUTES R4 using (NODE, ROUTE_INDEX, LINK_NODE, QUALITY)
  where RH4.NODE = :CHECK_NODE and RH4.ROUTE_INDEX = 4
  order by NODE, ROUTE_UPDATED, ROUTE_INDEX
  into :ROUTE_UPDATED do suspend;
  /* If matched across 4 levels this is a duplicate.
  otherwise ROUTE_UPDATED will be NULL */
END

So...this gives me a list of all duplicates for a given NODE. Since I 
now have something somewhat functional - some optimization questions:


1.  I don't think I would gain anything by tweaking the above into using 
recursion - but if someone thinks so...please share.


2.  I would like to be able to run this against/with other queries.  
Among other reasons - I want to look at doing a bulk delete in my 
history table.  At the moment, I can "manually" run something like:
SELECT p.NODE, p.ROUTE_UPDATED FROM CHECK_ROUTE_EXISTS 
('CHECK_NODE') p;


but I'm not sure how to apply this against my entire NODES table, nor 
how to run a delete with it.  I tried:
delete from ROUTE_HISTORY H where H.NODE=5003 and H.ROUTE_UPDATED in 
(select ROUTE_UPDATED from CHECK_ROUTE_EXISTS(5003))


but that broke my poor server's brain - I had to stop the statement.

--
Daniel

On 9/30/2017 9:39:31 PM, "Daniel Miller" <dmil...@amfes.com> wrote:


Set,

Thank you. This definitely does something...to be honest I'm not 
certain exactly what... I'm definitely going to need to study this for 
a while. I sincerely appreciate the effort - though I think I am going 
to reconsider my table structure.


--
Daniel

On 9/28/2017 1:42:18 PM, "setysvar setys...@gmail.com 
[firebird-support]" <firebird-support@yahoogroups.com> wrote:





Sorry again, Daniel, I had to get to a place where I could test things 
before replying. My problem was that what I'd done before was to try 
to find identical sets, not trying to find unique sets, and that made 
me mess up the logic. I hope this query will get you what you want 
(and this time I've tested before replying):


with tmp(NODE, ROUTE_UPDATED) as
(select distinct r.NODE, rh.ROUTE_UPDATED
 from routes r
 left join route_history rh on r.NODE = rh.NODE),
new_routes(NODE) as
(select distinct t.NODE
 from tmp t
 where exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX

and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct 
from rh.QUALITY
and t.ROUTE_UPDATED = 
rh.ROUTE_UPDATED

  where t.NODE = r.NODE
and rh.NODE is null)
or exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX = rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct from 
rh.QUALITY

  where t.NODE = rh.NODE
and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

HTH,
Set

Den 28.09.2017 11:00, skrev 'Daniel Miller' dmil...@amfes.com 
[firebird-support]:

This appears no different than a simple:

select * from routes

So...not there yet.  I think what I'm fighting is the lack of pivot 
or crosstab functionality - and I may have no choice (though it 
offends me deeply) but to setup my archive table for this comparison. 
 Either via the hated structure of column names ROUTE1, ROUTE2, 
ROUTE8...or the only slightly less offensive VARCHAR concatenation.


A concaten

[firebird-support] Suggested table structure for otherwise multi-row sets

['Daniel Miller' dmil...@amfes.com [firebird-support]]

Well...one of the joys of development is recognizing a design that was 
perfect...is less so in a given context.  And what I thought was an 
elegant & correct solution appears to not lend itself to answering the 
questions I'm now asking.  So...I'm asking for suggestions on my next 
revision.


My current structure:

A parent table
CREATE TABLE NODES
(
  NODE smallint NOT NULL,
  CONSTRAINT PK_NODES PRIMARY KEY (NODE)
);

A child table
CREATE TABLE ROUTES
(
  NODE smallint NOT NULL,
  ROUTE_INDEX smallint NOT NULL,
  LINK_NODE smallint NOT NULL,
  QUALITY smallint,
  CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
);
ALTER TABLE ROUTES ADD ROUTE_PK COMPUTED BY (cast(NODE as char(4)) || 
cast(ROUTE_INDEX as char(1)));

ALTER TABLE ROUTES ADD CONSTRAINT FK_ROUTES_NODE
  FOREIGN KEY (NODE) REFERENCES NODES (NODE) ON UPDATE CASCADE ON DELETE 
CASCADE;


And an archive table
CREATE TABLE ROUTE_HISTORY
(
  NODE smallint NOT NULL,
  ROUTE_INDEX smallint NOT NULL,
  LINK_NODE smallint NOT NULL,
  QUALITY smallint,
  ROUTE_UPDATED timestamp NOT NULL,
  CONSTRAINT PK_ROUTE_HISTORY PRIMARY KEY 
(NODE,ROUTE_INDEX,ROUTE_UPDATED)

);
ALTER TABLE ROUTE_HISTORY ADD CONSTRAINT FK_ROUTE_HISTORY_NODE
  FOREIGN KEY (NODE) REFERENCES NODES (NODE) ON UPDATE CASCADE ON DELETE 
CASCADE;
CREATE UNIQUE INDEX IDX_ROUTE_HISTORY_LIST ON ROUTE_HISTORY 
(NODE,ROUTE_UPDATED,ROUTE_INDEX);


The above structure worked perfectly...until I wanted to identify, 
filter, or delete duplicate entries. Because a "true" duplicate required 
matching across multiple rows this became...at best rather complicated. 
So I think I need to "flatten" my structure - I'm just not sure of the 
"best" method.


Please keep in mind the following:
1. Each NODE can have from 0 to 8 ROUTES active at any time.
2. ROUTES get updated anywhere from every few seconds to a few weeks 
depending on real-world activity.
3. The "detail" fields shown are a subset - there are actually 5 more 
(though they are all of type SMALLINT if that makes any difference).
4. I'm still trying to decide how to handle duplicate entries in the 
archive table. At a minimum, I need to be able to identify duplicates 
easily for filtering or search purposes. As long as that is simply 
obtained I can decide to either block duplicates in the history 
pre-insert or filter them later for display purposes. The purpose of the 
history is to analyze short-term changes - not find exceptions in 
long-term stability. So I'll probably wind up blocking duplicates 
pre-insert...


At first glance it seems I have a few options:
1. Experiment with Firebird array datatypes...but while I was able to 
create one in Flamerobin, I couldn't insert data. So I'm guessing this 
is still in the development/experimental area?


2. Implement my own array - use a CHAR or VARCHAR to store the info. 
Either using known lengths or delimited values.
2a. Next decision - flatten the entire "ROUTE" structure into a single 
long VARCHAR - or have a simulated array for each detail field.
2b. Computed fields - I could define a field using SUBSTRING for each 
individual detail...but I don't think I have any reason to operate with 
an individual route's detail field at the database level. I'll be 
passing "complete" route sets to/from the application - so this is 
probably a waste of time.


3. Create a group of singleton tables - Route_1, Route_2,..., Route_8 - 
with all the detail fields. A join would pull them together to build 
complete route. This would theoretically potentially maybe sort of 
reduce storage requirements - except now additional indexes and 
processing would be required. And real-world analysis tells me the 
majority of nodes will have more than 5 routes active - so this is 
probably a lot of effort for either little gain or actually a net loss 
in performance.


4. Probably the worst choice - have a table with individual fields for 
every route+detail, e.g., Route_1_Quality, Route_4_Linknode. I don't 
even like thinking about this one but now that I've typed it I'll leave 
it as an example of A Bad Thing.


5. Something brilliant from a member of this discussion group that I 
haven't thought of. I'm holding out for this one.


--
Daniel

Re[2]: [firebird-support] Find duplicate multi-row entries

['Daniel Miller' dmil...@amfes.com [firebird-support]]

Set,

Thank you. This definitely does something...to be honest I'm not certain 
exactly what... I'm definitely going to need to study this for a while. 
I sincerely appreciate the effort - though I think I am going to 
reconsider my table structure.


--
Daniel

On 9/28/2017 1:42:18 PM, "setysvar setys...@gmail.com 
[firebird-support]" <firebird-support@yahoogroups.com> wrote:





Sorry again, Daniel, I had to get to a place where I could test things 
before replying. My problem was that what I'd done before was to try to 
find identical sets, not trying to find unique sets, and that made me 
mess up the logic. I hope this query will get you what you want (and 
this time I've tested before replying):


with tmp(NODE, ROUTE_UPDATED) as
(select distinct r.NODE, rh.ROUTE_UPDATED
 from routes r
 left join route_history rh on r.NODE = rh.NODE),
new_routes(NODE) as
(select distinct t.NODE
 from tmp t
 where exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX

and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct 
from rh.QUALITY
and t.ROUTE_UPDATED = 
rh.ROUTE_UPDATED

  where t.NODE = r.NODE
and rh.NODE is null)
or exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX = rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct from 
rh.QUALITY

  where t.NODE = rh.NODE
and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

HTH,
Set

Den 28.09.2017 11:00, skrev 'Daniel Miller' dmil...@amfes.com 
[firebird-support]:

This appears no different than a simple:

select * from routes

So...not there yet.  I think what I'm fighting is the lack of pivot or 
crosstab functionality - and I may have no choice (though it offends 
me deeply) but to setup my archive table for this comparison.  Either 
via the hated structure of column names ROUTE1, ROUTE2, ROUTE8...or 
the only slightly less offensive VARCHAR concatenation.


A concatenated LIST seems like it would be a elegant solution - and 
while operating over the whole table is time-consuming a single node 
isn't too bad.  And if I can get the history filtered and old 
duplicate records deleted it would be fine.  But the inconsistency is 
killing me.


There MUST be a Firebird-friendly, normalized, compact storage 
structure that will work.  I'm just not seeing it.

--
Daniel

On 9/28/2017 1:19:18 AM, "Svein Erling Tysvær setys...@gmail.com 
[firebird-support]" <firebird-support@yahoogroups.com> wrote:


Sorry again, Daniel, fourth query required (I forgot that things may 
vary depending on time, this is something I've never done before):


with tmp(NODE, ROUTE_UPDATED) as
(select distinct rh.NODE, rh.ROUTE_UPDATED
 from route_history rh
 join routes r on rh.NODE = r.NODE), //*This join is only for 
performance reasons in case ROUTE_HISTORY contains a lot more records 
than ROUTES and may be removed*/

new_routes(NODE) as
(select distinct t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY is not 
distinct from rh.QUALITY
and t.ROUTE_UPDATED = 
rh.ROUTE_UPDATED

  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY is not 
distinct from rh.QUALITY

  where t.NODE = rh.NODE
and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

Hope I finally got it right,
Set

2017-09-28 10:01 GMT+02:00 Svein Erling Tysvær <setys...@gmail.com>:
Sorry, I forgot that should probably use 'IS NOT DISTINCT' and not 
'=' for QUALITY.


Note that this checks only checks if there has ever been anything 
identical in ROUTE_HISTORY, it doesn't restrain itself to the l

Re[2]: [firebird-support] LIST gives inconsistent results

['Daniel Miller' dmil...@amfes.com [firebird-support]]

But that doesn't make sense. Right from the docs you quoted - "If 
ordering is important,
the source data can be pre-sorted using a derived table or similar." So 
if I create the derived table using ORDER BY - why is the LIST not 
sorted?

But...if somehow the join is messing up the LIST processing I can work 
around that - generate the LIST first and then join after.  Here's the 
next issue:

Having generated a table with LIST results - why can I not sort or 
compare on the LISTS?  I first create a view:
create view TEMP_ROUTE_HISTORY_LISTED (NODE, ROUTE_UPDATED, 
LISTED_ROUTE)
as
select H.NODE, H.ROUTE_UPDATED, 
list(H.ROUTE_INDEX||H.LINK_NODE||H.QUALITY) LISTED_ROUTE
 from ROUTE_HISTORY H
 group by H.NODE, H.ROUTE_UPDATED;

This (seems) to work - I get exactly what I think I want now.  
Except...the sort order of the rows.  So...
select TRHL.LISTED_ROUTE
 from TEMP_ROUTE_HISTORY_LISTED TRHL
 where NODE=5003
 order by TRHL.LISTED_ROUTE

This does...exactly the same as the view. LISTED_ROUTE is obviously not 
sorted. What am I doing wrong now?
--
Daniel

On 9/28/2017 5:40:51 AM, "Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support]" <firebird-support@yahoogroups.com> wrote:

>On 2017-09-28 10:49, 'Daniel Miller' dmil...@amfes.com
>[firebird-support] wrote:
>>It's certainly possible my database is corrupted - I don't think it
>>is.  But I'm willing to test if someone tells me how.  However...
>>
>>At the moment, after several painful hours, I think I've determined
>>the following:
>>
>>CREATE TABLE NODES
>>(
>>   NODE smallint NOT NULL,
>>   ROUTE_UPDATED timestamp,
>>   CONSTRAINT PK_NODES PRIMARY KEY (NODE)
>>);
>>
>>CREATE TABLE ROUTES
>>(
>>   NODE smallint NOT NULL,
>>   ROUTE_INDEX smallint NOT NULL,
>>   LINK_NODE smallint NOT NULL,
>>   QUALITY smallint,
>>   CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
>>);
>>
>>Entries in table ROUTES are inserted in primary key order - as well as
>>sorted by primary key. So via both "raw & natural" order and an active
>>ORDER BY it shouldn't be that difficult to have a sorted list of
>>routes!
>
>If you assume that Firebird will somehow guarantee that rows are
>returned in insertion order (or that they are even stored in insertion
>order on disk), than your are mistaken. There is no such guarantee, the
>only guarantee is using an ORDER BY.
>
>>if I do:
>>select R.NODE, list(R.ROUTE_INDEX||R.LINK_NODE||R.QUALITY)
>>LISTED_ROUTE
>> from ROUTES R
>> group by R.NODE
>>
>>I get a computed column that is properly sorted. I don't even need to
>>specify an ORDER BY.  But...
>>
>>select N.NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
>> from NODES N
>> join (select R.NODE, R.ROUTE_INDEX, R.LINK_NODE, R.QUALITY from
>>ROUTES R order by R.NODE,R.ROUTE_INDEX) using (NODE)
>> group by N.NODE
>>
>>Even though I'm explicitly sorting the source derived table for the
>>LIST - I get a set of results that seems almost random. Absolutely
>>maddening.
>
>As documented in :
>https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-aggfuncs.html#fblangref25-functions-aggfuncs-list
>
>"**The ordering of the list values is undefined**—the order in which 
>the
>strings are concatenated is determined by read order from the source 
>set
>which, in tables, is not generally defined. If ordering is important,
>the source data can be pre-sorted using a derived table or similar."
>
>(emphasis mine)
>
>In your query your ORDER BY in the subquery of the join gets lost (or 
>is
>not guaranteed to be maintained) in the join, so you need to push that
>ORDER BY up out of the join:
>
>  select NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
>  from (
> select N.NODE, R.ROUTE, R.LINK_NODE, R.QUALITY
> from NODES N
> join ROUTES R using (NODE)
> order by N.NODE, R.ROUTE_INDEX
>  ) a
>  group by NODE
>
>However, this still depends on an implementation artefact and is not
>guaranteed to work, nor guaranteed to work in future versions.
>
>Mark
>
>
>
>Posted by: Mark Rotteveel <m...@lawinegevaar.nl>
>
>
>++
>
>Visit http://www.firebirdsql.org and click the Documentation item
>on the main (top) menu.  Try FAQ and other links from the left-side 
>menu there.
>
>Also search the knowledgebases at 
>http://www.ibphoenix.com/resources/documents/
>
>++
>
>
>Yahoo Groups Links
>
>
>

Re[6]: [firebird-support] Find duplicate multi-row entries

['Daniel Miller' dmil...@amfes.com [firebird-support]]

This appears no different than a simple:

select * from routes

So...not there yet.  I think what I'm fighting is the lack of pivot or 
crosstab functionality - and I may have no choice (though it offends me 
deeply) but to setup my archive table for this comparison.  Either via 
the hated structure of column names ROUTE1, ROUTE2, ROUTE8...or the only 
slightly less offensive VARCHAR concatenation.


A concatenated LIST seems like it would be a elegant solution - and 
while operating over the whole table is time-consuming a single node 
isn't too bad.  And if I can get the history filtered and old duplicate 
records deleted it would be fine.  But the inconsistency is killing me.


There MUST be a Firebird-friendly, normalized, compact storage structure 
that will work.  I'm just not seeing it.

--
Daniel

On 9/28/2017 1:19:18 AM, "Svein Erling Tysvær setys...@gmail.com 
[firebird-support]" <firebird-support@yahoogroups.com> wrote:





Sorry again, Daniel, fourth query required (I forgot that things may 
vary depending on time, this is something I've never done before):


with tmp(NODE, ROUTE_UPDATED) as
(select distinct rh.NODE, rh.ROUTE_UPDATED
 from route_history rh
 join routes r on rh.NODE = r.NODE), //*This join is only for 
performance reasons in case ROUTE_HISTORY contains a lot more records 
than ROUTES and may be removed*/

new_routes(NODE) as
(select distinct t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY is not 
distinct from rh.QUALITY
and t.ROUTE_UPDATED = 
rh.ROUTE_UPDATED

  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY is not 
distinct from rh.QUALITY

  where t.NODE = rh.NODE
and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

Hope I finally got it right,
Set

2017-09-28 10:01 GMT+02:00 Svein Erling Tysvær <setys...@gmail.com>:
Sorry, I forgot that should probably use 'IS NOT DISTINCT' and not '=' 
for QUALITY.


Note that this checks only checks if there has ever been anything 
identical in ROUTE_HISTORY, it doesn't restrain itself to the latest 
entry only (i.e. the query I've written is slightly similar to the 
second query I wrote, it has to be extended if you want something 
slightly similar to the first query). Also, my third query doesn't 
consider duplicate rows (since the fields seem to be the primary key 
in ROUTE, it isn't theoretically possible in your example).


Set

2017-09-28 9:52 GMT+02:00 Svein Erling Tysvær <setys...@gmail.com>:
OK, that's completely different, but I've done something similar once 
before. It is not possible to directly compare sets for equality, but 
it is possible to check that set A doesn't contain anything that 
isn't in set B and that set B doesn't contain anything that isn't in 
set A. I assume NODE is the one common denominator:


with tmp (NODE) as
(select distinct NODE from routes),
new_routes(NODE) as
(select t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY = 
rh.QUALITY

  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX = 
rh.ROUTE_INDEX
and r.LINK_NODE = 
rh.LINK_NODE
and r.QUALITY = 
rh.QUALITY

  where t.NODE = rh.NODE
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

Does this get you the result you want?
Set

2017-09-28 9:19 GMT+02:00 'Daniel Miller' dmil...@amfes.com 
[firebird-support] <firebird-support@yahoogroups.com 
<mailto:firebird-support@yahoogroups.com>>:



Thanks - but that still doesn't wo

[firebird-support] LIST gives inconsistent results

['Daniel Miller' dmil...@amfes.com [firebird-support]]

It's certainly possible my database is corrupted - I don't think it is.  
But I'm willing to test if someone tells me how.  However...


At the moment, after several painful hours, I think I've determined the 
following:


CREATE TABLE NODES
(
  NODE smallint NOT NULL,
  ROUTE_UPDATED timestamp,
  CONSTRAINT PK_NODES PRIMARY KEY (NODE)
);

CREATE TABLE ROUTES
(
  NODE smallint NOT NULL,
  ROUTE_INDEX smallint NOT NULL,
  LINK_NODE smallint NOT NULL,
  QUALITY smallint,
  CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
);

Entries in table ROUTES are inserted in primary key order - as well as 
sorted by primary key. So via both "raw & natural" order and an active 
ORDER BY it shouldn't be that difficult to have a sorted list of routes!


if I do:
select R.NODE, list(R.ROUTE_INDEX||R.LINK_NODE||R.QUALITY) LISTED_ROUTE
from ROUTES R
group by R.NODE

I get a computed column that is properly sorted. I don't even need to 
specify an ORDER BY.  But...


select N.NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
from NODES N
join (select R.NODE, R.ROUTE_INDEX, R.LINK_NODE, R.QUALITY from 
ROUTES R order by R.NODE,R.ROUTE_INDEX) using (NODE)

group by N.NODE

Even though I'm explicitly sorting the source derived table for the LIST 
- I get a set of results that seems almost random. Absolutely maddening.

--
Daniel

Re: [firebird-support] Multiple LIST columns

['Daniel Miller' dmil...@amfes.com [firebird-support]]

I found a far more elegant? way to accomplish this - concatenate the 
fields with a single LIST, e.g.


select n.NODE, h.ROUTE_UPDATED,
list( h.ROUTE_INDEX || h.LINK_NODE || h.QUALITY )
from NODES n
join ROUTE_HISTORY h on n.NODE=h.NODE
where n.NODE=5104
group by n.NODE, h.ROUTE_UPDATED
order by h.ROUTE_UPDATED desc

This ensures the "lists" are matching - but brings up another problem 
which I'll start another thread on.

--
Daniel

On 9/27/2017 10:26:31 PM, "'Daniel Miller' dmil...@amfes.com 
[firebird-support]" <firebird-support@yahoogroups.com> wrote:





With a statement such as:

select n.NODE, h.ROUTE_UPDATED,
list( h.ROUTE_INDEX ) LI, list( h.LINK_NODE ) LN, list( h.QUALITY ) 
LQ

from NODES n
join ROUTE_HISTORY h on n.NODE=h.NODE
where n.NODE=5104
group by n.NODE, h.ROUTE_UPDATED
order by h.ROUTE_UPDATED desc

Are the LISTS guaranteed to be in matching order? So each entry of each 
comma-delimited list is pulled from the same row as the corresponding 
entry in the other lists?


--
Daniel

Re[4]: [firebird-support] Find duplicate multi-row entries

['Daniel Miller' dmil...@amfes.com [firebird-support]]

Thanks - but that still doesn't work.  I get a huge return set with a 
ton of duplicates.  I think something's being lost in translation.  A 
simple match of one row isn't sufficient - I need to match the "set".


With the following six records:
5557111160
5557211150
5557351042
5557450732
5557552222
5557651102

That is the list of routes a given node has at a specific point in time. 
 So it's perfectly possible at another time to have fewer, or more, or 
different routes.  And I need to consider ALL the routes in play for a 
given timestamp to be a single "set".  So if today I have the above list 
of routes, and yesterday route index 4 was looking at a different node - 
that would constitute a different and unique set even though the other 5 
records match.


5557111160
5557211150
5557351042
555742
5557552222
5557651102

So it's quite possible I will have lots of duplicates for an individual 
row in ROUTES when compared in ROUTE_HISTORY - what I'm trying to filter 
is the pattern of rows.  Only if ALL the rows of a current entries in 
ROUTES exist in ROUTE_HISTORY should it be considered a duplicate 
condition.


--
Daniel

On 9/27/2017 11:53:48 PM, "Svein Erling Tysvær setys...@gmail.com 
[firebird-support]" <firebird-support@yahoogroups.com> wrote:





Sure it is possible to write such a query:

select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from routes r
left join route_history rh1 on r.node = rh1.node
  and r.route_index = rh1.route_index
  and r.link_node = rh1.link_node
  and r.quality is not distinct from 
rh1.quality

left join route_history rh2 on rh1.node = rh2.node
  and rh1.route_index = rh2.route_index
  and rh1.route_updated < rh2.route_updated
where rh1.node is null
  and rh2.node is null

I use two LEFT JOINS because I assume you want things to be returned if 
things are changed back, e.g. if you have


5557111160
5557111160

then you only want one row in route_history, but if you have

5557111160
5557111161
5557111160

then you don't want the last record to be considered a duplicate, but 
know when it changed back. If ROUTE_INDEX is just a running number, you 
consider


5557111160
5557211160

to be duplicates, and there are no gaps between the ROUTE_INDEX for 
each NODE, then the query can be simplified:


select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from routes r
left join route_history rh1 on r.node = rh1.node
  and r.route_index+1 = rh1.route_index
  and r.link_node = rh1.link_node
  and r.quality is not distinct from 
rh1.quality

where rh1.node is null

Note that I use IS NOT DISTINCT so that NULLs are considered equal. If 
you prefer, the left joins can be replaced by (nested) NOT EXISTS.


HTH,
Set

2017-09-28 7:22 GMT+02:00 'Daniel Miller' dmil...@amfes.com 
[firebird-support] <firebird-support@yahoogroups.com>:



On 9/27/2017 9:20:54 PM, "liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support]" <firebird-support@yahoogroups.com 
<mailto:firebird-support@yahoogroups.com>> wrote:


group by + having
is your friend.
E.g.
Select field1, field2, count(*)
From tablex
Group by field1, field2
Having count(*)>1

But in your scenario i do not know if this is the solution. Question 
is if record was changed and in next update "restored" and once again 
changed is this duplicate or no?

If yes above query is ok if no then procedure is your friend.

And for the future change your audit trigger and check before if 
there was any change


Thank you, but I this isn't quite what I need.  I need to match 
against all the records of a set (up to 8 rows per set) - not just 
individual rows.


--
Daniel

[firebird-support] Multiple LIST columns

['Daniel Miller' dmil...@amfes.com [firebird-support]]

With a statement such as:

select n.NODE, h.ROUTE_UPDATED,
list( h.ROUTE_INDEX ) LI, list( h.LINK_NODE ) LN, list( h.QUALITY ) 
LQ

from NODES n
join ROUTE_HISTORY h on n.NODE=h.NODE
where n.NODE=5104
group by n.NODE, h.ROUTE_UPDATED
order by h.ROUTE_UPDATED desc

Are the LISTS guaranteed to be in matching order? So each entry of each 
comma-delimited list is pulled from the same row as the corresponding 
entry in the other lists?


--
Daniel

Re[2]: [firebird-support] Find duplicate multi-row entries

['Daniel Miller' dmil...@amfes.com [firebird-support]]

On 9/27/2017 9:20:54 PM, "liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support]"  wrote:


group by + having
is your friend.
E.g.
Select field1, field2, count(*)
From tablex
Group by field1, field2
Having count(*)>1

But in your scenario i do not know if this is the solution. Question is 
if record was changed and in next update "restored" and once again 
changed is this duplicate or no?

If yes above query is ok if no then procedure is your friend.

And for the future change your audit trigger and check before if there 
was any change


Thank you, but I this isn't quite what I need.  I need to match against 
all the records of a set (up to 8 rows per set) - not just individual 
rows.


--
Daniel

[firebird-support] Find duplicate multi-row entries

['Daniel Miller' dmil...@amfes.com [firebird-support]]

I have a table like:

CREATE TABLE ROUTES
(
  NODE smallint NOT NULL,
  ROUTE_INDEX smallint NOT NULL,
  LINK_NODE smallint NOT NULL,
  QUALITY smallint,
  CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
);

In use, this table can have from 0-8 rows per NODE. Similar to:
5557111160
5557211150
5557351042
5557450732
5557552222
5557651102

On a regular basis this table gets updated. In the process, I copy the 
current values to an archive table via a BEFORE UPDATE trigger:

CREATE TABLE ROUTE_HISTORY
(
  NODE smallint NOT NULL,
  ROUTE_INDEX smallint NOT NULL,
  LINK_NODE smallint NOT NULL,
  QUALITY smallint,
  ROUTE_UPDATED timestamp NOT NULL,
  CONSTRAINT PK_ROUTE_HISTORY PRIMARY KEY 
(NODE,ROUTE_INDEX,ROUTE_UPDATED)

);

I created these tables to monitor some conditions - and now I'm starting 
to try to use these for analysis.  One of the conditions I'm seeing is - 
often during the update there's actually no change.  As a result, my 
history table has an awful lot of duplicate records.  I don't think I 
need these.  So...I need a filter to find duplicate conditions - 
whereupon I'll decide either to not insert them to begin with, or prune 
them from the database, or maybe I'll keep them and just not display the 
duplicates for certain reports.


So the question is how to determine the duplicates.  My initial reaction 
is a stored procedure, which would perform the necessary processing - 
probably using some recursive loops.  But...is there a way to express 
this via a select statement?

--
Daniel

[firebird-support] Firebird Alpha 4 Release Notes

[Daniel Miller dmil...@amfes.com [firebird-support]]

The misunderstanding is probably mine - but I've never heard of 34-bit 
values/precision before.  Is that a typo for either 32 or 64?  Or "34" 
is correct?

-- 
Daniel

[firebird-support] Transactions for read-only selects

['Daniel Miller' dmil...@amfes.com [firebird-support]]

When closing a read-only transaction for a read operation - does it 
matter if COMMIT vs ROLLBACK is used?


--
Daniel

[firebird-support] php transactions

['Daniel Miller' dmil...@amfes.com [firebird-support]]

I have an application I'm developing with PHP & Firebird.  While it 
mostly works fine - I've been having what I believe to be transaction 
issues.


One problem was visibility - committed changes were not visible to 
selects.  I know they were committed - after executing the commit within 
the scripts, I could see the changes within FlameRobin.  Yet the script 
didn't see them.


I also saw the "oldest active transaction" getting old...and it didn't 
seem to update until I killed some of the longer-running php processes.


I use explicit transactions for every insert or update followed by 
explicit commits.  Could my problem be that I need to use explicit 
transactions for the selects as well?


--
Daniel

[firebird-support] When do calculated columns calculate?

['Daniel Miller' dmil...@amfes.com [firebird-support]]

Seems a silly question, but:

Given two columns:
A SmallInt
B SmallInt

And it so happens that the data that will be stored will always be a 
four digit integer for "A" and a single digit integer for "B", so I can 
make calculated column "C":

(cast(A as char(5)) || cast(B as char(1)))

as a char(6).  Now - I don't need to index on this field because I index 
on the "raw" A & B (they're a composite primary/foreign key).  However, 
there are times when I want to compare primary keys to determine if they 
are the same or different records.  Writing statements like:


where T1.A=T2.A and T1.B=T2.B

can certainly be done...but I like the simplicity of:
where T1.C = T2.C

or more importantly for some of my queries:
where T1.C <> T2.C

Which is a roundabout way of getting to my question but I figured I'd 
give the background - since this typically results in someone pointing 
out I'm missing something simple that will eliminate my question 
altogether!  But assuming this application is still valid...I only need 
this calculated value (in two tables) for a specific query for limiting 
purposes.  All other insert, update, and select operations will never 
reference this calculated value.  If my select and/or insert statements 
don't reference the calculated column - does the server still need to 
calculate it for each row?  Or only when explicitly referenced in the 
select statement?


I'd rather have a slight performance impact for a single query than 
something application-wide - and I'd prefer not to store a duplicated 
value.  Though if the calculation happens regardless I'll probably 
switch to an insert/update trigger to store the value and then use that 
for primary/foreign keys as needed.

--
Daniel

Re: was [firebird-support] Delete visibility - now bonehead insert/delete with generators

['Daniel Miller' dmil...@amfes.com [firebird-support]]

Still haven't answered my visibility question - but figured out the 
brilliant bug I implemented which caused me to question things.  I have 
a primary key defined as a Smallint and an associated generator.  You've 
probably already know what's coming...


This table has a high amount of inserts/deletes.  Which rapidly 
increments the generator...which results in overflow errors.  So...note 
to self:  Either use a generator-based primary key sized to accommodate 
the traffic, or don't use a generator - use a composite PK which is 
sufficiently unique.  Adding a dedicated unique field for PK's may be 
elegant...but has issues for high traffic.  Which I would have figured 
out if I'd thought it through...

--
Daniel

[firebird-support] Delete visibility

['Daniel Miller' dmil...@amfes.com [firebird-support]]

Just had a problem come up in a database that was previously working - 
at least I thought it was.  I'm using PHP as the client.


Table Nodes has ID.
Table Routes has foreign key NODE_ID.
Table Elevations has foreign key ROUTE_ID.

The foreign keys have cascade update & delete.

As part of an update process, I first delete all routes & elevations for 
a given node.  Theoretically, I could rely on the cascade delete, but 
(using a stored procedure) for a given Node I identify the associated 
routes, then explicitly delete all elevations for the routes, then 
delete the routes.  And for convenience, the proc returns the node ID.


I call this procedure with an explicit read/write transaction and then 
commit the transaction. Then I proceed with inserting the new values.


Just ran into an issue where Firebird via PHP was reporting foreign key 
errors.  I tried a few debug lines, then examined my logic.  In my PHP 
client, I start a transaction, perform the deletions, then do the 
insert/updates, then commit.  The theory being a full all-or-nothing 
update.  But since that now wasn't working...thought I'd try explicitly 
committing the delete first, then performing the updates.


Same issue.  Now I'm really confused.  So...break out FlameRobin.  I 
perform the steps manually...

select NID from NODES - gives me an one.
select RID from ROUTES where NODE_ID = NID - gives me routes.
select EID from ELEVATIONS where ROUTE_ID = RID gives me a 
lot.


Ok...everything's there.  Now execute:
select NID from purge_routes(1234)

Now commit in that window.  Then execute the route & elevation queries 
again, and I shouldn't see anything...but I do!  Close them and run 
again...now they're clear.  I haven't had this happen before - either 
I'm doing something stupid, or have I got some kind of corruption?

--
Daniel

[firebird-support] Users for application

['Daniel Miller' dmil...@amfes.com [firebird-support]]

I don't know if this specific question has been asked - but searches 
haven't given me a close enough answer so hopefully this is a little 
new.


Separate from security theories and considerations of "good practice", 
what, if any, benefits accrue from using multiple users when accessing a 
Firebird database?


If that's too general a question, here's my specific need.  I have a 
database that has at least three simultaneous connections via PHP.  I 
have two long-running PHP daemons (yes I know PHP isn't intended for 
such but it works and allows me to keep a consistent code base).  These 
listen for external events and update the database accordingly.  Part of 
their updates do involve reads as well.  Then the main application is 
PHP via Nginx/php-fpm.


I cache the database connection via the http session for the application 
- the daemons just keep the connection open as an application global.


At this time, everything is just using the SYSDBA user.  I'm not 
certain, but I think from time to time there is a visibility issue.  
I'll update information via the web application, and confirm it in both 
the web application and FlameRobin - but my daemons don't seem to see 
the changes.  Stopping/starting the daemons appears to correct the 
problem.  As I say - this is totally unconfirmed and I'd need more 
testing to narrow this down.


Again - separate from security considerations - are there any 
reasons/benefits to creating and using individual users for each of 
these connections?  Security-wise, I'm the only user and the server 
isn't internet exposed so I'm not terribly worried at the moment.

--
Daniel

Re[2]: [firebird-support] select parent based on multiple child conditions

['Daniel Miller' dmil...@amfes.com [firebird-support]]

With correction for one or two typos - those both worked, thank you!  
Had to read, and read, and read - to try to understand HOW these work.  
Which was exactly what I was hoping for - to better understand usage of 
Firebird SQL.

Looking at the query plans, it appears Alternative 2 - which uses the 
two exists(select) constructs instead of two joins appears to be 
slightly more efficient - at least the query plan appears slightly 
smaller.  So I'll file Alternate 1 for future testing against a larger 
dataset - and use Alternate 2 for now.

I'm assuming there would be no benefit, and would probably be worse, to 
add indexes for ROUTE_INDEX, or NETCON, or QUALITY?  Because they 
represent a small range of possible values that is duplicated for each 
node?

--
Daniel

-- Original Message --
From: "setysvar setys...@gmail.com [firebird-support]" 

To: firebird-support@yahoogroups.com
Sent: 12/19/2015 2:14:23 AM
Subject: Re: [firebird-support] select parent based on multiple child 
conditions

>Here's two alternatives for you, they should give the same result
>(though provide different options for modifications, see the comments
>inside the SQL), pick the one you prefer. Both differ from your SQL in
>that rows are also returned when there are no routes, whereas your SQL
>would require at least one route to get a result.
>
>Alternative 1)
>select distinct n.NODE
>from NODES n
>left join ROUTES r  on n.ID = r.NODE_ID --remove LEFT from this touple
>if you don't want to return NODES with no route.
>left join ROUTES r2 on n.ID = r2.NODE_ID and r.ID <> r2.ID
>where ((r.ROUTE_INDEX in (1, 2)
> and  (r.NETCON>5 or r.QUALITY>3))
> or r2.ID is null
>
>Alternative 2)
>select distinct NODE --remove distinct if you want one row for each
>record in the NODES table, even when they have the same value for NODE.
>from NODES n
>where exists(select * from routes r
>   where n.ID = r.NODE_ID
>  and r.ROUTE_INDEX in (1, 2)
>  and r.NETCON>5 or r.QUALITY>3)
> or not exists(select * from routes r
>   join routes r2 on r.NODE_ID = r2.NODE_ID
> and r.ID < r2.ID
>   where n.ID = r.NODE_ID)
>
>If you prefer alternative 2 and want only touples with exactly one
>route, the NOT EXISTS can be changed to:
> or exists(select * from routes r
>   left join routes r2 on r.NODE_ID = r2.NODE_ID
> and r.ID < r2.ID
>   where n.ID = r.NODE_ID
> and r2.ID is null)
>
>HTH,
>Set
>
>
>
>Posted by: setysvar 
>
>
>++
>
>Visit http://www.firebirdsql.org and click the Documentation item
>on the main (top) menu.  Try FAQ and other links from the left-side 
>menu there.
>
>Also search the knowledgebases at 
>http://www.ibphoenix.com/resources/documents/
>
>++
>
>
>Yahoo Groups Links
>
>
>

Re: [firebird-support] select with constant value in where-clause

['Daniel Miller' dmil...@amfes.com [firebird-support]]

As an amateur - I'd suggest using an indexed field in the where clause, 
and choosing an invalid value that is less than the 1st real entry.


--
Daniel


-- Original Message --
From: "josef.gschwendt...@quattro-soft.de [firebird-support]" 


To: firebird-support@yahoogroups.com
Sent: 12/19/2015 9:46:02 AM
Subject: [firebird-support] select with constant value in where-clause




Hi,



the following select fetches all records of the table (FB 2.5.4) and 
obviously brings no resultset.


select * from Mytable where 1=0


Is there a trick to force Firebird not to scan all records?

We sometimes use such a statement (in for select loops) to get 
different datasets and process it in the loop.

select ... from Table1 where :InputParam = 'A'
union
select ... from Table2 where :InputParam = 'B'



Regards,

Josef

[firebird-support] select parent based on multiple child conditions

['Daniel Miller' dmil...@amfes.com [firebird-support]]

I'm not sure how to construct this.  There's probably an elegant 
solution available - I'm trying to feel my way there but...


Two tables.

CREATE TABLE NODES
(
  ID Smallint NOT NULL,
  NODE Smallint NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE (NODE)
);

CREATE TABLE ROUTES
(
  ID Integer NOT NULL,
  NODE_ID Smallint NOT NULL,
  ROUTE_INDEX Smallint NOT NULL,
  QUALITY Smallint NOT NULL,
  PRIMARY KEY (ID)
);
ALTER TABLE ROUTES ADD CONSTRAINT FK_ROUTE_NODE_ID
  FOREIGN KEY (NODE_ID) REFERENCES NODES (ID) ON UPDATE CASCADE ON 
DELETE CASCADE;
Table "Nodes" is simply a unique list, and table "Routes" has multiple 
children.  If it makes a difference for performance considerations, 
"Nodes" can potentially grow to 10,000 entries, and up to 8 Routes per 
Node.  Route_Index will be sequentially numbered for entries of each 
Node_ID.


I want to obtain a result set of Nodes that represent problems.  "Good" 
Nodes have at least two routes, and the first two routes must have 
Quality>7.  So "Bad" routes are any of the following:


1.  Less than 2 Routes per node.
2.  Route_Index=1 has Quality<7
3.  Route_Index=1 has Quality<7

Took me a bit to feel my way through, but my first attempt appears to be 
working:


select distinct n.NODE, count(r.NODE_ID) from NODES n
join ROUTES r on n.ID=r.NODE_ID
where (r.ROUTE_INDEX=1 and (r.QUALITY<7)) or (r.ROUTE_INDEX=2 and 
(r.QUALITY<7))

group by n.NODE
union
SELECT n.NODE, COUNT(r.NODE_ID) FROM NODES n
join ROUTES r on n.ID=r.NODE_ID
GROUP BY n.NODE
HAVING COUNT(r.NODE_ID)<2

This appears to give me a valid result set for my purposes.  The 
(r.QUALITY>7) constructs are in parentheses because I've simplified the 
tables and there are additional tests being done to determine validity 
within the parens.


So my question is - is there a "better" way of accomplishing this?
--
Daniel

Re: [firebird-support] select parent based on multiple child conditions

['Daniel Miller' dmil...@amfes.com [firebird-support]]

Took me a bit to feel my way through, but my first attempt appears to 
be working:


select distinct n.NODE, count(r.NODE_ID) from NODES n
join ROUTES r on n.ID=r.NODE_ID
where (r.ROUTE_INDEX=1 and (r.QUALITY<7)) or (r.ROUTE_INDEX=2 and 
(r.QUALITY<7))

group by n.NODE
union
SELECT n.NODE, COUNT(r.NODE_ID) FROM NODES n
join ROUTES r on n.ID=r.NODE_ID
GROUP BY n.NODE
HAVING COUNT(r.NODE_ID)<2
My next question would be how to achieve the above - without returning 
the count(r.NODE_ID).  Reason - I'm building a view using this statement 
and it offends my sense of elegance to have the count column in the 
view.  A version I've come up with is using a derived table - but again 
it strikes me that there's probably a better way to do this:


select node from
(select distinct n.NODE, count(r.NODE_ID) from NODES n
join ROUTES r on n.ID=r.NODE_ID
where (r.ROUTE_INDEX=1 and (r.NETCON>5 or r.QUALITY>3)) or 
(r.ROUTE_INDEX=2 and (r.NETCON>5 or r.QUALITY>3))

group by n.NODE
union
SELECT n.NODE, COUNT(r.NODE_ID) FROM NODES n
join ROUTES r on n.ID=r.NODE_ID
GROUP BY n.NODE
HAVING COUNT(r.NODE_ID)<2) bad_routes (node, routecount)

--
Daniel