[firebird-support] CTE

2016-10-12 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello everybody,

 

actually I have a stored procedure where I get the needet material for each
product, some of them I get many times. Now I would like to create A CTE and
gets the amount of each material.

 

In my case I call a stored procedure and those calls itself.

 

Here the example:

 

  for select a.teilenr, sum(a.menge) from tklpos a join tkl b on a.jahr =
b.jahr and a.nr = b.nr

  join trechnungen c on b.jahr = c.jahr and b.nr = c.nr join tteile d on
a.teilenr = d.teilenr

  where extract(year from c.datum) = coalesce(:jahr,extract(year from
c.datum))

  and extract(month from c.datum) = coalesce(:monat,extract(month from
c.datum)) and a.teilenr = coalesce(:t, a.teilenr)

  and d.umsatztyp = coalesce(:umsatztyp,d.umsatztyp) and c.fa =
coalesce(:fa, c.fa) and b.kundennr = coalesce(:kunde,b.kundennr) and
c.gutschrift = 0

  group by a.teilenr

  into :teil, :menge do

  begin

if(menge is null) then menge = 0;

for select materialnr, pteilenr, urspteilenr, anzahlm, lieferant from
P_GETTEILEMATPS_2014(:teil, :menge, -1) into

:matnr, :pteilenr, :urspteilenr, :anz, :lieferant do suspend;

  End

 

How can I can I build a CTE outside?

 

Thank you

 

Best regards

 

Olaf

[firebird-support] CTE

2014-02-26 Thread checkmail 
Hello,

 

I get an error message during executing the following statement:

 

with mat as (

select  a.teilenr, c.vkpreis, a.matteilenr, c.bezeichnung from tmaterial a
left join tteile c on a.teilenr = c.teilenr

where a.kundennr = 24823 )

update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr =
mat.teilenr

 

I know, I can do the following (update XX where (select.) 

 

Why complain firebird the update..?

 

Thank you,

 

Best regards

 

Olaf

[firebird-support] CTE Question

2017-04-28 Thread 'Olaf Kluge' olaf.kl...@satron.de [firebird-support] 
Hello,

in advance, many thanks!

I would like to combine all properties like this:

Property1  Property2 Property3
Property4..
IP54 Yellow   With lamp 30W
IP20 Blue  Without lamp40W
Red
50W
 
60W

In the first step I would like get this:
IP54-Yellow-With lamp-30W
IP54-Yellow-With lamp-40W
.
IP54-Yellow-Without lamp-30W.

(in the next step, I would like to set Properties which are not possible,
for example 60W is not possible if the Color is red)

For this, I have two tables:

Table A
ID Property (color could be value 1, lamp, IPXX, Watt..) integer
POS (in this sequence I would like to generatet he result) integer
ID itself, Integer, self generated.

Table B
ID itself auto generated integer
ID_Table A references to Table A
Property - (yellow, red..)

For example, 
Table A
ID Property  POSID itself
1 (Color)1 10
2(lamp) 2 11
3(Watt)3 12


Table B
ID itself ID_Table_A Property
101  10 RED
102 10 BLUE
103 10 White
104 11  With Lamp
105 11  Without Lamp
105 12 30 Watt
105 12 40 Watt
105 12 50 Watt

Now I would get:

Red-With Lamp-30 Watt
Red-With Lamp-40 Watt
Red-With Lamp-50 Watt
Red-Without Lamp-30 Watt
Red-Without Lamp-40 Watt
Red-Without Lamp-50 Watt
Blue-With Lamp-30 Watt.. and so on

How can I realize with firebird?

Thank you.

Best regards

Olaf

[firebird-support] CTE Problem

2012-11-06 Thread Frank 

Hi,

I do not understand why

set planonly;
with recursive
  CTE_CNT1 as (select 0 as I
from RDB$DATABASE
union all select I + 1
from CTE_CNT1
where I < 9),

  CTE_CNT2 as (select I, 0 as J
from CTE_CNT1
union all select J * 10 + c1.I, c2.J + 1
from CTE_CNT1 c1
join CTE_CNT2 c2 on c2.J < 10)

select I from CTE_CNT2;

fails with:
Statement failed, SQLSTATE = 42S22
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-C2.J
-At line 14, column 15

while the similar

with recursive
  CTE_CNT1 as (select 0 as I
from RDB$DATABASE
union all select I + 1
from CTE_CNT1
where I < 9),

  CTE_CNT2 as (select 0 as I, 0 as J
from RDB$DATABASE
union all select J * 10 + c1.I, c2.J + 1
from CTE_CNT1 c1
join CTE_CNT2 c2 on c2.J < 10)

select I from CTE_CNT2;

succeds with

PLAN (CTE_CNT2 RDB$DATABASE NATURAL, CTE_CNT2 CTE_CNT1 RDB$DATABASE
NATURAL, JOIN ())

Frank

-- 
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
  their tears have healing powers and they make highly faithful pets."
  - J.K. Rowling

Re: [firebird-support] CTE

2014-02-26 Thread Tim Ward 
Yeah, I wanted to do this the other day (but found a different way to 
structure the query so never got round to posting the question here).


The Firebird book contains what I found to be incomplete and 
contradictory information on this point.


On 26/02/2014 10:43, checkmail wrote:


Hello,

I get an error message during executing the following statement:

with mat as (

select a.teilenr, c.vkpreis, a.matteilenr, c.bezeichnung from 
tmaterial a left join tteile c on a.teilenr = c.teilenr


where a.kundennr = 24823 )

update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr = 
mat.teilenr


I know, I can do the following (update XX where (select...)

Why complain firebird the update..?

Thank you,

Best regards

Olaf





--
Tim Ward

Re: [firebird-support] CTE

2014-02-26 Thread Mark Rotteveel 
On Wed, 26 Feb 2014 11:43:24 +0100, "checkmail" 
wrote:
> Hello,
> 
> I get an error message during executing the following statement:
> 
> with mat as (
> 
> select  a.teilenr, c.vkpreis, a.matteilenr, c.bezeichnung from tmaterial
a
> left join tteile c on a.teilenr = c.teilenr
> 
> where a.kundennr = 24823 )
> 
> update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr =
> mat.teilenr
> 
> I know, I can do the following (update XX where (select.) 
> 
> Why complain firebird the update..?

If you post a question that you get an error, then please include the
actual error in your question. That is 1) easier for people answering your
question and 2) allows for search engines like google to help other people
find your question in the future.

It is (with FlameRobin):
SQL Message : -104
Invalid token

Engine Code: 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 5, column 1
UPDATE

The problem is that the Firebird syntax only allows the use of WITH with a
SELECT; it is not supported with other query types (contrary to some other
database systems). But even if it were allowed, your current syntax would
be invalid. 

Your current query is akin to having a view called mat and then trying:

update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr =
mat.teilenr

That fails as you never actually select from mat.

Mark

RE: [firebird-support] CTE

2014-02-26 Thread Svein Erling Tysvær 
>Hello,
>
>I get an error message during executing the following statement:
>
>with mat as (
>select  a.teilenr, c.vkpreis, a.matteilenr, c.bezeichnung from tmaterial a 
>left join tteile c on a.teilenr = c.teilenr
>where a.kundennr = 24823 )
>update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr = mat.teilenr
>
>I know, I can do the following (update XX where (select.) 
>
>Why complain firebird the update..?

Very simple, CTEs are defined as part of the SELECT statement 
(www.firebirdsql.org/refdocs/langrefupd25-update.html), not the UPDATE 
statement (www.firebirdsql.org/refdocs/langrefupd25-update.html). Though, I do 
notice there's no syntax definition for SELECT the same way as there are for 
UPDATE even though the examples are helpful.

I would say another way to solve your case, is to use something like:

execute block as
  declare variable teilenr integer;
  declare variable vkpreis integer;
begin
  for select a.teilenr, c.vkpreis from tmaterial a left join tteile c on 
a.teilenr = c.teilenr where a.kundennr = 24823) 
  into :teilenr, :vkpreis do
  begin
update tteile set minvk = :vkpreis * 0.90 where teilenr = :teilenr;
  end
end

At first sight your update statement may seem like a good way to write things. 
However, you're not allowed to use tables the way you use them, i.e. you 
couldn't write

update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr = mat.teilenr

if mat had been a table.

Rather, you would have to write

update tteile set tteile.minvk = (select vkpreis * 0.90 from mat where 
tteile.tteilenr = mat.teilenr) 
where exists(select * from mat where tteile.teilenr = mat.teilenr)

and that is not all too different from what I think you can do today:

update tteile set tteile.minvk = (with mat as (select c.vkpreis, from tmaterial 
a 
   join tteile c on a.teilenr = 
c.teilenr
   where a.kundennr = 24823)
  select mat.vkpreis * 0.90)
where exists(with mat as (select a.teilenr from tmaterial where kundennr = 
24823)
 select * from mat where teilenr = mat.teilenr)

Though, of course, I wouldn't mind if some kind of update shortcut was 
available, e.g.

with mat as (select a.teilenr, c.vkpreis, a.matteilenr, c.bezeichnung 
 from   tmaterial a 
 left join tteile c on a.teilenr = c.teilenr
 where a.kundennr = 24823)
update tteile 
join mat on tteile.teilenr = mat.teilenr
set tteile.minvk = mat.vkpreis * 0.90

(of course only updating the table before the join and requiring either 
singular joins or using aggregate results - e.g. sum(mat.vkpreis))

Note that update...join is just what comes to my mind now, I almost exclusively 
use Firebird and am almost certain there are better ways of implementing 
something similar (as well as good reasons for not implementing it).

Set

AW: [firebird-support] CTE

2014-02-26 Thread checkmail 
Hello @ll,

 

thanks a lot! So many solutions J

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Im Auftrag von Svein Erling Tysvær
Gesendet: Mittwoch, 26. Februar 2014 13:00
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] CTE

 

  

