[firebird-support] Very Strange: Query giving different results

2015-10-05 Thread 'Alexis (PROINTER)' ale...@prointersl.com [firebird-support] 
Hi, we have found a very strange problem with our firebird database, a
record on a table of firebird databes is not always beeing returned:


 


1 - Using flamerobin we are never fetching record 1648 of our datble.


 






 


2 - Using an application we are developing (with vb.net on vs2010) the
record is fetched:


 






 


3 - If we query from the application with condition IDESTADO = 3 wich the
desired record has that value that record is also not fetched:


 






 






 


4 - This is the design of our table:


 






 


 


If anyone can give me a clue about why is this happening we will truly
appreciate.


 


Thanks,


 


Alexis.


 






[Non-text portions of this message have been removed]

[firebird-support] Support for OS X 10.11 El Capitan

2015-10-05 Thread Bernd Frei - ASA bernd.f...@asaon.com [firebird-support] 


Hello Paul,


do you have an idea, when the packager will be supported El Capitan or is any 
other way manually to install firebird?


Thanks for you help
Bernd Frei

Re: Re: [firebird-support] How To Get Entire Linked Group Details

2015-10-05 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
Hi Norbert,
Sorry to say, but the below SQL you shared doesn't givbe the sub nodes in the 
base node, it just gives the first level of nodes and not the enitre node 
structure i.e. all other nodes below the nodes (i.e. nodes which I get from you 
SQL as a result ) are missing.
Would you please check again ?

With Best Regards.
Vishal


 On Monday, 5 October 2015 6:53 PM, "'Norbert Saint Georges' 
n...@tetrasys.eu [firebird-support]"  wrote:
   

     Recursive Trees with Firebird 
?http://www.firebirdsql.org/file/community/ppts/fbcon11/FBTrees2011.pdf       
Norbert Saint Georges     TetraSys Oy   Bergantie 69, FI-02540 Kylmälä   Tel. : 
+358 (0) 400 27 25 18   E- mail : n...@tetrasys.fi  De : 
firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : lundi 5 octobre 2015 16:06
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details    Hi 
Norbert,  Thank You So Much, I got exactly the same result what I was 
expecting.  Thank You So much.  Are you aware of any documents which are 
avaialble in english, so I could learn this recirsive SQL ?    With Best 
Regards.  Vishal    On Monday, 5 October 2015 3:55 PM, "'Norbert Saint Georges' 
n...@tetrasys.eu [firebird-support]"  wrote:  
  if I understand the request, something like select b.description from(select 
a.* from mygroup awhere  a.linked_to_group is not nulland(exists(select 1 from 
mygroup b  where  b.linked_to_group = a.pk_group )or a.linked_to_group != 
(select first 1 pk_group from mygroup where linked_to_group is null))) bwhere 
b.linked_to_group = (select first 1 pk_group from mygroup where description = 
'Vishal Group') Norbert Saint Georges    TetraSys Oy   Bergantie 69, 
FI-02540 Kylmälä   Tel. : +358 (0) 400 27 25 18   E- mail : n...@tetrasys.fi De 
: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : lundi 5 octobre 2015 11:15
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details   Hi 
Norbert, The SQL you have shared without CTE, gives me other group details as 
well if they exists. If you create another base group like 'Vishal Group', then 
this SQL gives that group details as well. I tried but putting group name in 
condition but getting only one record, could you show me how to get only 
'Vishal Group' records, so that would also a good learning for me.  Thanks In 
Advance. With Best Regards. Vishal  On Friday, 2 October 2015 6:57 PM, 
"'Norbert Saint Georges' n...@tetrasys.eu [firebird-support]" 
 wrote:   Without CTE select a.description 
