[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 Citrix-machine. Is there a way to get the IP-Adress of the client-machine. Is there a difference/improvement using FB 3? Thank you for your help. Josef
[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 I don't find any name or alias that works within this plan. Does anybody know how to do this? Regards, Josef
[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". with recursive cte as (select id, id_parent, green, SortText from MyTable where Green = 'Yes' union all select T2.id, T2.id_parent, T2.green, T2.SortText from MyTable T2 join cte on T2.ID = cte.id_parent), cte2 as (select t3.id, cast(rpad(t3.SortText,10) as varchar(200)) SortOrder from MyTable t3 where t3.id_parent is null union all select T2.id, cte2.SortOrder || rpad(T2.SortText,10) from MyTable T2 join cte2 on cte2.ID = t2.id_parent) select distinct cte.id, cte.id_parent, cte.green, cte.SortText, cte2.SortOrder from cte join cte2 on cte.id = cte2.id order by cte2.SortOrder Regards, Josef
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", but I can't find an example which works for me. Regards, Josef
[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 2 1 Yes B1 3 2 No C6 4 2 Yes C5 5 2 Yes C4 6 1 No B2 7 6 No C4 8 6 Yes C3 9 No A2 10 9 No B3 11 10 No C2 12 No A1 13 12 Yes B4 14 13 No C1 INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (1, NULL, 'No', 'A3'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (2, 1, 'Yes', 'B1'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (3, 2, 'No', 'C7'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (4, 2, 'Yes', 'C6'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (5, 2, 'Yes', 'C5'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (6, 1, 'No', 'B2'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (7, 6, 'No', 'C4'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (8, 6, 'Yes', 'C3'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (9, NULL, 'No', 'A2'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (10, 9, 'No', 'B3'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (11, 10, 'No', 'C2'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (12, NULL, 'No', 'A1'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (13, 12, 'Yes', 'B4'); INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (14, 13, 'No', 'C1'); Table-Data shown as Tree A3 B1 C7 C6 C5 B2 C4 C3 A2 B3 C2 A1 B4 C1 Now I need a dataset which suspends the green tree-nodes with the complete tree-path for each green cell. This dataset should be ordered alphabetically (A1 before A3 and C5 before C6) A1 B4 A3 B1 C5 C6 B2 C3 I (almost) get the result I want with this statement: with recursive cte as (select id, id_parent, green, SortText from MyTable where Green = 'Yes' union all select T2.id, T2.id_parent, T2.green, T2.SortText from MyTable T2 join cte on T2.ID = cte.id_parent) select distinct id, id_parent, green, SortText from cte The dataset is ok, but the ordering is not (because there is no “Depth First by” – Clause) With FB3 I tried to use windows functions (something like below): Rank() over(Partition by id_parent order by SortText) But I could not solve my problem! - For instance, I got the message: Recursive member of CTE cannot use aggregate or window function. Could anybody give me a hint how to solve this. Thank you, Josef
[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 with some example-code to do this. Thank you very much, Josef
[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 are needed (minimum)? Thank you for your help, Josef
[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] 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, Josef
[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 Now I have a dataset which suspends only the green tree-nodes. I need a SQL which suspends the complete tree-path for each green cell 1 1.1 1.1.2 1.2 1.2.2 3 3.1 How can this be done??? TIA, Sepp
[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 Thank you very much for your help. Josef Gschwendtner
[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 Key1 Value2 1 1 400 2 1 400 3 2 2000 4 3 1000 5 3 1000 6 3 500 Result Key1 Key2 Value 1 1 400 1 2 400 1 null200 2 3 2000 3 4 1000 3 5 1000 3 6 500 3 null500 4 null 4000 Note: In Tab2 are 0 to n related records for each record in Tab1. In return dataset should be one record with key2 is null if value1 sum(value2) for one relation.
[firebird-support] Re: Looking for a special select-statement
Hi, this works great, thank you. Josef
