[firebird-support] Get client-IP-Adress

Hi, with our client software installed on a terminal server we like to get the IP-Adress of the "real" client-computer connected to the database (the one the user sits in front of). With Mon$Remote_Address from Mon$Attachments (using FB 2.5.8) we get the IP-Adress of the

[firebird-support] get number of combinations of to keys

Hi, Is there a way to get this ProFaCount in one statement (without for select) ProFaCount = 0; for select distinct PRO, FA from Tab where Key = :Key into :PRO, :FA do ProFaCount = ProFaCount + 1; Thank you for your help. Regards, Josef

[firebird-support] Re: Plan with stored procedure

Hi, I forgot to write that "SC_Costs" is a stored procedure. Josef

[firebird-support] Plan with stored procedure

Hi, for a certain select-statement IB-Expert shows the plan below: PLAN JOIN (PRO INDEX (RDB$FOREIGN218), SORT (SORT (SC_COSTS NATURAL))) If I apend this plan to the statement I get the error below: There is no alias or table named SC_COSTS at this scope level. My problem is, that

[firebird-support] Re: Sorting-Problem on recursive query (window functions)

Hi Set, The performance is ok in my tests. Without "cast(rpad(t3.SortText,10) as varchar(200))" I get trouble on "deeper" trees. Thanks again for your help. Still it would be interesting for me wether this can be solved using window functions. Regards, Josef

Re: [firebird-support] Sorting-Problem on recursive query (window functions)

Hi Set, this is a very interesting solution. I changed it a bit to create a "SortOrder" that should always work. What do you think? I will test it on a real (big) table and see how the performance is. Maybe " cast(... as varchar(200))" is not necessary if I don't select "cte2.SortOrder".

Re: [firebird-support] Sorting-Problem on recursive query (window functions)

Hi, thank you for your ideas. But I think your solution would only work if the anchor query would select treemembers of level 1. Whereas my query starts with "where Green = 'Yes'" and these elements are in different levels. I have read that this should be possible with "window functions",

[firebird-support] Sorting-Problem on recursive query (window functions)

Hello, I have a table which data is representing a tree: CREATE TABLE MyTABLE ( ID INTEGER NOT NULL, ID_PARENT INTEGER, GREEN VARCHAR(3), SortText VARCHAR(5), CONSTRAINT PK_MYTABLE PRIMARY KEY (ID)); This is the data in this table: ID PARENT_ID GREEN SortText 1 No A3

[firebird-support] hirarchical data with window functions

Hi, I have a table with hierarchical data. Fields: PK PK_Parent Name I would need a statement to fill a tree with the data of this table. The sub nodes of each node should be sorted alphabetical. I think one can define such a statement with windows functions (FB3). Could anybody provide me

[firebird-support] Re: Minimal file file requirements for FB3 (client/embedded)

Hi, what is with msvcp100.dll and msvcr100.dll? Are these files not required? -- Josef

[firebird-support] Minimal file file requirements for FB3 (client/embedded)

Hi, could please anybody tell us which files we definitely have to ship with our app as client for a FB3 remote server or as an FB3 embedded server? (minimal files required). Standard Client: - fbclient.dll - are there other files needed? Embedded Server: - fbclient.dll - which other files

[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

[firebird-support] Sort hierarchical data

Hi, I have a table with hierarchical data. There is a numeric sortfield where the user sets the sort-order of the children of a particular parent (values from 1 to n für each parent). How can I write a recursive query which selects the whole tree and honors these sortnumbers. TIA,

[firebird-support] Recursive SQL-Question

I have a table in the database which represents a tree like the one below. Each tree-node is a record in the table and has a primary-key. 1 1.1 1.1.1 1.1.2 1.2 1.2.1 1.2.2 2 2.2 2.2.1 3 3.1 3.1.1

[firebird-support] Get ID of record with minsort

Hi, I have a table with one ID-Column and one Sort-Column. I need the ID from the record with the lowest sort-number. Sortnumbers are not unique (can be doubled). Can I do this with one SQL-Statement? Thank you for your help. Regards, Josef

RE: [firebird-support] Get ID of record with minsort

Hi Set, thank you very much. Row 1 is the solution... It would have been so easy. Sepp

[firebird-support] How to join records

Hi, we have 2 tables For each record in Table1 there are 2 records in Table2. Table1 (T1) == 1 2 Table2 (T2) T2T1 == 11 21 32 42 What is the cheapest way to get a dataset like below? T1T2aT2b === 1 12 2 34

[firebird-support] Looking for a special select-statement

Hi, we would need the below shown result-dataset from data shown in Tab1 and Tab2. Can this be expressed with a select-statement? Thank you for your help, Josef Gschwendtner Tab1 Key1 Value1 1 1000 2 2000 3 3000 4 4000 Tab2 Key2

[firebird-support] Re: Looking for a special select-statement

Hi, this works great, thank you. Josef