from mygroup awhere a.linked_to_group is not nulland(exists(select 1 from 
mygroup b  where  b.linked_to_group = a.pk_group )ora.linked_to_group != 
(select first 1 pk_group from mygroup where linked_to_group is null)) 
Norbert Saint Georges    TetraSys Oy   Bergantie 69, FI-02540 Kylmälä   Tel. : 
+358 (0) 400 27 25 18   E- mail : n...@tetrasys.fi De : 
firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : vendredi 2 octobre 2015 15:24
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details    Hi 
Karol Bieniaszewski, You are passing "Vishal Group1" in condition, I need to 
pass "Vishal Group" (i.e. base leaf for perticular group), because in the sql 
you provided if I put 'Test1', it gives entire structure details, one way that 
is correct, but only i need is to pass required base group as in the condition 
and not any sub-leaf in condition, could you please modify it ? Thanks Again In 
Advance. With Best Regards. Vishal  On Friday, 2 October 2015 5:23 PM, "Vishal 
Tiwari vishuals...@yahoo.co.in [firebird-support]" 
 wrote:   Hi Karol Bieniaszewski,  
Yess, YOU ARE THE MAN OF THE MOMENT.  Your SQL just ROCKS, JUST 
ROCKS. AWESOME KAROL, JUST AWESOME.  I am going to next level of my 
code where I need to find out that the deepest leaf, if it is not used in 
certain table then I would like to delete it. For time being one more questions 
(Please expect more in upcoming time on this issue :) ), is it possible to get 
the records for one entire leaf and it dependent leafs and then another leaf 
and its dependent and so on via your SQL ?  Result order like: Vishal Group 
Vishal Group1Vishal Group1.1Vishal Group1.1.1Vishal Group2Vishal Group2.1Vishal 
Group2.1.1Vishal Group3Vishal Group4Vishal Group4.1  With Best Regards. Vishal  
 On Friday, 2 October 2015 4:59 PM, "liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support]"  wrote:   Hi, i do not 
know if you describe your problem precisely.But i understand it like this:looks 
like you need all leaf from same parent (with parent included) as is for 
"Vishal Group1" and all its childs try this  WITH

Re: Re: [firebird-support] How To Get Entire Linked Group Details

2015-10-05 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
Sorry, forgot to put SQL for reference.
 if I understand the request, something like select b.description from(select 
a.* from mygroup awhere  a.linked_to_group is not nulland(exists(select 1 from 
mygroup b  where  b.linked_to_group = a.pk_group )or a.linked_to_group != 
(select first 1 pk_group from mygroup where linked_to_group is null))) bwhere 
b.linked_to_group = (select first 1 pk_group from mygroup where description = 
'Vishal Group') 


 On Tuesday, 6 October 2015 9:56 AM, "Vishal Tiwari vishuals...@yahoo.co.in 
[firebird-support]"  wrote:
   

     Hi Norbert,
Sorry to say, but the below SQL you shared doesn't givbe the sub nodes in the 
base node, it just gives the first level of nodes and not the enitre node 
structure i.e. all other nodes below the nodes (i.e. nodes which I get from you 
SQL as a result ) are missing.
Would you please check again ?

With Best Regards.
Vishal


 On Monday, 5 October 2015 6:53 PM, "'Norbert Saint Georges' 
n...@tetrasys.eu [firebird-support]"  wrote:
   

     Recursive Trees with Firebird 
?http://www.firebirdsql.org/file/community/ppts/fbcon11/FBTrees2011.pdf       
Norbert Saint Georges     TetraSys Oy   Bergantie 69, FI-02540 Kylmälä   Tel. : 
+358 (0) 400 27 25 18   E- mail : n...@tetrasys.fi  De : 
firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : lundi 5 octobre 2015 16:06
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details    Hi 
Norbert,  Thank You So Much, I got exactly the same result what I was 
expecting.  Thank You So much.  Are you aware of any documents which are 
avaialble in english, so I could learn this recirsive SQL ?    With Best 
Regards.  Vishal    On Monday, 5 October 2015 3:55 PM, "'Norbert Saint Georges' 
n...@tetrasys.eu [firebird-support]"  wrote:  
  if I understand the request, something like select b.description from(select 
a.* from mygroup awhere  a.linked_to_group is not nulland(exists(select 1 from 
mygroup b  where  b.linked_to_group = a.pk_group )or a.linked_to_group != 
(select first 1 pk_group from mygroup where linked_to_group is null))) bwhere 
b.linked_to_group = (select first 1 pk_group from mygroup where description = 
'Vishal Group') Norbert Saint Georges    TetraSys Oy   Bergantie 69, 
FI-02540 Kylmälä   Tel. : +358 (0) 400 27 25 18   E- mail : n...@tetrasys.fi De 
: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : lundi 5 octobre 2015 11:15
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details   Hi 
Norbert, The SQL you have shared without CTE, gives me other group details as 
well if they exists. If you create another base group like 'Vishal Group', then 
this SQL gives that group details as well. I tried but putting group name in 
condition but getting only one record, could you show me how to get only 
'Vishal Group' records, so that would also a good learning for me.  Thanks In 
Advance. With Best Regards. Vishal  On Friday, 2 October 2015 6:57 PM, 
"'Norbert Saint Georges' n...@tetrasys.eu [firebird-support]" 
 wrote:   Without CTE select a.description 