>Hello,
>
>I get an error message during executing the following statement:
>
>with mat as (
>select  a.teilenr, c.vkpreis, a.matteilenr, c.bezeichnung from tmaterial a
left join tteile c on a.teilenr = c.teilenr
>where a.kundennr = 24823 )
>update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr =
mat.teilenr
>
>I know, I can do the following (update XX where (select.) 
>
>Why complain firebird the update..?

Very simple, CTEs are defined as part of the SELECT statement (
<http://www.firebirdsql.org/refdocs/langrefupd25-update.html>
www.firebirdsql.org/refdocs/langrefupd25-update.html), not the UPDATE
statement ( <http://www.firebirdsql.org/refdocs/langrefupd25-update.html>
www.firebirdsql.org/refdocs/langrefupd25-update.html). Though, I do notice
there's no syntax definition for SELECT the same way as there are for UPDATE
even though the examples are helpful.

I would say another way to solve your case, is to use something like:

execute block as
declare variable teilenr integer;
declare variable vkpreis integer;
begin
for select a.teilenr, c.vkpreis from tmaterial a left join tteile c on
a.teilenr = c.teilenr where a.kundennr = 24823) 
into :teilenr, :vkpreis do
begin
update tteile set minvk = :vkpreis * 0.90 where teilenr = :teilenr;
end
end

At first sight your update statement may seem like a good way to write
things. However, you're not allowed to use tables the way you use them, i.e.
you couldn't write

update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr =
mat.teilenr

if mat had been a table.

Rather, you would have to write

update tteile set tteile.minvk = (select vkpreis * 0.90 from mat where
tteile.tteilenr = mat.teilenr) 
where exists(select * from mat where tteile.teilenr = mat.teilenr)

and that is not all too different from what I think you can do today:

update tteile set tteile.minvk = (with mat as (select c.vkpreis, from
tmaterial a 
join tteile c on a.teilenr = c.teilenr
where a.kundennr = 24823)
select mat.vkpreis * 0.90)
where exists(with mat as (select a.teilenr from tmaterial where kundennr =
24823)
select * from mat where teilenr = mat.teilenr)

Though, of course, I wouldn't mind if some kind of update shortcut was
available, e.g.

with mat as (select a.teilenr, c.vkpreis, a.matteilenr, c.bezeichnung 
from tmaterial a 
left join tteile c on a.teilenr = c.teilenr
where a.kundennr = 24823)
update tteile 
join mat on tteile.teilenr = mat.teilenr
set tteile.minvk = mat.vkpreis * 0.90

(of course only updating the table before the join and requiring either
singular joins or using aggregate results - e.g. sum(mat.vkpreis))

Note that update...join is just what comes to my mind now, I almost
exclusively use Firebird and am almost certain there are better ways of
implementing something similar (as well as good reasons for not implementing
it).

Set

Re: [firebird-support] CTE Question

2017-04-28 Thread setysvar setys...@gmail.com [firebird-support] 
Hi Olaf!

First, SQL doesn't like unknown columns, you need to know at least the 
maximum possible number of properties to support. Having said that, you 
could try something like:

select B1.Property, B2.Property, B3.Property, B4.Property
from TableA A1
join TableB B1 on A1.ID = B1.ID_TableA
left join TableA A2 on A2.Pos = 2
left join TableB B2 on A2.ID = B2.ID_TableA
left join TableA A3 on A3.Pos = 3
left join TableB B3 on A3.ID = B3.ID_TableA
left join TableA A4 on A4.Pos = 4
left join TableB B4 on A4.ID = B4.ID_TableA
left join TableC C on C.ID_TableB_1 in (A1.ID, A2.ID, A3.ID, A4.ID)
and (C.ID_TableB_2 in (A1.ID, A2.ID, A3.ID, A4.ID) 
or C.ID_TableB_2 is null)
and (C.ID_TableB_3 in (A1.ID, A2.ID, A3.ID, A4.ID) 
or C.ID_TableB_3 is null)
and (C.ID_TableB_4 in (A1.ID, A2.ID, A3.ID, A4.ID) 
or C.ID_TableB_4 is null)
where A1.Pos = 1
   and C.ID is null

Since the left joins to TableA doesn't refer to other tables, I assume 
the left joins between TableA and TableB to be the same as cross joins.

TableC is for your next step, I'm assuming TableC could be defined like:
ID, ID_TableB_1, ID_TableB_2, ID_TableB_3, ID_TableB_4

and that not red, 60W could be written like:

1, 101, 109, , 

(assuming 101 to be red and 109 to be 60W)

It is of course thinkable that TableC also could have rows rather than 
columns for properties like TableB, but that would make the query more 
complex.

HTH,
Set






++

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/

AW: [firebird-support] CTE Question

2017-05-01 Thread 'Olaf Kluge' olaf.kl...@satron.de [firebird-support] 
.
Geschäftsführer: Bernd Grötzschel, Gerd Kaden
Amtsgericht: Chemnitz HRB1218
Ust-ID-Nr: DE141294791



Diese E-Mail ist vertraulich. Wenn Sie nicht der beabsichtigte Empfänger
sind, dürfen Sie die Informationen nicht offen legen oder benutzen. Wenn Sie
diese E-Mail durch einen Fehler bekommen haben, teilen Sie uns dies bitte
mit, indem Sie die E-Mail an den Absender zurücksenden. Bitte löschen Sie
danach diese E-Mail.
This email is confidential. If you are not the intended recipient, you must
not disclose or use the information contained in it.
If you have received this mail in error, please tell us immediately by
return email and delete the document.


-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 28. April 2017 22:17
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] CTE Question

Hi Olaf!

First, SQL doesn't like unknown columns, you need to know at least the
maximum possible number of properties to support. Having said that, you
could try something like:

select B1.Property, B2.Property, B3.Property, B4.Property from TableA A1
join TableB B1 on A1.ID = B1.ID_TableA left join TableA A2 on A2.Pos = 2
left join TableB B2 on A2.ID = B2.ID_TableA left join TableA A3 on A3.Pos =
3 left join TableB B3 on A3.ID = B3.ID_TableA left join TableA A4 on A4.Pos
= 4 left join TableB B4 on A4.ID = B4.ID_TableA left join TableC C on
C.ID_TableB_1 in (A1.ID, A2.ID, A3.ID, A4.ID)
and (C.ID_TableB_2 in (A1.ID, A2.ID, A3.ID, A4.ID) or
C.ID_TableB_2 is null)
and (C.ID_TableB_3 in (A1.ID, A2.ID, A3.ID, A4.ID) or
C.ID_TableB_3 is null)
and (C.ID_TableB_4 in (A1.ID, A2.ID, A3.ID, A4.ID) or
C.ID_TableB_4 is null) where A1.Pos = 1
   and C.ID is null

Since the left joins to TableA doesn't refer to other tables, I assume the
left joins between TableA and TableB to be the same as cross joins.

TableC is for your next step, I'm assuming TableC could be defined like:
ID, ID_TableB_1, ID_TableB_2, ID_TableB_3, ID_TableB_4

and that not red, 60W could be written like:

1, 101, 109, , 

(assuming 101 to be red and 109 to be 60W)

It is of course thinkable that TableC also could have rows rather than
columns for properties like TableB, but that would make the query more
complex.

HTH,
Set






