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

[josef.gschwendt...@quattro-soft.de [firebird-support]]

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

RE: [firebird-support] Firebird 3.0 grant to VIEW with GTT not working

[Brian Dunstan br...@dunstan.biz [firebird-support]]

Hi Doug,

Good question!

The idea is that I’ll get the set of PROVIDERs
Where the PROVIDER’s logon_user_name is either null or matches the 
current_organisation. Current_organisation is a single row GTT that identifies 
the organisational entity that work is being done for. It’s populated (with one 
row) by an ON CONNECT trigger according to the organisation a user has been 
linked to.

The idea is that a provider who has a logon name will be restricted (by the 
software) to certain organisations.
A provider who has not been restricted by logon name will be able to logon to 
any organisation.

Providers are people who provide services to customers. Some people can be 
represented by any user (p.logon_user_name is null) whilst others can only be 
represented by a single user.

Since I am just doing a migration I can’t change the logic, no matter how 
bizarre 

Brian

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Tuesday, 7 November 2017 3:25 AM
To: firebird-support 
Subject: Re: [firebird-support] Firebird 3.0 grant to VIEW with GTT not working


Brian-

Sorry, I do not have any insights on this problem. I am curious, however, what 
you intend with that join:

join current_organisation co on exists(select 1
  from 
user_organisation uo
  where uo.ib_username 
= p.logon_user_name
  and 
uo.parameters_organisation_no = co.parameters_organisation_no)

Why code it this way? What are you trying to achieve? As you indicate, looks 
bizarre.

Thanks!

Doug C.

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

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

what about this?
with recursive cte as
  (select M.id, M.id_parent, M.green, CAST(M.SortText AS VARCHAR(100)) AS 
SortText
 from MyTable M
where M.Green = 'Yes'
union all
   select T2.id, T2.id_parent, T2.green, T2.SortText || '->' || C.SortText
 from MyTable T2
  join cte C on T2.ID = C.id_parent)
select distinct id, id_parent, green, SortText
  from cte
  order by SortText

regards,
Karol Bieniaszewski

From: josef.gschwendt...@quattro-soft.de [firebird-support] 
Sent: Monday, November 6, 2017 12:32 PM
To: firebird-support@yahoogroups.com 
Subject: [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

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

What about something like:

with recursive cte as

  (select id, id_parent, green, SortText, SortText SortColumn

 from MyTable

where Green = 'Yes'

union all

   select T2.id, T2.id_parent, T2.green, T2.SortText, cte.SortColumn ||
t2.SortText

 from MyTable T2

  join cte on T2.ID  = cte.id_parent)



select id, id_parent, green, SortText

  from cte

  group by 1, 2, 3, 4

order by min(SortColumn)

Don't know whether or not it works, I use recursive CTEs very rarely and
haven't tried much sorting of them.

HTH,

Set

2017-11-06 12:32 GMT+01:00 josef.gschwendt...@quattro-soft.de
[firebird-support] :

>
>
> 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] Sorting-Problem on recursive query (window functions)

[josef.gschwendt...@quattro-soft.de [firebird-support]]

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] Firebird 3.0 grant to VIEW with GTT not working

[Brian Dunstan br...@dunstan.biz [firebird-support]]

Hi,

I am working on migrating a DB and application from Firebird 2.5 (dialect 1) to 
Firebird 3.0 dialect 3. The database is now dialect 3 and running a Firebird 
3.0 default installation on a Windows 10 server.

Mostly it has gone quite smoothly, but I’ve encountered this problem, which 
seems a little strange. I’ve found a workaround, but I thought I’d post here in 
the hope I can understand what I’m missing, or perhaps document a bug.

I have a somewhat bizarre view:
CREATE VIEW PROVIDER_ORG_VIEW(
PROVIDER_NO, …)
AS
select p.* from provider P
join current_organisation co on exists(select 1
  from 
user_organisation uo
  where uo.ib_username 
= p.logon_user_name
  and 
uo.parameters_organisation_no = co.parameters_organisation_no)
 or p.logon_user_name is 
null

grants are:
GRANT SELECT ON CURRENT_ORGANISATION TO PUBLIC; -- CURRENT_ORGANISATION is a 
global temporary table
GRANT SELECT ON PROVIDER_ORG_VIEW TO PUBLIC;
GRANT SELECT ON USER_ORGANISATION TO VIEW PROVIDER_ORG_VIEW;

When a non-administrative user attempts to select from PROVIDER_ORG_VIEW, it 
gets an exception:
“This user does not have privilege to perform this operation on this object.
No permission for SELECT access to TABLE USER_ORGANISATION.”

If I change the grant on user_organisation to:
GRANT SELECT ON USER_ORGANISATION TO public;
Then the select on the view works OK.

Also, if I remove the EXISTS and join all the same tables in the view, then 
there is no exception, but the results are different of course, so it doesn’t 
really work.

Any help or insight will be greatly appreciated 

Thanks,

Brian