from mygroup awhere a.linked_to_group is not nulland(exists(select 1 from 
mygroup b  where  b.linked_to_group = a.pk_group )ora.linked_to_group != 
(select first 1 pk_group from mygroup where linked_to_group is null)) 
Norbert Saint Georges    TetraSys Oy   Bergantie 69, FI-02540 Kylmälä   Tel. : 
+358 (0) 400 27 25 18   E- mail : n...@tetrasys.fi De : 
firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : vendredi 2 octobre 2015 15:24
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details    Hi 
Karol Bieniaszewski, You are passing "Vishal Group1" in condition, I need to 
pass "Vishal Group" (i.e. base leaf for perticular group), because in the sql 
you provided if I put 'Test1', it gives entire structure details, one way that 
is correct, but only i need is to pass required base group as in the condition 
and not any sub-leaf in condition, could you please modify it ? Thanks Again In 
Advance. With Best Regards. Vishal  On Friday, 2 October 2015 5:23 PM, "Vishal 
Tiwari vishuals...@yahoo.co.in [firebird-support]" 
 wrote:   Hi Karol Bieniaszewski,  
Yess, YOU ARE THE MAN OF THE MOMENT.  Your SQL just ROCKS, JUST 
ROCKS. AWESOME KAROL, JUST AWESOME.  I am going to next level of my 
code where I need to find out that the deepest leaf, if it is not used in 
certain table then I would like to delete it. For time being one more questions 
(Please expect more in upcoming time on this issue :) ), is it possible to get 
the records for one entire leaf and it dependent leafs and then another leaf

Re: Re: [firebird-support] How To Get Entire Linked Group Details

2015-10-05 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
Hi Norbert,
The SQL you have shared without CTE, gives me other group details as well if 
they exists. If you create another base group like 'Vishal Group', then this 
SQL gives that group details as well. I tried but putting group name in 
condition but getting only one record, could you show me how to get only 
'Vishal Group' records, so that would also a good learning for me.

Thanks In Advance.
With Best Regards.
Vishal 


 On Friday, 2 October 2015 6:57 PM, "'Norbert Saint Georges' 
n...@tetrasys.eu [firebird-support]"  wrote:
   

     Without CTE  select a.description from mygroup awhere a.linked_to_group is 
not nulland(exists(select 1 from mygroup b  where  b.linked_to_group = 
a.pk_group )ora.linked_to_group != (select first 1 pk_group from mygroup where 
linked_to_group is null))       Norbert Saint Georges     TetraSys Oy   
Bergantie 69, FI-02540 Kylmälä   Tel. : +358 (0) 400 27 25 18   E- mail : 
n...@tetrasys.fi  De : firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Envoyé : vendredi 2 octobre 2015 15:24
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details     
Hi Karol Bieniaszewski,  You are passing "Vishal Group1" in condition, I need 
to pass "Vishal Group" (i.e. base leaf for perticular group), because in the 
sql you provided if I put 'Test1', it gives entire structure details, one way 
that is correct, but only i need is to pass required base group as in the 
condition and not any sub-leaf in condition, could you please modify it ?  
Thanks Again In Advance.  With Best Regards.  Vishal    On Friday, 2 October 
2015 5:23 PM, "Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]" 
 wrote:    Hi Karol Bieniaszewski,    