++

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

AW: [firebird-support] CTE Question

2017-05-01 Thread 'Olaf Kluge' olaf.kl...@satron.de [firebird-support] 
Sorry, 3 categories = 3 layer


Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 2. Mai 2017 08:36
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] CTE Question

  
Hi SET,

I would like to specify this:

Table A:

CREATE TABLE T_L_PRKOM_GRP (
ID_GRP INTEGER NOT NULL,
ID_KOM INTEGER NOT NULL,
POS INTEGER,
ID INTEGER NOT NULL
);

Example:

INSERT INTO T_L_PRKOM_GRP (ID_GRP, ID_KOM, POS, ID)
VALUES (1, 2, 1, 15);
INSERT INTO T_L_PRKOM_GRP (ID_GRP, ID_KOM, POS, ID)
VALUES (8, 2, 2, 16);
INSERT INTO T_L_PRKOM_GRP (ID_GRP, ID_KOM, POS, ID)
VALUES (9, 2, 3, 17);

COMMIT WORK;

Table B:

CREATE TABLE T_L_PRKOM_GRP_POS (
KENNZEICHEN VARCHAR(10) NOT NULL COLLATE DE_DE,
BEZEICHNUNG VARCHAR(50) COLLATE DE_DE,
ID INTEGER NOT NULL,
ID_KOM_GRP INTEGER,
PRICE DOUBLE PRECISION
);

Examples:

INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('00', NULL, 28, 15, 5, 1, 2, 1, 15);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('01', NULL, 29, 15, 6, 1, 2, 1, 15);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('02', NULL, 30, 15, 7, 1, 2, 1, 15);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('55', NULL, 32, 16, 1, 8, 2, 2, 16);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('56', NULL, 33, 16, 2, 8, 2, 2, 16);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('57', NULL, 34, 16, 3, 8, 2, 2, 16);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('88', NULL, 35, 17, 2, 9, 2, 3, 17);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('89', NULL, 36, 17, 3, 9, 2, 3, 17);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('90', NULL, 37, 17, 4, 9, 2, 3, 17);

COMMIT WORK;

Now we have 2 categories, 3 layers and I would like every combination like
this:

list(pos), List(id from t_l_prkom_grp_pos), list(price), List(kennzeichen)

pos(1,2,3), id(28,32,35), price(5,1,2), (00,55,88)
pos(1,2,3), id(28,32,36), price(5,1,3), (00,55,89)
pos(1,2,3), id(28,32,37), price(5,1,4), (00,55,90)
pos(1,2,3), id(28,33,35), price(5,2,2), (00,56,88)
..
Pos(1,2,3), id(30,34,37), price(7,3,4), (02,57,90)

I can create with this informations records like this

Part-number, price..
005588, 8 Euro..
005589, 9 Euro..
005590, 10 Euro..
005688, 9 Euro
..
025790, 14 Euro