Yess, YOU ARE THE MAN OF THE MOMENT.   Your SQL just ROCKS, JUST 
ROCKS. AWESOME KAROL, JUST AWESOME.    I am going to next level of my 
code where I need to find out that the deepest leaf, if it is not used in 
certain table then I would like to delete it.  For time being one more 
questions (Please expect more in upcoming time on this issue :) ), is it 
possible to get the records for one entire leaf and it dependent leafs and then 
another leaf and its dependent and so on via your SQL ?    Result order like:  
Vishal Group Vishal Group1Vishal Group1.1Vishal Group1.1.1Vishal Group2Vishal 
Group2.1Vishal Group2.1.1Vishal Group3Vishal Group4Vishal Group4.1    With Best 
Regards.  Vishal      On Friday, 2 October 2015 4:59 PM, "liviuslivius 
liviusliv...@poczta.onet.pl [firebird-support]" 
 wrote:    Hi, i do not know if you describe 
your problem precisely.But i understand it like this:looks like you need all 
leaf from same parent (with parent included) as is for "Vishal Group1" and all 
its childs try this  WITH RECURSIVE G1_PARENT AS ( SELECT MGP.DESCRIPTION FROM 
MYGROUP MG INNER JOIN MYGROUP MGP ON MGP.PK_GROUP=MG.LINKED_TO_GROUP WHERE 
MG.DESCRIPTION='Vishal Group1' ),  R_TREE AS  (  SELECT TT.PK_GROUP AS A, 
CAST(CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN GP.DESCRIPTION ELSE '' END AS 
VARCHAR(255)) AS PARENT   FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1 WHERE 
TT.LINKED_TO_GROUP IS NULL   UNION ALL        SELECT TT.PK_GROUP AS A, CASE 
WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN GP.DESCRIPTION ELSE RT.PARENT END AS 
PARENT FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1 JOIN R_TREE RT ON RT.A = 
TT.LINKED_TO_GROUP )  SELECT  *FROM  R_TREE RT2  INNER JOIN G1_PARENT GP ON 
RT2.PARENT=GP.DESCRIPTION  INNER JOIN MYGROUP TT2 ON TT2.PK_GROUP=RT2.A  with 
this query i got the same result as you showed in table below  regards,Karol 
Bieniaszewski  W dniu 2015-10-02 06:36:16 użytkownik Vishal Tiwari 
vishuals...@yahoo.co.in [firebird-support]  
napisał:
  Hi All, There is some change in my previous SQL. Please consider below SQLs. 
CREATE TABLE MYGROUP (  PK_GROUP GUID DEFAULT 'newid()' NOT NULL,  DESCRIPTION 
Varchar(255),  LINKED_TO_GROUP GUID,  PRIMARY KEY (PK_GROUP)); COMMIT; INSERT 
INTO MYGROU P (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES 
('{-111---} ', 'My Items', NULL);  INSERT INTO 
MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES 
('{CD1E33D1-1666-49B9-83BE-067687E4DDD6}', 'Vishal Group', 
'{-111---}'); INSERT INTO MYGROUP (PK_GROUP, 
DESCRIPTION, LINKED_TO_GROUP) VALUES ('{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}', 
'Vishal Group1', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');IN SERT INTO MYGROUP 
(PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES 
('{A87E921D-0468-497D-92C5-19AB63751EE8}', 'Vishal Group1.1', 
'{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}');INSERT INTO MYGROUP (PK_GROUP, 
DESCRIPTION, LINKED_TO_GROUP) VALUES ('{0FDC729A-8FCC-4D23-8619-436A459835DD}', 
'Vishal Group1.1.1', '{A87E921D-0468-497D-92C5-19AB63751EE8}'); INSERT INTO 
MYGROUP (PK_GROUP,

Re: [firebird-support] Very Strange: Query giving different results

2015-10-05 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support] 
On Mon, Oct 5, 2015 at 12:59 PM, 'Alexis (PROINTER)' ale...@prointersl.com
[firebird-support]  wrote:

> Hi, we have found a very strange problem with our firebird database, a
> record on a table of firebird databes is not always beeing returned:
>
> 1 - Using flamerobin we are never fetching record 1648 of our datble.
>
> 2 - Using an application we are developing (with vb.net on vs2010) the
> record is fetched:
>
> 3 - If we query from the application with condition IDESTADO = 3 wich the
> desired record has that value that record is also not fetched:
>
>
Yahoo ate your table structure, so this is just a guess.  You may have an
index that's missing an entry - that shouldn't happen, of course.  Using
isql, try fetching the record by primary key value, primary key
concatenated with an empty string (if it's a string) or plus zero (if it's
a number).  They try the same thing on any secondary indexes.

Good luck,

Ann

[firebird-support] DATA COMPARISON

2015-10-05 Thread 'Stef' s...@autotech.co.za [firebird-support] 
Hi All

 

Is there a query structure available I can use to identify where records
have been change or are missing between two table with the exact table
structure?

 

i.e.

 

I store a backup of financial data annually in separate tables.  

JOURNAL2013

JOURNAL2015

JOURNAL2015 etc.

where the main data resides in a JOURNAL table

 

I need to see only where data has been CHANGED or is MISSING from to JOURNAL
table as compared to data stored in the JOURNAL2014 table.

 

Regards

 

Stef

 



[Non-text portions of this message have been removed]

RE: Re: [firebird-support] How To Get Entire Linked Group Details

2015-10-05 Thread 'Norbert Saint Georges' n...@tetrasys.eu [firebird-support] 
if I understand the request, something like

 

select b.description from

(

select a.* from mygroup a

where  a.linked_to_group is not null

and

(

exists(select 1 from mygroup b  where  b.linked_to_group = a.pk_group )

or a.linked_to_group != (select first 1 pk_group from mygroup where 
linked_to_group is null)

)

) b

where b.linked_to_group = (select first 1 pk_group from mygroup where 
description = 'Vishal Group')

 

 

   Norbert Saint Georges

 

   TetraSys Oy

   Bergantie 69, FI-02540 Kylmälä

   Tel. : +358 (0) 400 27 25 18

   E- mail : n...@tetrasys.fi  

 

De : firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : lundi 5 octobre 2015 11:15
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details

 

  

Hi Norbert,

 

The SQL you have shared without CTE, gives me other group details as well if 
they exists. If you create another base group like 'Vishal Group', then this 
SQL gives that group details as well. I tried but putting group name in 
condition but getting only one record, could you show me how to get only 
'Vishal Group' records, so that would also a good learning for me.

 

 

Thanks In Advance.

 

With Best Regards.

 

Vishal

 

 

On Friday, 2 October 2015 6:57 PM, "'Norbert Saint Georges' n...@tetrasys.eu 
[firebird-support]"  wrote:

 

  

Without CTE

 

select a.description from mygroup a

where a.linked_to_group is not null

and

(

exists(select 1 from mygroup b  where  b.linked_to_group = a.pk_group )

or

a.linked_to_group != (select first 1 pk_group from mygroup where 
linked_to_group is null)

)

 

 

   Norbert Saint Georges

 

   TetraSys Oy

   Bergantie 69, FI-02540 Kylmälä

   Tel. : +358 (0) 400 27 25 18

   E- mail : n...@tetrasys.fi  

 

De : firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : vendredi 2 octobre 2015 15:24
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details

 

  

 

Hi Karol Bieniaszewski,

 

You are passing "Vishal Group1" in condition, I need to pass "Vishal Group" 
(i.e. base leaf for perticular group), because in the sql you provided if I put 
'Test1', it gives entire structure details, one way that is correct, but only i 
need is to pass required base group as in the condition and not any sub-leaf in 
condition, could you please modify it ?

 

Thanks Again In Advance.

 

With Best Regards.

 

Vishal

 

 

On Friday, 2 October 2015 5:23 PM, "Vishal Tiwari vishuals...@yahoo.co.in 
[firebird-support]"  wrote:

 

  

Hi Karol Bieniaszewski,

 

 

Yess, YOU ARE THE MAN OF THE MOMENT. 

 

Your SQL just ROCKS, JUST ROCKS. AWESOME KAROL, JUST AWESOME.

 

 

I am going to next level of my code where I need to find out that the deepest 
leaf, if it is not used in certain table then I would like to delete it.

 

For time being one more questions (Please expect more in upcoming time on this 
issue :) ), is it possible to get the records for one entire leaf and it 
dependent leafs and then another leaf and its dependent and so on via your SQL ?

 

 

Result order like:

 

Vishal Group 

Vishal Group1

Vishal Group1.1

Vishal Group1.1.1

Vishal Group2

Vishal Group2.1

Vishal Group2.1.1

Vishal Group3

Vishal Group4

Vishal Group4.1

 

 

With Best Regards.

 

Vishal

 

 

 

On Friday, 2 October 2015 4:59 PM, "liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support]"  wrote:

 

  

Hi,

 

i do not know if you describe your problem precisely.

But i understand it like this:

looks like you need all leaf from same parent (with parent included) as is for 
"Vishal Group1" and all its childs

 

try this

 

 WITH RECURSIVE

 G1_PARENT AS

 (

 SELECT MGP.DESCRIPTION FROM MYGROUP MG INNER JOIN MYGROUP MGP ON 
MGP.PK_GROUP=MG.LINKED_TO_GROUP WHERE MG.DESCRIPTION='Vishal Group1'

 ),

  R_TREE AS 

 ( 

 SELECT TT.PK_GROUP AS A, CAST(CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN 
GP.DESCRIPTION ELSE '' END AS VARCHAR(255)) AS PARENT  

 FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1

 WHERE TT.LINKED_TO_GROUP IS NULL 

 

 UNION ALL 

  

 SELECT TT.PK_GROUP AS A, CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN 
GP.DESCRIPTION ELSE RT.PARENT END AS PARENT

 FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1

 JOIN R_TREE RT ON RT.A = TT.LINKED_TO_GROUP

 ) 

 SELECT 

 * 

  

 FROM 

 R_TREE RT2 

 INNER JOIN G1_PARENT GP ON RT2.PARENT=GP.DESCRIPTION 

 INNER JOIN MYGROUP TT2 ON TT2.PK_GROUP=RT2.A 

 

with this query i got the same result as you showed in table below 

 

regards,

Karol Bieniaszewski

 

 

W dniu 2015-10-02 06:36:16 użytkownik Vishal Tiwari vishuals...@yahoo.co.in 
[firebird-support]  napisał:

Re: Re: [firebird-support] How To Get Entire Linked Group Details

2015-10-05 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
Hi Norbert,
Thank You So Much, I got exactly the same result what I was expecting.
Thank You So much.
Are you aware of any documents which are avaialble in english, so I could learn 
this recirsive SQL ?

With Best Regards.
Vishal 


 On Monday, 5 October 2015 3:55 PM, "'Norbert Saint Georges' 
n...@tetrasys.eu [firebird-support]"  wrote:
   

     if I understand the request, something like  select b.description 
from(select a.* from mygroup awhere  a.linked_to_group is not 
nulland(exists(select 1 from mygroup b  where  b.linked_to_group = a.pk_group 
)or a.linked_to_group != (select first 1 pk_group from mygroup where 
linked_to_group is null))) bwhere b.linked_to_group = (select first 1 pk_group 
from mygroup where description = 'Vishal Group')       Norbert Saint Georges    
 TetraSys Oy   Bergantie 69, FI-02540 Kylmälä   Tel. : +358 (0) 400 27 25 18   
E- mail : n...@tetrasys.fi  De : firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Envoyé : lundi 5 octobre 2015 11:15
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details    Hi 
Norbert,  The SQL you have shared without CTE, gives me other group details as 
well if they exists. If you create another base group like 'Vishal Group', then 
this SQL gives that group details as well. I tried but putting group name in 
condition but getting only one record, could you show me how to get only 
'Vishal Group' records, so that would also a good learning for me.    Thanks In 
Advance.  With Best Regards.  Vishal    On Friday, 2 October 2015 6:57 PM, 
"'Norbert Saint Georges' n...@tetrasys.eu [firebird-support]" 
 wrote:    Without CTE select a.description 
from mygroup awhere a.linked_to_group is not nulland(exists(select 1 from 
mygroup b  where  b.linked_to_group = a.pk_group )ora.linked_to_group != 
(select first 1 pk_group from mygroup where linked_to_group is null)) 
Norbert Saint Georges    TetraSys Oy   Bergantie 69, FI-02540 Kylmälä   Tel. : 
+358 (0) 400 27 25 18   E- mail : n...@tetrasys.fi De : 
firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : vendredi 2 octobre 2015 15:24
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details    Hi 
Karol Bieniaszewski, You are passing "Vishal Group1" in condition, I need to 
pass "Vishal Group" (i.e. base leaf for perticular group), because in the sql 
you provided if I put 'Test1', it gives entire structure details, one way that 
is correct, but only i need is to pass required base group as in the condition 
and not any sub-leaf in condition, could you please modify it ? Thanks Again In 
Advance. With Best Regards. Vishal  On Friday, 2 October 2015 5:23 PM, "Vishal 
Tiwari vishuals...@yahoo.co.in [firebird-support]" 
 wrote:   Hi Karol Bieniaszewski,  
Yess, YOU ARE THE MAN OF THE MOMENT.  Your SQL just ROCKS, JUST 
ROCKS. AWESOME KAROL, JUST AWESOME.  I am going to next level of my 
code where I need to find out that the deepest leaf, if it is not used in 
certain table then I would like to delete it. For time being one more questions 
(Please expect more in upcoming time on this issue :) ), is it possible to get 
the records for one entire leaf and it dependent leafs and then another leaf 
and its dependent and so on via your SQL ?  Result order like: Vishal Group 
Vishal Group1Vishal Group1.1Vishal Group1.1.1Vishal Group2Vishal Group2.1Vishal 
Group2.1.1Vishal Group3Vishal Group4Vishal Group4.1  With Best Regards. Vishal  
 On Friday, 2 October 2015 4:59 PM, "liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support]"  wrote:   Hi, i do not 
know if you describe your problem precisely.But i understand it like this:looks 
like you need all leaf from same parent (with parent included) as is for 
"Vishal Group1" and all its childs try this  WITH RECURSIVE G1_PARENT AS ( 
SELECT MGP.DESCRIPTION FROM MYGROUP MG INNER JOIN MYGROUP MGP ON 
MGP.PK_GROUP=MG.LINKED_TO_GROUP WHERE MG.DESCRIPTION='Vishal Group1' ),  R_TREE 
AS  (  SELECT TT.PK_GROUP AS A, CAST(CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION 
THEN GP.DESCRIPTION ELSE '' END AS VARCHAR(255)) AS PARENT   FROM MYGROUP TT 
LEFT JOIN G1_PARENT GP ON 1=1 WHERE TT.LINKED_TO_GROUP IS NULL   UNION ALL      
  SELECT TT.PK_GROUP AS A, CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN 
GP.DESCRIPTION ELSE RT.PARENT END AS PARENT FROM MYGROUP TT LEFT JOIN G1_PARENT 
GP ON 1=1 JOIN R_TREE RT ON RT.A = TT.LINKED_TO_GROUP )  SELECT  *FROM  
R_TREE RT2  INNER JOIN G1_PARENT GP ON RT2.PARENT=GP.DESCRIPTION  INNER JOIN 
MYGROUP TT2 ON TT2.PK_GROUP=RT2.A  with this query i got the same result as you 
showed in table below  regards,Karol Bieniaszewski  W dniu 2015-10-02 06:36:16 
użytkownik Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]