My test procedure works not in every case, I get the following (not after 3
category-combinations a return and not every informations:

create or alter procedure P_TMP_LITESA
returns (
A varchar(3000),
B varchar(3000),
C varchar(3000),
D varchar(3000))
AS
declare variable kennung varchar(10);
declare variable id_kom_grp integer;
declare variable tmp_kennzeichen varchar(10);
declare variable tmp_pos integer;
declare variable pos2 integer;
declare variable pos integer;
declare variable tmp1 varchar(100);
declare variable tmp2 varchar(100);
declare variable tmp3 varchar(10);
declare variable tmp_pos1 integer;
declare variable i integer = 0;
begin
-- teilenr = '';
-- select kennung from t_l_prkom where id = 2 into :kennung;

for with recursive ok as
(select a.pos, b.kennzeichen, b.price, b.id from t_l_prkom_grp a left join
t_l_prkom_grp_pos b on a.id = b.id_kom_grp where a.id_kom = 2
union all
select c.pos, d.kennzeichen, d.price, d.id from t_l_prkom_grp c left join
t_l_prkom_grp_pos d on c.id = d.id_kom_grp
inner join ok on c.pos = ok.pos+1
where c.id_kom = 2 order by pos)
select list(pos), list(kennzeichen), list(price), list(id) from ok
into :a, :b, :c, :d do
begin
suspend;

end
/*
select pos, kennzeichen from ok into :idid, :k do
begin

/*
if(tmp_pos1 = 8) then
begin
suspend;
teilenr = left(teilenr,char_length(teilenr)-(2*(8-:idid+1)));
end
teilenr = teilenr || k;
tmp_pos1 = idid;

end
*/

End

How can I optimize this?

Thank you.

Mit freundlichen Grüßen / with best regards

Olaf Kluge

S A T R O N  Sachsen 
Steuerungstechnik GmbH
Johann-Gottlob-Pfaff Straße 7
D-09405 Zschopau

Tel: +49 (0) 3725 / 3506-31
Fax:    +49 (0) 3725 / 3506-12
Mobil:  +49 (0) 170 / 9292375
E-Mail:  mailto:olaf.kl...@satron.de
Internet: http://www.satron.de/



Geschäftsführer: Bernd Grötzschel, Gerd Kaden
Amtsgericht: Chemnitz HRB1218
Ust-ID-Nr: DE141294791



Diese E-Mail ist vertraul

AW: [firebird-support] CTE Question

2017-05-02 Thread 'Olaf Kluge' olaf.kl...@satron.de [firebird-support] 
Hello,

 

Ive testet this, but it is’nt the best solution, I think:

 

..and why does I get the last positions (combinations) from the recursive
cte twice?

 

create or alter procedure P_TMP_LITESA

returns (

A varchar(3000),

B varchar(3000),

C varchar(3000),

D varchar(3000),

E varchar(3000))

AS

declare variable kennung varchar(10);

declare variable id_kom_grp integer;

declare variable tmp_kennzeichen varchar(10);

declare variable tmp_pos integer;

declare variable pos2 integer;

declare variable pos integer;

declare variable tmp1 varchar(100);

declare variable tmp2 varchar(100);

declare variable tmp3 varchar(10);

declare variable tmp_pos1 integer;

declare variable i integer = 0;

declare variable i1 integer;

declare variable i2 integer;

declare variable i3 integer;

declare variable d1 double precision;

declare variable t1 varchar(10);

declare variable zae integer;

declare variable del integer = 0;

declare variable maxpos integer;

begin

-- teilenr = '';

-- select kennung from t_l_prkom where id = 2 into :kennung;

a = ',';

b = ',';

c = ',';

d = ',';

for with recursive ok as

(select a.pos, b.kennzeichen, b.price, b.id from t_l_prkom_grp a inner join
t_l_prkom_grp_pos b on a.id = b.id_kom_grp where a.id_kom = 2

union all

select c.pos, d.kennzeichen, d.price, d.id from t_l_prkom_grp c inner join
t_l_prkom_grp_pos d on c.id = d.id_kom_grp

join ok on c.pos = ok.pos+1

where c.id_kom = 2 order by pos)

 

select pos, kennzeichen, price, id from ok into :i1, :t1, :d1, :i2 do

begin

 

if (del = 1) then

begin

   del = 0;

   zae = 3-i1+1;

   while (zae > 0) do

   begin

   a = left(a, char_length(a)-position(',',reverse(a),2)+1);

   b = left(b, char_length(b)-position(',',reverse(b),2)+1);

   c = left(c, char_length(c)-position(',',reverse(c),2)+1);

   d = left(d, char_length(d)-position(',',reverse(d),2)+1);

   zae = zae -1;

   end

end

a = a || :i1 || ',';

b = b || :t1 || ',';

c = c || :d1 || ',';

d = d || :i2 || ',';

if(i1 = 3) then  -- später maxpos

begin

   del = 1;

   suspend;

 

 

--   teilenr = left(teilenr,char_length(teilenr)-(2*(8-:idid+1)));

end

-- teilenr = teilenr || k;

 

 

end

 

 

end

 

I would like to create a record with all categories after the loop is on the
last point for each combination and I take the string (,1,23,21,..) and set
each into a new record oft he piece.

Sorry, 3 categories = 3 layer

Von: firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com> 
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 2. Mai 2017 08:36
An: firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com> 
Betreff: AW: [firebird-support] CTE Question

  
Hi SET,

I would like to specify this:

Table A:

CREATE TABLE T_L_PRKOM_GRP (
ID_GRP INTEGER NOT NULL,
ID_KOM INTEGER NOT NULL,
POS INTEGER,
ID INTEGER NOT NULL
);

Example:

INSERT INTO T_L_PRKOM_GRP (ID_GRP, ID_KOM, POS, ID)
VALUES (1, 2, 1, 15);
INSERT INTO T_L_PRKOM_GRP (ID_GRP, ID_KOM, POS, ID)
VALUES (8, 2, 2, 16);
INSERT INTO T_L_PRKOM_GRP (ID_GRP, ID_KOM, POS, ID)
VALUES (9, 2, 3, 17);

COMMIT WORK;

Table B:

CREATE TABLE T_L_PRKOM_GRP_POS (
KENNZEICHEN VARCHAR(10) NOT NULL COLLATE DE_DE,
BEZEICHNUNG VARCHAR(50) COLLATE DE_DE,
ID INTEGER NOT NULL,
ID_KOM_GRP INTEGER,
PRICE DOUBLE PRECISION
);

Examples:

INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('00', NULL, 28, 15, 5, 1, 2, 1, 15);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('01', NULL, 29, 15, 6, 1, 2, 1, 15);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('02', NULL, 30, 15, 7, 1, 2, 1, 15);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('55', NULL, 32, 16, 1, 8, 2, 2, 16);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('56', NULL, 33, 16, 2, 8, 2, 2, 16);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('57', NULL, 34, 16, 3, 8, 2, 2, 16);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('88', NULL, 35, 17, 2, 9, 2, 3, 17);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('89', NULL, 36, 17, 3, 9, 2, 3, 17);
INSERT INTO T_L_PRKOM_GRP_POS (KENNZEICHEN, BEZEICHNUNG, ID, ID_KOM_GRP,
PRICE, ID_GRP, ID_KOM, POS, ID1)
VALUES ('90', NULL, 37, 17, 4, 9, 2, 3, 17);

COMMIT WORK;

Now we have 2 categories, 3 layers

[firebird-support] CTE, tricky request

2019-06-13 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello,

 

I would like to generate a list with appointments. 

 

In a table tkal_main I set the day (field "t") of the meeting, every month I
would like to get an entry. The description of the appointment is "bez".

 

I helps me with a separate table (tmonate, id 1 = January and so on). 

 

With cte.(

Select other termins

Union all

select cast(a.t || '.' || b.id || '.' || extract(year from current_date) as
timestamp) as ts, a.bez from tkal_main a, tmonate b)

select ts, bez from kal where ts >= current_date and ts < current_date +
:vorschau order by ts into :datum, bez do

 

Vorschau is an integer with days in the future I would show, from tomorrow
to tomorrow + x (vorschau) days

 

Now I get a List of appointments, but if I would get a month of the next
year, the "year from current_date" is the problem.

 

Example:

Day 10 of every Month, today is the 13.06.2019, Vorschau = 365 days I would
get:

 

10.07.2019

10.08.2019

...

10.06.2020

 

At the time I get only til this December. And I have non indexed reads for
table tmonate, because there is no relation. How can I make it better? With
a List instead of the table tmonate? 

 

Thank you.

 

Best regards

 

Olaf

[firebird-support] CTE difficult question

2019-11-11 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello,

 

I have the following situation:

 

There are some parts with parts inside

 

Part A has Part B inside

Part A has Part C inside

 

Part B has BA inside

Part B has BB inside

 

Part BA has BAA inside

Part BA has also BAB inside

Part BB has BBA inside

 

Part C has CA inside

 

A   ->   B->   BA ->   BAA

A   ->   B->   BA ->   BAB

A   ->   B->   BB ->   BBA

A   ->   C->   CA

 

With a cte I can get every last parts, for example BA with BAA, BA with BAB,
BB with BBA and C with CA. This is fine, but I would get the entire
combination in a List

 

Instead of BA - BAA I would get A - B - BA - BAA.

 

Teilenummer is in this case the first left part, Matteilenr is the last part

 

Saved in the Table tmaterial

 

Teilenr (pteilenr)MatteilenrAnzahl (amount)

A   B1

BBA 10

BA BAA   10

BA BAB   5

B   BB  5

BB BBA   4

A   C2

CCA 10

 

The CTE: (tteile is just for the unit)

 

for with recursive ang as(

select a.matteilenr, a.teilenr as pteilenr, a.anzahl * :anzahlt as anzahl,
b.einheitnr, a.kundennr from tmaterial a

left join tteile b on(a.matteilenr = b.teilenr)

where a.teilenr = :teilenr

union all

select aa.matteilenr, aa.teilenr as pteilenr, aa.anzahl * ang2.anzahl,
ab.einheitnr, aa.kundennr from tmaterial aa

left join tteile ab on (aa.matteilenr = ab.teilenr)

inner join ang as ang2 on (aa.teilenr = ang2.matteilenr)

)

select a.matteilenr, a.pteilenr, sum(a.anzahl), a.einheitnr, a.kundennr from
ang a

group by a.matteilenr, a.pteilenr, a.einheitnr, a.kundennr

into :materialnr, :pteilenr, :anzahlm, :einheit, :lieferant do suspend;

 

I give the cte the :teilenr (for Example A) and get every part itself and
every block of two pairs. Now I would get the entire path, all layers.

 

Thank you.

Re: AW: [firebird-support] CTE

2014-02-26 Thread Tim Ward 
I've just this minute come across this again, with INSERT this time 
rather than UPDATE.

For my current problem the following code does not give an error:

INSERT INTO ...
WITH CTE_X AS ( ... )
SELECT ... FROM CTE_X
WHERE ...

-- 
Tim Ward

AW: [firebird-support] CTE, tricky request

2019-06-13 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
...perhaps a solution?

 

  union all

  select cast(a.t || b.str as timestamp) as ts, a.bez from tkal_main a,
sub_datum(:vorschau) as b where a.serie = 2

 

create procedure SUB_DATUM (

days integer)

returns (

str varchar(8))

as

declare variable c_date timestamp;

declare variable t_date timestamp;

declare variable temp_date timestamp;

begin

c_date = current_date;

t_date = current_date + days;

temp_date = c_date;

  while (temp_date < t_date) do

  begin

str = '.' || lpad(extract(month from temp_date),2,'0') || '.' ||
extract(year from temp_date);

suspend;

temp_date = temp_date + 30;

  end

end

 

it works..

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Donnerstag, 13. Juni 2019 16:36
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] CTE, tricky request

 

  

Hello,

 

I would like to generate a list with appointments. 

 

In a table tkal_main I set the day (field "t") of the meeting, every month I
would like to get an entry. The description of the appointment is "bez".

 

I helps me with a separate table (tmonate, id 1 = January and so on). 

 

With cte.(

Select other termins

Union all

select cast(a.t || '.' || b.id || '.' || extract(year from current_date) as
timestamp) as ts, a.bez from tkal_main a, tmonate b)

select ts, bez from kal where ts >= current_date and ts < current_date +
:vorschau order by ts into :datum, bez do

 

Vorschau is an integer with days in the future I would show, from tomorrow
to tomorrow + x (vorschau) days

 

Now I get a List of appointments, but if I would get a month of the next
year, the "year from current_date" is the problem.

 

Example:

Day 10 of every Month, today is the 13.06.2019, Vorschau = 365 days I would
get:

 

10.07.2019

10.08.2019

...

10.06.2020

 

At the time I get only til this December. And I have non indexed reads for
table tmonate, because there is no relation. How can I make it better? With
a List instead of the table tmonate? 

 

Thank you.

 

Best regards

 

Olaf

AW: [firebird-support] CTE difficult question

2019-11-12 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Ist it possible somehow? 

 

I would get each Part and the complete Path for it. If the Part is a
material, I would get it and the complete list with all parts from the first
til the material itself.

 

Thanks. 

 



  

Hello,

 

I have the following situation:

 

There are some parts with parts inside

 

Part A has Part B inside

Part A has Part C inside

 

Part B has BA inside

Part B has BB inside

 

Part BA has BAA inside

Part BA has also BAB inside

Part BB has BBA inside

 

Part C has CA inside

 

A   ->   B->   BA ->   BAA

A   ->   B->   BA ->   BAB

A   ->   B->   BB ->   BBA

A   ->   C->   CA

 

With a cte I can get every last parts, for example BA with BAA, BA with BAB,
BB with BBA and C with CA. This is fine, but I would get the entire
combination in a List

 

Instead of BA - BAA I would get A - B - BA - BAA.

 

Teilenummer is in this case the first left part, Matteilenr is the last part

 

Saved in the Table tmaterial

 

Teilenr (pteilenr)MatteilenrAnzahl (amount)

A   B1

BBA 10

BA BAA   10

BA BAB   5

B   BB  5

BB BBA   4

A   C2

CCA 10

 

The CTE: (tteile is just for the unit)

 

for with recursive ang as(

select a.matteilenr, a.teilenr as pteilenr, a.anzahl * :anzahlt as anzahl,
b.einheitnr, a.kundennr from tmaterial a

left join tteile b on(a.matteilenr = b.teilenr)

where a.teilenr = :teilenr

union all

select aa.matteilenr, aa.teilenr as pteilenr, aa.anzahl * ang2.anzahl,
ab.einheitnr, aa.kundennr from tmaterial aa

left join tteile ab on (aa.matteilenr = ab.teilenr)

inner join ang as ang2 on (aa.teilenr = ang2.matteilenr)

)

select a.matteilenr, a.pteilenr, sum(a.anzahl), a.einheitnr, a.kundennr from
ang a

group by a.matteilenr, a.pteilenr, a.einheitnr, a.kundennr

into :materialnr, :pteilenr, :anzahlm, :einheit, :lieferant do suspend;

 

I give the cte the :teilenr (for Example A) and get every part itself and
every block of two pairs. Now I would get the entire path, all layers.

 

Thank you.

AW: [firebird-support] CTE difficult question

2019-11-13 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Sorry, I don't want to annoy, but can I do this with a separate
list-function or is it possible to realize it with new functions in firebird
4?

 

TA TB

A   B   B is a part from A

A   C   C is a part from A

A   D   D is a part from A

D   X   X is a part from D, X is material

D   Y   Y is a part from D, Y is material

C   Z   Z is a part from C, Z is material

B   Z   Z is a part from B, Z is material

 

Now I would get all from A with Material:

 

A - B - Z1, material:Z1

A - C - Z, material: Z

A - D - X, material: X

A - D - Y, material: Y

 

Later I can build a sum from all materials, for example Z

 

Thank you.

 

Von: firebird-support@yahoogroups.com  
Gesendet: Dienstag, 12. November 2019 14:23
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] CTE difficult question

 

  

Ist it possible somehow? 

 

I would get each Part and the complete Path for it. If the Part is a
material, I would get it and the complete list with all parts from the first
til the material itself.

 

Thanks. 

 



  

Hello,

 

I have the following situation:

 

There are some parts with parts inside

 

Part A has Part B inside

Part A has Part C inside

 

Part B has BA inside

Part B has BB inside

 

Part BA has BAA inside

Part BA has also BAB inside

Part BB has BBA inside

 

Part C has CA inside

 

A   ->   B->   BA ->   BAA

A   ->   B->   BA ->   BAB

A   ->   B->   BB ->   BBA

A   ->   C->   CA

 

With a cte I can get every last parts, for example BA with BAA, BA with BAB,
BB with BBA and C with CA. This is fine, but I would get the entire
combination in a List

 

Instead of BA - BAA I would get A - B - BA - BAA.

 

Teilenummer is in this case the first left part, Matteilenr is the last part

 

Saved in the Table tmaterial

 

Teilenr (pteilenr)MatteilenrAnzahl (amount)

A   B1

BBA 10

BA BAA   10

BA BAB   5

B   BB  5

BB BBA   4

A   C2

CCA 10

 

The CTE: (tteile is just for the unit)

 

for with recursive ang as(

select a.matteilenr, a.teilenr as pteilenr, a.anzahl * :anzahlt as anzahl,
b.einheitnr, a.kundennr from tmaterial a

left join tteile b on(a.matteilenr = b.teilenr)

where a.teilenr = :teilenr

union all

select aa.matteilenr, aa.teilenr as pteilenr, aa.anzahl * ang2.anzahl,
ab.einheitnr, aa.kundennr from tmaterial aa

left join tteile ab on (aa.matteilenr = ab.teilenr)

inner join ang as ang2 on (aa.teilenr = ang2.matteilenr)

)

select a.matteilenr, a.pteilenr, sum(a.anzahl), a.einheitnr, a.kundennr from
ang a

group by a.matteilenr, a.pteilenr, a.einheitnr, a.kundennr

into :materialnr, :pteilenr, :anzahlm, :einheit, :lieferant do suspend;

 

I give the cte the :teilenr (for Example A) and get every part itself and
every block of two pairs. Now I would get the entire path, all layers.

 

Thank you.

[firebird-support] CTE with unknown input parameter

2012-05-22 Thread Svein Erling Tysvær 
Hi, I'm trying to use a CTE a bit differently from what I usually do, but am 
failing.

WITH MyCTE AS
(SELECT CAST(:FromMainTable AS VarChar(5)) FromMainTable, Field1
FROM MySmallLookupTable
WHERE Field2 = :FromMainTable
UNION
SELECT CAST(:FromMainTable AS VarChar(5)), Field1
FROM MySmallLookupTable
WHERE Field2 = substring(:FromMainTable FROM 1 FOR 4))
...

SELECT ...
FROM MyMainAndLargerTable MT
LEFT JOIN MyCTE CTE ON MT.Afield = CTE.FromMainTable
LEFT JOIN MyCTE CTE2 ON MT.Anotherfield = CTE2.FromMainTable
...

The problem with this is that upon prepare, DB Workbench (naturally?) wants me 
to supply a value for the parameter :FromMainTable, a value that will vary from 
row to row.

Of course, I can easily get a single value using

SELECT ...
FROM MyCTE CTE
WHERE Field2 = :FromMainTable

but I'd prefer the entire result set to be returned within one query.

(The actual query was 166 lines in Fb 1.5 and when I noticed I had to fix it 
(which would make it grow), I thought that I could take advantage of CTEs in Fb 
2.5 to prevent it from increasing to 500 lines).

Should it at all be possible to use CTEs in a way similar to this or would I 
have to either let my original query grow or use EXECUTE BLOCK?

Set


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

[firebird-support] CTE Recursive left join problem

2017-10-02 Thread Germán Balbi bal...@yahoo.com [firebird-support] 
Hello everyoneI'm having a problem, and I do not understand why.I have a 
structure similar to:
with recursive
 aux as (
  select 0 as n
    from rdb$database  union all  select aux.n + 1
    from aux
    where aux.n <= 20
 )
 select aux.n, mytable.id
 from aux
   LEFT JOIN mytable on aux.n between mytable.id and mytable.id +1  where 
mytable.id is not null
Where the left join behaves as join.
Any condition that I put in the clause WHERE referred to mytable, converts the 
LEFT JOIN into JOIN

[firebird-support] CTE Spaces between every day

2019-08-12 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello,

 

I have created a CTE in a stored prodedure which gives me all terms of the
next days, for example:

 

01.10.2019   Meeting with..

01.10.2019   Exkursion

02.10.2019  Termin

04.10.2019  Termin XX

 

Now I would set between every day a blank record, a kind of space.

 

01.10.2019   Meeting with..

01.10.2019   Exkursion

 

02.10.2019  Termin

 

04.10.2019  Termin XX

 

That I can realize this, I have set a counter, each record, 1,2,3..

 

Here a part of my procedure:

 

  for with kalT as(

  select cast(m.t || '.' || m.m || '.' || m.j as timestamp) as ts, m.bez,
m.id, m.zeit, coalesce(w.b,0) - coalesce(w.q,0) as qself

  from tkal_main m left join tkal_wek w on m.id = w.main_id

  where m.serie = 3 and w.wek_id = :wek_team

 

… each other..

 

  -- halbjährlich Termin

  union all

  select cast(p.str as timestamp) as ts, m.bez, m.id, m.zeit,
coalesce(w.b,0) - coalesce(w.q,0) as qself from tkal_main m left join
tkal_wek w on m.id = w.main_id, p_sub_datum2(:vorschau,1,m.t,m.m,6) p

  where m.serie = 6 and w.wek_id = :wek_team -- noch überarbeiten, jährlich,
unterfunktion und vorwarnung extra

 

  -- halbjährlich Vorwarnung

  union all

  select cast(p.str as timestamp) - e.t_before as ts , m.bez || '
Vorwarnung', m.id, m.zeit, coalesce(w.b,0) - coalesce(w.q,0) as qself from
tkal_main m left join tkal_wek w on m.id = w.main_id left join tkal_erin e

  on m.id = e.main_id, p_sub_datum2(:vorschau,1,m.t,m.m,6) as p  where
m.serie = 6 and w.wek_id = :wek_team

  )

  select :ds + 1, ts, bez, id, zeit, qself, s.gverh from kalT,
p_sub_datums(id) s where ts >= current_date and ts < current_date +
:vorschau order by ts into :ds, :datum, :bez, :ds_id, :zeit, :qs, :gv do

  suspend;

 

Can I select a blank record between the days?

 

Thank you.

Re: AW: [firebird-support] CTE difficult question

2019-11-13 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
 
from your description i really do not know what is working for you and what is 
not working.
And your expectation.
 
but to understand recursive CTE look at simple sample. Recursive CTE work 
throught tree.
 
### metadata ###
CREATE TABLE TEST_TREE
(
  ID INTEGER NOT NULL,
  ID_HEADER INTEGER,
  CONSTRAINT PK_TEST_TREE__ID PRIMARY KEY (ID)
);
CREATE INDEX IXA_TEST_TREE__ID_HEADER ON TEST_TREE (ID_HEADER);
### test data ###
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('1', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('2', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('3', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('4', '1');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('5', '4');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('6', '2');
### test query ###
 WITH RECURSIVE
 R_TREE AS
 (
 SELECT TT.ID AS A, CAST(TT.ID AS VARCHAR(100)) AS ASUM, 0 AS LEVEL
 FROM TEST_TREE TT
 WHERE TT.ID_HEADER IS NULL
 
 UNION ALL
     
 SELECT TT.ID AS A, RT.ASUM || '_' || TT.ID, LEVEL + 1
 FROM TEST_TREE TT JOIN R_TREE RT ON RT.A = TT.ID_HEADER
 )
 SELECT
 *
 
 FROM
 R_TREE RT2 INNER JOIN TEST_TREE TT2 ON TT2.ID=RT2.A
 
###
 
run it and then addapt to your needs, as your situation looks same to me
 
regards,
Karol Bieniaszewski
 
 

AW: AW: [firebird-support] CTE difficult question

2019-11-13 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello Karol,

 

thank you, that was the one piece I was looking for, the right position for the 
listing. 

 

Best thanks.

 

Regards.

 

Olaf

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 13. November 2019 09:51
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] CTE difficult question

 

  

Hi,

 

from your description i really do not know what is working for you and what is 
not working.

And your expectation.

 

but to understand recursive CTE look at simple sample. Recursive CTE work 
throught tree.

 

### metadata ###

CREATE TABLE TEST_TREE
(
  ID INTEGER NOT NULL,
  ID_HEADER INTEGER,
  CONSTRAINT PK_TEST_TREE__ID PRIMARY KEY (ID)
);

CREATE INDEX IXA_TEST_TREE__ID_HEADER ON TEST_TREE (ID_HEADER);

### test data ###

INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('1', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('2', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('3', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('4', '1');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('5', '4');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('6', '2');

### test query ###

 WITH RECURSIVE

 R_TREE AS

 (

 SELECT TT.ID AS A, CAST(TT.ID AS VARCHAR(100)) AS ASUM, 0 AS LEVEL

 FROM TEST_TREE TT

 WHERE TT.ID_HEADER IS NULL

 

 UNION ALL

 

 SELECT TT.ID AS A, RT.ASUM || '_' || TT.ID, LEVEL + 1

 FROM TEST_TREE TT JOIN R_TREE RT ON RT.A = TT.ID_HEADER

 )

 SELECT

 *

 

 FROM

 R_TREE RT2 INNER JOIN TEST_TREE TT2 ON TT2.ID=RT2.A

 

###

 

run it and then addapt to your needs, as your situation looks same to me

 

regards,

Karol Bieniaszewski

Re: [firebird-support] CTE with unknown input parameter

2012-05-22 Thread Martijn Tonies 
Hello Set,

> Hi, I'm trying to use a CTE a bit differently from what I usually do, but 
> am failing.
>
> WITH MyCTE AS
> (SELECT CAST(:FromMainTable AS VarChar(5)) FromMainTable, Field1

Wouldn't this be the value of Field2 then?

> FROM MySmallLookupTable
> WHERE Field2 = :FromMainTable
> UNION
> SELECT CAST(:FromMainTable AS VarChar(5)), Field1

Same here?

> FROM MySmallLookupTable
> WHERE Field2 = substring(:FromMainTable FROM 1 FOR 4))
> ...
>
> SELECT ...
> FROM MyMainAndLargerTable MT
> LEFT JOIN MyCTE CTE ON MT.Afield = CTE.FromMainTable
> LEFT JOIN MyCTE CTE2 ON MT.Anotherfield = CTE2.FromMainTable
> ...
>
> The problem with this is that upon prepare, DB Workbench (naturally?) 
> wants me to supply a value for the parameter :FromMainTable, a value that 
> will vary from row to row.
>
> Of course, I can easily get a single value using
>
> SELECT ...
> FROM MyCTE CTE
> WHERE Field2 = :FromMainTable
>
> but I'd prefer the entire result set to be returned within one query.
>
> (The actual query was 166 lines in Fb 1.5 and when I noticed I had to fix 
> it (which would make it grow), I thought that I could take advantage of 
> CTEs in Fb 2.5 to prevent it from increasing to 500 lines).
>
> Should it at all be possible to use CTEs in a way similar to this or would 
> I have to either let my original query grow or use EXECUTE BLOCK?

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

RE: [firebird-support] CTE with unknown input parameter

2012-05-23 Thread Svein Erling Tysvær 
>Hello Set,

Hello Martijn!

>> WITH MyCTE AS
>> (SELECT CAST(:FromMainTable AS VarChar(5)) FromMainTable, Field1
>
>Wouldn't this be the value of Field2 then?

Sure, but that's not the problem (just a desparate workaround I attempted).

>> UNION
>> SELECT CAST(:FromMainTable AS VarChar(5)), Field1
>> FROM MySmallLookupTable
>> WHERE Field2 = substring(:FromMainTable FROM 1 FOR 4)) ...
>
>Same here?

No, Field2 might e.g. be '1000' whereas :FromMainTable would be '1'.

My real problem/question is that I don't know the value of :FromMainTable until 
the main select attempts:

LEFT JOIN MyCTE CTE ON MT.Afield = CTE.FromMainTable

The simplest way to put my question would then be (though oversimplifying, 
probably leading to answers that doesn't work in the real case):

Can

SELECT ...
FROM A 
LEFT JOIN B B1 ON A.FIELD1 = B1.FIELD1
LEFT JOIN B B2 ON A.FIELD2 = B2.FIELD1

in any way be replaced by a construct similar to

WITH TMP_B AS 
(SELECT ... FROM B WHERE FIELD1 = ???)
SELECT ...
FROM A 
LEFT JOIN TMP_B B1 ON A.FIELD1 = B1.FIELD1
LEFT JOIN TMP_B B2 ON A.FIELD2 = B2.FIELD1

The problem is of course that the value for ??? will come from the LEFT JOIN of 
the outer select (for one alias it should match A.FIELD1, for another 
A.FIELD2). I suspect I have to put that in the LEFT JOIN itself rather than use 
the CTE, but that would make the real query (with 10 LEFT JOINs, each 
containing about 10 ORs and one NOT EXISTS and the error discovered could 
require an additional nine NOT EXISTS with up to 45 ORs for each LEFT JOIN) 
grow considerably from its current 166 lines (possibly making the query 700 
lines - I will probably consider modifying it to EXECUTE BLOCK, I dislike 
writing unnecessarily verbose SQL or code).

Set

RE: [firebird-support] CTE with unknown input parameter

2012-05-23 Thread Svein Erling Tysvær 
>My real problem/question is that I don't know the value of :FromMainTable 
>until the main select attempts:
>
>LEFT JOIN MyCTE CTE ON MT.Afield = CTE.FromMainTable
>
>The simplest way to put my question would then be (though oversimplifying, 
>probably leading to answers that doesn't work in the real case):
>
>Can
>
>SELECT ...
>FROM A
>LEFT JOIN B B1 ON A.FIELD1 = B1.FIELD1
>LEFT JOIN B B2 ON A.FIELD2 = B2.FIELD1
>
>in any way be replaced by a construct similar to
>
>WITH TMP_B AS
>(SELECT ... FROM B WHERE FIELD1 = ???)
>SELECT ...
>FROM A
>LEFT JOIN TMP_B B1 ON A.FIELD1 = B1.FIELD1 LEFT JOIN TMP_B B2 ON A.FIELD2 = 
>B2.FIELD1
>
>The problem is of course that the value for ??? will come from the LEFT JOIN 
>of the 
>outer select (for one alias it should match A.FIELD1, for another A.FIELD2). I 
>suspect 
>I have to put that in the LEFT JOIN itself rather than use the CTE, but that 
>would make
>the real query (with 10 LEFT JOINs, each containing about 10 ORs and one NOT 
>EXISTS and
>the error discovered could require an additional nine NOT EXISTS with up to 45 
>ORs for 
>each LEFT JOIN) grow considerably from its current 166 lines (possibly making 
>the query
>700 lines - I will probably consider modifying it to EXECUTE BLOCK, I dislike 
>writing 
>unnecessarily verbose SQL or code).

Fixed my problem very differently, rather than having 10 LEFT JOINs with many 
ORs and NOT EXISTS, I ended up with 100 LEFT JOINs and 10 occurrences of 
COALESCE with 11 possible values each. The query seems to perform OK and is 
easily readable with only 122 lines (including the 100 LEFT JOINs).

Set

Re: [firebird-support] CTE Recursive left join problem

2017-10-02 Thread 'livius' liviusliv...@poczta.onet.pl [firebird-support] 
Hi,

this is normal sql behavior
if you do not need to implicit INNER JOIN condition then move filter to left 
join itself
in your situation:
instead “where mytable.id is not null” write “ AND mytable.id is not null”

...
select aux.n, mytable.id
from aux
   LEFT JOIN mytable on aux.n between mytable.id and mytable.id +1 and 
mytable.id is not null
...

regards,
Karol Bieniaszewski

From: Germán Balbi bal...@yahoo.com [firebird-support] 
Sent: Monday, October 2, 2017 7:37 PM
To: Firebird-support 
Subject: [firebird-support] CTE Recursive left join problem

  

Hello everyone
I'm having a problem, and I do not understand why.
I have a structure similar to:with recursive aux as (  select 0 as nfrom 
rdb$database  union all  select aux.n + 1from auxwhere aux.n <= 20 )  
select aux.n, mytable.id from aux   LEFT JOIN mytable on aux.n between 
mytable.id and mytable.id +1  where mytable.id is not null Where the left join 
behaves as join. Any condition that I put in the clause WHERE referred to 
mytable, converts the LEFT JOIN into JOIN

Re: [firebird-support] CTE Spaces between every day

2019-08-12 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
 
simply add loop inside
do
  begin
     suspend;
    --put here some loop
    while something do
      begin
          --modify output variables here
          suspend;
      end
  end;
 
regards,
Karol Bieniaszewski

AW: [firebird-support] CTE Spaces between every day

2019-08-12 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello Karol,

 

I had the same idea, but it is a little bit complex. First, I must save the 
last date, if the new one ist different, I must save all return variables, set 
it to null, suspend the record and restore the saved return values and suspend 
this. So I have the old Date, the blank record and the new one. I thought, 
there is a simplier way.

 

Thank you.

 

Von: firebird-support@yahoogroups.com  
Gesendet: Montag, 12. August 2019 12:17
An: firebird-support@yahoogroups.com; 'Check_Mail' check_m...@satron.de 
[firebird-support] 
Betreff: Re: [firebird-support] CTE Spaces between every day

 

  

Hi,

 

simply add loop inside

do

  begin

 suspend;

--put here some loop

while something do

  begin

  --modify output variables here

  suspend;

  end

  end;

 

regards,

Karol Bieniaszewski

Re: [firebird-support] CTE Spaces between every day

2019-08-12 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Maybe you could add something like:

union all
select distinct cast(m.t || '.' || m.m || '.' || m.j as timestamp), null,
null, null, null
from tkal_main m
where m.serie in ( 3, 6 ) and w.wek_id = :wek_team

It won't quite be a blank line, but a line only containing the date.

HTH,
Set

man. 12. aug. 2019 kl. 14:23 skrev 'Check_Mail' check_m...@satron.de
[firebird-support] :

>
>
> Hello Karol,
>
>
>
> I had the same idea, but it is a little bit complex. First, I must save
> the last date, if the new one ist different, I must save all return
> variables, set it to null, suspend the record and restore the saved return
> values and suspend this. So I have the old Date, the blank record and the
> new one. I thought, there is a simplier way.
>
>
>
> Thank you.
>
>
>
> *Von:* firebird-support@yahoogroups.com 
>
> *Gesendet:* Montag, 12. August 2019 12:17
> *An:* firebird-support@yahoogroups.com; 'Check_Mail' check_m...@satron.de
> [firebird-support] 
> *Betreff:* Re: [firebird-support] CTE Spaces between every day
>
>
>
>
>
> Hi,
>
>
>
> simply add loop inside
>
> do
>
>   begin
>
>  suspend;
>
> --put here some loop
>
> while something do
>
>   begin
>
>   --modify output variables here
>
>   suspend;
>
>   end
>
>   end;
>
>
>
> regards,
>
> Karol Bieniaszewski
>
>
>
> 
>

AW: [firebird-support] CTE Spaces between every day

2019-08-12 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello Set,

 

okay, I will test and check it, if I can take it for my situation.

 

Serie can be daily, weekly, monthly, half of a year or a quarter of a year, all 
this with reminder (days before). So it can be the situation, that I have 5 
records for one day (daily and montly entries for example) and one of weekly 
and if there is the next day with terms, I would like to show a blank line.

 

Actually I have realize this with

 

Do 

Begin

If(date <> save date)

Begin

Save output values

Set output values to null

Return

Restore output values

Set the counter + 1

end

 

Save the actually date

 

Thanks, it should work and it is okay.

 

Best regards

 

Olaf

 

Von: firebird-support@yahoogroups.com  
Gesendet: Montag, 12. August 2019 15:04
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] CTE Spaces between every day

 

  

Maybe you could add something like:

 

union all
select distinct cast(m.t || '.' || m.m || '.' || m.j as timestamp), null, null, 
null, null
from tkal_main m
where m.serie in ( 3, 6 ) and w.wek_id = :wek_team

 

It won't quite be a blank line, but a line only containing the date.

 

HTH,

Set

 

man. 12. aug. 2019 kl. 14:23 skrev 'Check_Mail' check_m...@satron.de 
<mailto:check_m...@satron.de>  [firebird-support] 
mailto:firebird-support@yahoogroups.com> >:

 

Hello Karol,

 

I had the same idea, but it is a little bit complex. First, I must save the 
last date, if the new one ist different, I must save all return variables, set 
it to null, suspend the record and restore the saved return values and suspend 
this. So I have the old Date, the blank record and the new one. I thought, 
there is a simplier way.

 

Thank you.

 

Von: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> 
 mailto:firebird-support@yahoogroups.com> > 
Gesendet: Montag, 12. August 2019 12:17
An: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> 
; 'Check_Mail' check_m...@satron.de <mailto:check_m...@satron..de>  
[firebird-support] mailto:firebird-support@yahoogroups.com> >
Betreff: Re: [firebird-support] CTE Spaces between every day

 

  

Hi,

 

simply add loop inside

do

  begin

 suspend;

--put here some loop

while something do

  begin

  --modify output variables here

  suspend;

  end

  end;

 

regards,

Karol Bieniaszewski

Re: [firebird-support] CTE Spaces between every day

2019-08-12 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2019-08-12 11:53, 'Check_Mail' check_m...@satron.de 
[firebird-support] wrote:
> Hello,
> 
> I have created a CTE in a stored prodedure which gives me all terms of
> the next days, for example:
> 
> 01.10.2019   Meeting with..
> 
> 01.10.2019   Exkursion
> 
> 02.10.2019  Termin
> 
> 04.10.2019  Termin XX
> 
> Now I would set between every day a blank record, a kind of space.
> 
[..]
> 
> Can I select a blank record between the days?

That is not something you should fix in your query, you should do that 
in your presentation layer (eg add a blank line when the date changes)

Mark

[firebird-support] CTE query works on fb 2.5 but not on fb 3.0

2018-02-01 Thread Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] 
Good afternoon.
I’ve been assigned to debug a query that was working on 2.5 but stopped
workin on 3.0.

 

As of now I have little information on the context of where this query is
used, but I’m assuming it might be something simple.

 

Apparently what’s causing the error is using this FROM sintax with multiple
tables separated by comma (FROM tablea, tableb, tablec).

I’m assuming that because the error is always thrown on the line subsequent
to the “from” line


Was there any change to this type of from syntax in firebird 3.0? 
Below is the query. If needed I can create a test gdb with data.

Thanks in advance

 

 

 

with recursive sucessoras (task_id) as

(

select prj_task_dependencies.dependencies_task_id as task_id

from prj_task_dependencies

inner join prj_tasks t_req on t_req.task_id =
prj_task_dependencies.dependencies_req_task_id

and t_req.task_status = 0

inner join prj_tasks t_dep on t_dep.task_id =
prj_task_dependencies.dependencies_task_id

and t_dep.task_status = 0

where prj_task_dependencies.dependencies_req_task_id = 98

union all

select

prj_task_dependencies.dependencies_task_id as task_id

from prj_task_dependencies, sucessoras

inner join prj_tasks t_req on t_req.task_id =
prj_task_dependencies.dependencies_req_task_id

and t_req.task_status = 0

inner join prj_tasks t_dep on t_dep.task_id =
prj_task_dependencies.dependencies_task_id

and t_dep.task_status = 0

where prj_task_dependencies.dependencies_req_task_id =
sucessoras.task_id

)

 

select

 

distinct(sucessoras.task_id) ,

t.idbpo_proc, 

t.task_name,

t.task_project as idclienteprojeto,

t.task_constraint,

t.task_constraint_date,

t.TASK_BASELINE_DURATION as THIS_TASK_BASELINE_DURATION,

t.TASK_BASELINE_DURATION_D as THIS_TASK_BASELINE_DURATION_D,

t.TASK_DURATION_TYPE as THIS_TASK_DURATION_TYPE,

t.task_baseline_start as THIS_TASK_BASELINE_START,

t.task_baseline_end as THIS_TASK_BASELINE_END,

 

cast(t.task_baseline_start as time) as
THIS_TIME_TASK_BASELINE_START,

cast(t.task_baseline_end as time) as THIS_TIME_TASK_BASELINE_END,   

 

t.recorrencia_semanal_domingo,

t.recorrencia_semanal_segunda,

t.recorrencia_semanal_terca,

t.recorrencia_semanal_quarta,

t.recorrencia_semanal_quinta,

t.recorrencia_semanal_sexta,

t.recorrencia_semanal_sabado,

 

(select max(t_req.task_baseline_end)

from prj_task_dependencies td

inner join prj_tasks t_req on t_req.task_id =
td.dependencies_req_task_id

and t_req.task_status = 0

inner join prj_tasks t_dep on t_dep.task_id =
td.dependencies_task_id

and t_dep.task_status = 0

where td.dependencies_task_id = sucessoras.task_id) as
PRED_TASK_BASELINE_END

 

from prj_task_dependencies , sucessoras , prj_tasks t

inner join prj_tasks t_req on t_req.task_id =
prj_task_dependencies.dependencies_req_task_id

and t_req.task_status = 0

inner join prj_tasks t_dep on t_dep.task_id =
prj_task_dependencies.dependencies_task_id

and t_dep.task_status = 0

where prj_task_dependencies.dependencies_task_id =
sucessoras.task_id

and t.task_id = sucessoras.task_id

 

 

 

Atenciosamente,

Rudi Feijó


Multidados Informática Ltda.
*  (11) 2579-8794 / 2579-8795
*   rudi.fe...@multidadosti.com.br
*   www.multidadosti.com.br
  www.timesheet.com.br

 



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

Re: [firebird-support] CTE query works on fb 2.5 but not on fb 3.0

2018-02-01 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
Your problem has nothing to do with CTEs, you are combining implicit 
(SQL-89) joins and explicit (SQL-92) joins, and referencing the 
implicitly joined table from the explicitly joined tables. The rules for 
these have changed in Firebird 3 to be compliant with the SQL 
specification and to avoid hard to diagnose bugs.

See the Firebird 3 release notes: Support for Mixed-Syntax Joins is Gone 
: 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-compat-sql.html#rnfb30-compat-sql_psql-nomixedjoins

Specifically, you need to change:

> from prj_task_dependencies , sucessoras , prj_tasks t
> 
> inner join prj_tasks t_req on t_req.task_id =
> prj_task_dependencies.dependencies_req_task_id
> 
> and t_req.task_status = 0
> 
> inner join prj_tasks t_dep on t_dep.task_id =
> prj_task_dependencies.dependencies_task_id
> 
> and t_dep.task_status = 0
> 
> where prj_task_dependencies.dependencies_task_id =
> sucessoras.task_id
> 
> and t.task_id = sucessoras.task_id

to

from prj_task_dependencies
inner join sucessoras on prj_task_dependencies.dependencies_task_id = 
sucessoras.task_id
inner join prj_tasks t on t.task_id = sucessoras.task_id
inner join prj_tasks t_req on t_req.task_id = 
prj_task_dependencies.dependencies_req_task_id and t_req.task_status = 0
inner join inner join prj_tasks t_dep on t_dep.task_id = 
prj_task_dependencies.dependencies_task_id and t_dep.task_status = 0

Alternatively, you could make all these joins implicit:

from prj_task_dependencies , sucessoras , prj_tasks t, prj_tasks t_req, 
prj_tasks t_dep
where prj_task_dependencies.dependencies_task_id = sucessoras.task_id
and t.task_id = sucessoras.task_id
and t_req.task_id = prj_task_dependencies.dependencies_req_task_id and 
t_req.task_status = 0
and t_dep.task_id = prj_task_dependencies.dependencies_task_id and 
t_dep.task_status = 0

But personally I find that extremely hard to read, and really prefer the 
SQL-92 explicit joins.

Mark

On 2018-02-01 17:25, Rudi Feijó rudi.fe...@multidadosti.com.br 
[firebird-support] wrote:
> Good afternoon.
> I’ve been assigned to debug a query that was working on 2.5 but stopped
> workin on 3.0.
> 
> 
> 
> As of now I have little information on the context of where this query 
> is
> used, but I’m assuming it might be something simple.
> 
> 
> 
> Apparently what’s causing the error is using this FROM sintax with 
> multiple
> tables separated by comma (FROM tablea, tableb, tablec).
> 
> I’m assuming that because the error is always thrown on the line 
> subsequent
> to the “from” line
> 
> 
> Was there any change to this type of from syntax in firebird 3.0?
> Below is the query. If needed I can create a test gdb with data.
> 
> Thanks in advance
> 
> 
> 
> 
> 
> 
> 
> with recursive sucessoras (task_id) as
> 
> (
> 
> select prj_task_dependencies.dependencies_task_id as 
> task_id
> 
> from prj_task_dependencies
> 
> inner join prj_tasks t_req on t_req.task_id =
> prj_task_dependencies.dependencies_req_task_id
> 
> and t_req.task_status = 0
> 
> inner join prj_tasks t_dep on t_dep.task_id =
> prj_task_dependencies.dependencies_task_id
> 
> and t_dep.task_status = 0
> 
> where prj_task_dependencies.dependencies_req_task_id = 98
> 
> union all
> 
> select
> 
> prj_task_dependencies.dependencies_task_id as task_id
> 
> from prj_task_dependencies, sucessoras
> 
> inner join prj_tasks t_req on t_req.task_id =
> prj_task_dependencies.dependencies_req_task_id
> 
> and t_req.task_status = 0
> 
> inner join prj_tasks t_dep on t_dep.task_id =
> prj_task_dependencies.dependencies_task_id
> 
> and t_dep.task_status = 0
> 
> where prj_task_dependencies.dependencies_req_task_id =
> sucessoras.task_id
> 
> )
> 
> 
> 
> select
> 
> 
> 
> distinct(sucessoras.task_id) ,
> 
> t.idbpo_proc,
> 
> t.task_name,
> 
> t.task_project as idclienteprojeto,
> 
> t.task_constraint,
> 
> t.task_constraint_date,
> 
> t.TASK_BASELINE_DURATION as THIS_TASK_BASELINE_DURATION,
> 
> t.TASK_BASELINE_DURATION_D as THIS_TASK_BASELINE_DURATION_D,
> 
> t.TASK_DURATION_TYPE as THIS_TASK_DURATION_TYPE,
> 
> t.task_baseline_start as THIS_TASK_BASELINE_START,
> 
> t.task_baseline_end as THIS_TASK_BASELINE_END,
> 
> 
> 
> cast(t.task_baseline_start as time) as
> THIS_TIME_TASK_BASELINE_START,
> 
> cast(t.task_baseline_end as time) as 
> THIS_TIME_TASK_BASELINE_END,
> 
> 
> 
> t.recorrencia_semanal_domingo,
> 
> t.recorrencia_semanal_segunda,
> 
> t.recorrencia_semanal_terca,
> 
> t.recorrencia_semanal_quarta,
> 
> t.recorrencia_semanal_quinta,
> 
> t.recorrencia_sem

RE: [firebird-support] CTE query works on fb 2.5 but not on fb 3.0

2018-02-01 Thread Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] 
Thanks a lot Mark !

>Your problem has nothing to do with CTEs, you are combining implicit 
> (SQL-89) joins and explicit (SQL-92) joins, and referencing the 
>implicitly joined table from the explicitly joined tables. The rules for 
>these have changed in Firebird 3 to be compliant with the SQL 
>specification and to avoid hard to diagnose bugs.