[firebird-support] It a CTE possible here?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

the following stored Procedure calls a second one and I get for each product
some parts where included. Now, I group this at all, but some products uses
the same parts and I would group it in this function, without calling with
another one which groups it. It is possible?

 

create or alter procedure P_GETTEILEMATPULPS (

PSJAHR integer,

PSKW integer)

returns (

MATNR varchar(16),

ANZ double precision,

EINHEIT integer)

AS

declare variable teil varchar(16);

declare variable menge double precision;

BEGIN

  for select teilenr, sum(stck_je_tag) from tplan_kw_pos where ltjahr =
:psjahr and ltkw = :pskw

  and abgearbeitet = 0

  group by teilenr

  into :teil, :menge do

  begin

if(menge is null) then menge = 0;

for select materialnr, sum(anzahlm) as anzahlm, einheit from
P_GETTEILEMATps_pul(:teil, :menge)

group by materialnr, einheit

into :matnr, :anz, :einheit do suspend;

  end

END

 

Thank you.

 

Best regards

 

Olaf

 

AW: [firebird-support] SQL Error 303

['Check_Mail' check_m...@satron.de [firebird-support]]

The name of the function is isc_expand_dpb

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 27. Mai 2020 12:39
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] SQL Error 303

 

  

Sorry, one thing. The firebird.dll from version 2 is working, the from fb3
not. Can anyone tell me what is different in this case from 2 to 3?

 

Von: firebird-support@yahoogroups.com
  mailto:firebird-support@yahoogroups.com> > 
Gesendet: Mittwoch, 27. Mai 2020 11:56
An: firebird-support@yahoogroups.com
 
Betreff: [firebird-support] SQL Error 303

 

  

Hello,

 

we have some problems since migration VC++ 2005 to VC++ 2016. The same code,
the same fbclient.dll but now we get the error after connect:

 

Dynamic SQL Error

 

SQL error code = -303

 

Implementation of text subtype 205 not located.

 

What could it be? The charset is ISO8859_1, Firebird 3.0 superserver, no
UDFs.

 

Thank you. Best regards.

 

Olaf

 

 

AW: [firebird-support] SQL Error 303

['Check_Mail' check_m...@satron.de [firebird-support]]

Sorry, one thing. The firebird.dll from version 2 is working, the from fb3
not. Can anyone tell me what is different in this case from 2 to 3?

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 27. Mai 2020 11:56
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] SQL Error 303

 

  

Hello,

 

we have some problems since migration VC++ 2005 to VC++ 2016. The same code,
the same fbclient.dll but now we get the error after connect:

 

Dynamic SQL Error

 

SQL error code = -303

 

Implementation of text subtype 205 not located.

 

What could it be? The charset is ISO8859_1, Firebird 3.0 superserver, no
UDFs.

 

Thank you. Best regards.

 

Olaf

 

 

[firebird-support] SQL Error 303

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

we have some problems since migration VC++ 2005 to VC++ 2016. The same code,
the same fbclient.dll but now we get the error after connect:

 

Dynamic SQL Error

 

SQL error code = -303

 

Implementation of text subtype 205 not located.

 

What could it be? The charset is ISO8859_1, Firebird 3.0 superserver, no
UDFs.

 

Thank you. Best regards.

 

Olaf

 

 

AW: [firebird-support] Re: Round the Time

['Check_Mail' check_m...@satron.de [firebird-support]]

Thank you


-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Donnerstag, 5. Dezember 2019 18:34
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Re: Round the Time

'Check_Mail' check_m...@satron.de [firebird-support] a écrit :
> Dateadd is an option or extrac t the complete time in hours and 
> minutes, but can I do this more simple?

for fun

cast(overlay( cast(cast('now' as timestamp) as char(24)) placing '00:'
from 18 for 7) as timestamp )

--
Norbert Saint Georges
http://tetrasys.fi







++

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

[firebird-support] Round the Time

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

is there a simply function available to round the time to entire minutes?
18:15:45 can be 18:15:00 to calculate full minutes between begin and end.

 

Dateadd is an option or extract the complete time in hours and minutes, but
can I do this more simple?

 

Thank you.

 

Regards

 

Olaf

 

 

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

['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

 

 

AW: [firebird-support] CTE difficult question

['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.

 

 

 

AW: [firebird-support] CTE difficult question

['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.

 

 

 

[firebird-support] CTE difficult question

['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.

 

 

 

[firebird-support] Linked Table and double precision

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

I use Microsoft Access as frontend, firebird 3 superserver as backand. Now,
there a many linked tables in access over firebird odbc, some of the colums
are in double precision format.

 

Now, I don't know why, in a Report I calculate the VAT, in my case:

 

279.578,50 * 0,19

 

If I calculate it wit a calculator, I get 53.119,915

 

The frontend calculates me 53.119,143 and in result, the final sum is wrong.
915 should 92, but 914 will be 91.

 

Thanks for your Help.

 

Regards

 

Olaf

 

 

AW: [firebird-support] 32 Bit and 64 Bit

['Check_Mail' check_m...@satron.de [firebird-support]]

Thank you, Dimitry,

it sounds logical.

Best regards.

Olaf

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 6. September 2019 16:18
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] 32 Bit and 64 Bit

06.09.2019 15:35, 'Check_Mail' check_m...@satron.de [firebird-support]
wrote:
> Can I use the 64 Bit Client-Installation of Firebird for the clients 
> or should I take the 32-Bit firebird installer to install the minimum
client installation?

   Applications cannot use libraries of different bitness. If you use 32
bits application they need 32 bits Firebird client. If you use 64 bits
applications they need 64 bits Firebird client. If you use both, you must
install both Firebird clients.


-- 
   WBR, SD.






++

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

[firebird-support] 32 Bit and 64 Bit

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

I would like to install a 64 Bit Server OS with 64 Bit Firebird Superserver.
We are using 32 Bit Office Access with 32 Bit Firebird ODBC. 

 

Can I use the 64 Bit Client-Installation of Firebird for the clients or
should I take the 32-Bit firebird installer to install the minimum client
installation?

 

Thank you.

 

 

AW: [firebird-support] CpuAffinityMask on firebird 3.X and firebird memory leakage

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Dmitry,

but the UDFs I haven’t change in. How can I delimit this leak? Can I debug
the memory?


-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Dienstag, 3. September 2019 13:17
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] CpuAffinityMask on firebird 3.X and firebird
memory leakage

03.09.2019 13:12, 'Check_Mail' check_m...@satron.de [firebird-support]
wrote:
> How can I understand this gap?

   The biggest probability is a memory leak in your UDFs.


-- 
   WBR, SD.






++

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] CpuAffinityMask on firebird 3.X and firebird memory leakage

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Dimitry,

Thank you.

I have checked the monitoring table, the sum of active memory usage is
160MB, the max usage around 290MB. But the firebird process takes up ca.
730MB. How can I understand this gap? Also the memory usage of the
firebird.exe increases over the time till the max of 32Bit (2GB). This
problem occurred only recently with no major changes in our database. Maybe
windowsupdates have been a part of the problem? The clients uses firebird
ODBC (MS Access) and some clients are written in C.

Thanks a lot.

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Dienstag, 3. September 2019 12:28
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] CpuAffinityMask on firebird 3.X and firebird
memory leakage

03.09.2019 12:10, 'Check_Mail' check_m...@satron.de [firebird-support]
wrote:
> if we use the default firebird.conf with #CpuAffinityMask = 0, does 
> firebird use all cpus/cores? Or should I set it to 15 for 4 cores? I 
> have found some information for the
> 2.5 only.

   For Firbird 3 CPU affinity has no point.

> Does upgrading to 3.04 fix that problem or should we use the classic
server? In the future, we should install a 64 Bit Windows, but the existing
udf-files do not work with this architecture. 

   You must detect where problem is before one can tell for sure whether it
will be fixed or not. Start from inspecting MON$MEMORY_USAGE.
   In any case 3.0.4 has a lot of bugfixes so it is better to upgrade even
if it cannot solve your memory problem.


-- 
   WBR, SD.






++

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

[firebird-support] CpuAffinityMask on firebird 3.X and firebird memory leakage

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

if we use the default firebird.conf with #CpuAffinityMask = 0, does firebird
use all cpus/cores? Or should I set it to 15 for 4 cores? I have found some
information for the 2.5 only. 

 

We have some memory-problems on firebird 3.0.1 32 Bit Superserver on server
2008 32 Bit, the one process increases to 2GB in RAM and then the server
accepts no new connections. Does upgrading to 3.04 fix that problem or
should we use the classic server? In the future, we should install a 64 Bit
Windows, but the existing udf-files do not work with this architecture. 

 

Another question, we would like to update an old client to 64Bit
architecture in C Visual Studio 2005, and therefore we need instead of the
ib_util the ib_util64 libraries. The ib_util_malloc were not found during
compiling.

 

Thank you.

 

Regards

 

Olaf

 

 

AW: AW: [firebird-support] Converting with parameters stored in variables?

['Check_Mail' check_m...@satron.de [firebird-support]]

Helle Karol,

 

thank you, now it works and it is more easier.

 

I have modified this:

 

SELECT lpad(cast(TRUNC(x.a) as integer), :i_anz, '0') || '.' || 
lpad((X.A-TRUNC(X.A))*X.B, 3, '0') FROM (SELECT cast(:z_str as double 
precision) AS A, POWER(10,:i_anz2) AS B FROM RDB$DATABASE) X

 

Without cast as integer I get the 100 to the left (100.300) (11.3), 
without casting as double precision, I get an error, cannot work with strings.

 

Thanks again 

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Samstag, 31. August 2019 09:20
An: firebird-support@yahoogroups.com
Betreff: ODP: AW: [firebird-support] Converting with parameters stored in 
variables?

 

  

>>Trunc instead of pow?

 

I only mean that you do not need to operate on strings to separate number.

 

A = 123.45 – numeric not a double

A1 = TRUNC(A)

A2 = (A-A1)*POWER(10,3)

 

Then 

A1 = 123

A2 = 450

 

SELECT LPAD(TRUNC(X.A), 10, '0') || '.' || LPAD((X.A-TRUNC(X.A))*X.B, 3, '0'), 
FROM (SELECT 100.12 AS A, POWER(10,3) AS B FROM RDB$DATABASE) X

 

Regards,

Karol Bieniaszewski

AW: [firebird-support] Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

Oh, Alan,

 

I’m using the extract-function. What do you use?

 

select extract(weekday from current_date) from rdb$database – result is 5.

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 30. August 2019 09:01
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] Array datatype?

 

  

 

Hello Alan,

 

Im using now the extract function an there is 0 = Sunday, 1 = Monday I think.

 

 

It’s Friday here today:

select f_dayofweek(current_timestamp) from rdb$database

 

returns 6

AW: AW: [firebird-support] Converting with parameters stored in variables?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hallo Karol,

 

we have a rfc interface with a high bay storage an the telegram defines the 
different datatypes. If I would create a new material, I must set different 
parameters #name#weight…#111.111# and so on.

 

Trunc instead of pow?

 

Regards

 

Olaf 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 30. August 2019 10:04
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] Converting with parameters stored in 
variables?

 

  

Hi,

 

You can do this simpler by using trunc.

 

But i ask why do you need this format in resultset? Formatting numbers is a 
client side task, same as for DATE.

 

 

Regards,

Karol Bieniaszewski

AW: [firebird-support] Converting with parameters stored in variables?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Karol,

 

yes, I know padding. In my case I would like to set the numeric parameters with 
two variables first.

 

*   Take the R11.3 into two variables f1 and f2 (f1 = 11, f2 = 3)
*   Set the numeric(:f1,:f2) without execute statements

 

Now I have it realized as following:

 

  c_anz = substring(t_str from 2 for (position( '.' in t_str)-2)); -- precision 
(t_str is ’11.3’ a string)

  select value_int from p_u_isnumeric(:c_anz) into :i_anz; -- check for integer 
and convert into an int

  c_anz2 = substring(t_str from (position('.' in t_str)+1) for 5); -- scale

  select value_int from p_u_isnumeric(:c_anz2) into :i_anz2;

  tmp_int = cast(z_str as double precision) * power(10,i_anz2); -- set the 
value, round it –z_str = double precision, the value to convert into a string 
(2.4 into 002.400)

  tmp_str =  lpad(cast(tmp_int as varchar(15)),i_anz + i_anz2,'0');

  n_str = o_str || left(tmp_str,i_anz) || '.' || right(tmp_str,i_anz2) || '#'; 
-- set the decimalpoint

  suspend;

 

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Donnerstag, 29. August 2019 16:55
An: firebird-support@yahoogroups.com
Betreff: ODP: [firebird-support] Converting with parameters stored in variables?

 

  

Hi

 

This is called padding.

Simple example (but not what you want you must padd also decimal point)

SELECT LPAD(CAST(100 AS NUMERIC(10,3)), 14, '0') FROM RDB$DATABASE

 

But you must tell us what is your real problem, as you need padding for what?

 

Pozdrawiam,

Karol Bieniaszewski

AW: [firebird-support] Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Alan,

 

Im using now the extract function an there is 0 = Sunday, 1 = Monday I think.

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Donnerstag, 29. August 2019 09:23
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] Array datatype?

 

  

Hello Kjell,

 

then I have to do the mapping between Monday = 1, Tuesday = 2, Sunday = 0. But 
I can only select the value with an execute statement.

 

I will test it, thank you. 

 

WHEN F_DAYOFWEEK(J.COMMDATE)=1 /* Sunday */

??

 

Alan McDonald

 

 

 

AW: [firebird-support] Re: Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Vlad,

 

thank you, I will revise my concept.

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Donnerstag, 29. August 2019 09:26
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Re: Array datatype?

 

  

 

  Use CASE:

 

azeit = azeit + 

  CASE EXTRACT(WEEKDAY FROM :akttag)

WHEN 0 THEN F0

WHEN 1 THEN F1



WHEN 6 THEN F6

  END;

 

Regards,

Vlad

 

PS procedure contains logical errors, hope it is just a quick sample

 

 

---In firebird-support@yahoogroups.com 
 , mailto:check_mail@...> > wrote :

In my case, I would like to do this:

 

create procedure P_ARBZEIT(

beginn date,

ende date,

f1 float,

f2 float,

f3 float,

f4 float,

f5 float,

f6 float,

f0 float)

returns (

azeit double precision)

as

declare variable wt integer;

declare variable akttag date;

begin

azeit = 0;

akttag = beginn;

while (akttag = ende or beginn > ende) do

begin

wt = extract(weekday from :akttag);

azeit = azeit + cast('f'||wt as char(2));

akttag = akttag + 1;

end

 

 

  suspend;

end

 

f1 = Monday, f2 = Tuesday.. f0 = Sunday.

 

I get the day from date with weekday and I would add the value from the day to 
the azeit. Take the value from the variable f0 when it is Sunday.

 

[firebird-support] Converting with parameters stored in variables?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

I get from a application the following

 

100

R10.3

Or 

22.22

R10.3

 

Now I should take it into:

 

000100.000

And

22.220

 

I think, I cannot simply (cast :input as :vartype) (vartype =
'decimal(10.3)'

Also it will fortunately not work: cast(input as decimal(:v1,:v2) (v1 = 10
v2 = 3).

 

How can I realize it without dismantle the 10.3 and the 22.22?

 

Thank you.

 

 

AW: [firebird-support] Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Kjell,

 

then I have to do the mapping between Monday = 1, Tuesday = 2, Sunday = 0. But 
I can only select the value with an execute statement.

 

I will test it, thank you. 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Donnerstag, 29. August 2019 07:46
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Array datatype?

 

  

Den 2019-08-29 kl. 07:17, skrev 'Check_Mail' check_m...@satron.de 
  
[firebird-support]: 
> 
> Okay, 
> 
> unfortunately there is no coherence between the weekday 0-6 and my 
> table-Field Montag, Dienstag etc. (developed historically) and the execute 
> statement works not too (not with "variable = variable + variable" and not 
> with "select variable + variableb from rdb$database" 
> 
> Thank you, Dimitry! 
> 
You should probably consider a lookup table, i.e. whatever you would 
like to put in an array, put it in a separate table instead, one record 
for each array entry. 

If the "array" is different for each execution of the procedure, 
consider using a global temporary table, GTT. You can find more on this 
page (about 1/4 down): 
https://firebirdsql.org/rlsnotesh/rnfb210-ddl.html 

Regards, 
Kjell 


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

AW: AW: [firebird-support] memory bug?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Mark,

 

actually, we use the superserver with this settings. In the past, we had the 
classic server, because only it could access to some more cpu cores. 

 

I thought, when we go back to the classic server, the problem with the 2gb RAM 
overflow is historical, because each of the connection has then an separate 
memory reservation an this should not exceeded the 2gb. I don’t know, why the 
superserver despite the limitation increases to more than 2gb of ram.

 

Thank you..

 

Best regards.

 

Olaf

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 28. August 2019 18:02
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] memory bug?

 

  

On 28-8-2019 09:27, 'Check_Mail' check_m...@satron.de 
  [firebird-support] 
wrote:
> in our production environment I cannot simply install every update, it 
> is an 24-hour-running system, I dont like to „touch“ i fit is not 
> absolutely necessary. Okay, I will update the server and all clients soon..

The update frequency of Firebird isn't really high and I must assume you 
need to plan for downtime anyway for OS updates etc. Firebird 3.0.1 is 
almost 3 years old now (even 3.0.4 is almost one year old by now). 
Upgrading Firebird 3.0.1 to a higher 3.0 version is a drop in 
replacement (no backup and restore is necessary).

Although keeping the client libraries up-to-date is advisable, updating 
the server doesn't require updating your clients at the same time. Older 
Firebird clients (eg from 2.5 or 2.1, in theory even 1.0 although I 
haven't verified that) can also still connect, but you'll need to 
disable wire encryption and make sure that user is created with legacy_auth...

> The defaultdbcachepagesize I have n ot set in this case, I have learned, 
> that is it necessary by using classing Server (set to 75). Or should I 
> change this netzertheless?

Your previous message said you were using SuperServer. Are you using 
ClassicServer instead? That difference is rather important for your problem...

[..]
> Page Size 4096
[..]
> Page buffers 65536
[..]

With SuperServer this is probably fine (256 megabytes per database), 
with ClassicServer or SuperClassic, this requires 256 megabytes per 
connection.

[..]

> Perhaps it is better to use the classic server, it uses for each 
> connection some ram and a own process.

If you are using SuperServer right now, and you want to switch to 
ClassicServer, you should change the page buffer settings of your database.

Mark

-- 
Mark Rotteveel

AW: [firebird-support] Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

Okay, 

unfortunately there is no coherence between the weekday 0-6 and my
table-Field Montag, Dienstag etc. (developed historically) and the execute
statement works not too (not with "variable = variable + variable" and not
with "select variable + variableb from rdb$database"

Thank you, Dimitry!

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 28. August 2019 17:31
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Array datatype?

28.08.2019 16:18, 'Check_Mail' check_m...@satron.de [firebird-support]
wrote:
> is there no way? I know the thing with the execute statement, but it 
> is not the best solution. I would like to add a value, if the weekday 
> is 1 (Monday), then I would like to take the variable t1 and add the value
from t1 to the sum..

   Yes, there is no way. Reconsider your task to more SQL-is way or use dumb
solution with CASE, but arrays don't exist in PSQL.


-- 
   WBR, SD.






++

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] Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

...statement does not works, local variables does the statement not found..

 

create or alter procedure P_ARBZEIT (

BEGINN date,

ENDE date,

F1 float,

F2 float,

F3 float,

F4 float,

F5 float,

F6 float,

F0 float)

returns (

AZEIT float)

AS

declare variable wt integer;

declare variable akttag date;

declare variable stmtxt varchar(100);

begin

azeit = 0;

akttag = beginn;

if(beginn > ende) then exit;

while (akttag <= ende) do

begin

wt = extract(weekday from :akttag);

stmtxt = 'select cast(:azeit + :f' || cast(wt as char(1)) || '  as float)
from rdb$database' ;

execute statement stmtxt into :azeit;

akttag = akttag + 1;

end

 

 

  suspend;

end

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 28. August 2019 16:18
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] Array datatype?

 

  

Hello Dimitry,

 

is there no way? I know the thing with the execute statement, but it is not
the best solution. I would like to add a value, if the weekday is 1
(Monday), then I would like to take the variable t1 and add the value from
t1 to the sum..

 

 

Von: firebird-support@yahoogroups.com
  mailto:firebird-support@yahoogroups.com> > 
Gesendet: Mittwoch, 28. August 2019 15:48
An: firebird-support@yahoogroups.com
 
Betreff: AW: [firebird-support] Array datatype?

 

  

In my case, I would like to do this:

 

create procedure P_ARBZEIT(

beginn date,

ende date,

f1 float,

f2 float,

f3 float,

f4 float,

f5 float,

f6 float,

f0 float)

returns (

azeit double precision)

as

declare variable wt integer;

declare variable akttag date;

begin

azeit = 0;

akttag = beginn;

while (akttag = ende or beginn > ende) do

begin

wt = extract(weekday from :akttag);

azeit = azeit + cast('f'||wt as char(2));

akttag = akttag + 1;

end

 

 

  suspend;

end

 

f1 = Monday, f2 = Tuesday.. f0 = Sunday.

 

I get the day from date with weekday and I would add the value from the day
to the azeit. Take the value from the variable f0 when it is Sunday.

 

My question about the array, because I can set if it is possible
(array[0,8,8,8,8,8,0] and array[weekday])

 

Von: firebird-support@yahoogroups.com
  mailto:firebird-support@yahoogroups.com> > 
Gesendet: Mittwoch, 28. August 2019 15:29
An: firebird-support@yahoogroups.com
 
Betreff: [firebird-support] Array datatype?

 

  

Hello,

 

I have test something like this: 

 

Declare vat integer[7] or {7}.

 

How can I define an array datatype and use it in some functions?
(vat[1]/vat[countervail])

 

Thank you.

 

 

AW: [firebird-support] Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Dimitry,

 

is there no way? I know the thing with the execute statement, but it is not
the best solution. I would like to add a value, if the weekday is 1
(Monday), then I would like to take the variable t1 and add the value from
t1 to the sum..

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 28. August 2019 15:48
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] Array datatype?

 

  

In my case, I would like to do this:

 

create procedure P_ARBZEIT(

beginn date,

ende date,

f1 float,

f2 float,

f3 float,

f4 float,

f5 float,

f6 float,

f0 float)

returns (

azeit double precision)

as

declare variable wt integer;

declare variable akttag date;

begin

azeit = 0;

akttag = beginn;

while (akttag = ende or beginn > ende) do

begin

wt = extract(weekday from :akttag);

azeit = azeit + cast('f'||wt as char(2));

akttag = akttag + 1;

end

 

 

  suspend;

end

 

f1 = Monday, f2 = Tuesday.. f0 = Sunday.

 

I get the day from date with weekday and I would add the value from the day
to the azeit. Take the value from the variable f0 when it is Sunday.

 

My question about the array, because I can set if it is possible
(array[0,8,8,8,8,8,0] and array[weekday])

 

Von: firebird-support@yahoogroups.com
  mailto:firebird-support@yahoogroups.com> > 
Gesendet: Mittwoch, 28. August 2019 15:29
An: firebird-support@yahoogroups.com
 
Betreff: [firebird-support] Array datatype?

 

  

Hello,

 

I have test something like this: 

 

Declare vat integer[7] or {7}.

 

How can I define an array datatype and use it in some functions?
(vat[1]/vat[countervail])

 

Thank you.

 

 

AW: [firebird-support] Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

In my case, I would like to do this:

 

create procedure P_ARBZEIT(

beginn date,

ende date,

f1 float,

f2 float,

f3 float,

f4 float,

f5 float,

f6 float,

f0 float)

returns (

azeit double precision)

as

declare variable wt integer;

declare variable akttag date;

begin

azeit = 0;

akttag = beginn;

while (akttag = ende or beginn > ende) do

begin

wt = extract(weekday from :akttag);

azeit = azeit + cast('f'||wt as char(2));

akttag = akttag + 1;

end

 

 

  suspend;

end

 

f1 = Monday, f2 = Tuesday.. f0 = Sunday.

 

I get the day from date with weekday and I would add the value from the day
to the azeit. Take the value from the variable f0 when it is Sunday.

 

My question about the array, because I can set if it is possible
(array[0,8,8,8,8,8,0] and array[weekday])

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 28. August 2019 15:29
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Array datatype?

 

  

Hello,

 

I have test something like this: 

 

Declare vat integer[7] or {7}.

 

How can I define an array datatype and use it in some functions?
(vat[1]/vat[countervail])

 

Thank you.

 

 

[firebird-support] Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

I have test something like this: 

 

Declare vat integer[7] or {7}.

 

How can I define an array datatype and use it in some functions?
(vat[1]/vat[countervail])

 

Thank you.

 

 

AW: AW: [firebird-support] memory bug?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Karol,

 

first, thank your for your effort.

 

There are zwo databases running on our system, the second one has no open 
transactions, another transactions-model and only just one software were 
connect tot he server.

 

Can the update to the last firebird-version solve this? We had two times now 
this issue, without changing some configurations. Perhaps, the last 
windowsupdates or office updates changes some situations and now this works not 
perfektly. 

 

The transactions-gap is normal, because we uses firebird odbc with ms access 
and the this transactions I cannot affect. All the other clients works fine in 
relation tot he transactions.

 

The storage should be okay, its a vmware one from the newest generation, sas 
hdds, very fast. 

 

The Server OS shows me no warnings or errors. How can I see the content of the 
firebird process? Can I see based on the table stats some indicators, why my 
datbase needs so much memory.

 

If I update to a 64 Bit System, perhaps I dont have this problem anymore. 

 

Thank you.

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 28. August 2019 10:16
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] memory bug?

 

  

 

Hi

 

You have buffers set inside database. Your current setting is 256MB ( 

65536×4096) so if this is your only database then Firebird should not eat whole 
memory. But this depend on your sort buffer also.

 

You should look at your system and monitor hardware especially HDD wait.

Also look at transaction management, as you have ~4k transactions gap between 
oldest active and next one.

 

Regards,

Karol Bieniaszewski

AW: [firebird-support] memory bug?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Carol and Mark,

 

in our production environment I cannot simply install every update, it is an
24-hour-running system, I dont like to "touch" i fit is not absolutely
necessary. Okay, I will update the server and all clients soon.

 

The defaultdbcachepagesize I have not set in this case, I have learned, that
is it necessary by using classing Server (set to 75). Or should I change
this netzertheless?

 

Flags   0

Generation  29993102

System Change Number  0

Page Size 4096

Oldest Transaction 29988285

Oldest active29988286

Oldest snapshot29981373

Next transaction   29992797

Sequence number 0

Next att. Id  304929

Implementation HW = Intel/i386 little endian os Windows CC MSVC

Shadow count 0

Page buffers 65536

Next header page 0

Dialect3

Create date mar 18, 2019 12:39:33

Attributes  force write

 

Variable header data:

Sweep interval 0

 

I think, the gap between oldest and next transaction is normal in our case
(odbc).

 

The sweep will be set every night by windows task.

 

I have check it again, not the oldest is 29.995.544 and the next 30.007.437

 

I've been thinking about the update to a 64 Bit operating system and the 64
Bit Version of firebird (no limitation with 2gb of ram), but some of the
applications cannot run on 64 Bit, too old. Although I could run it on a
other pc/vmware, but the udfs also runs not on 64 Bit Firebird, freeadhocudf
and a self programmed one, very old, without quellcode. Allthough I can
change the stored procedures and triggers (replace the udf-functionality
with the new given by firebird) , but in my case, I have a lot of work, 200+
stored procedures and many more triggers.

 

Perhaps it is better to use the classic server, it uses for each connection
some ram and a own process.

 

Thank you.

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Dienstag, 27. August 2019 19:52
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] memory bug?

 

  

On 2019-08-27 14:31, 'Check_Mail' check_m...@satron.de
  
[firebird-support] wrote:
> we are using firebird 3.0.1 Superserver 32 Bit on a Windows Server
> 2008 32 Bit. Currently we have all 60 days the problem, that our
> Applications works not well, the firebird-process uses almost 2GB of
> RAM and this is seemingly the limit of an 32 Bit process. In this
> case, I cannot connect with my tool to see the monitoring tables, no
> new connection can be established.

First, upgrade to 3.0.4 to exclude the possibility that this is caused 

 


by bugs that were fixed 3.0.2, 3.0.3 or 3.0.4.

Also tell use:

- the page size of your database
- the DefaultDbCachePages setting from firebird.conf
- the output of gstat -h on your database

Mark

[firebird-support] memory bug?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

we are using firebird 3.0.1 Superserver 32 Bit on a Windows Server 2008 32
Bit. Currently we have all 60 days the problem, that our Applications works
not well, the firebird-process uses almost 2GB of RAM and this is seemingly
the limit of an 32 Bit process. In this case, I cannot connect with my tool
to see the monitoring tables, no new connection can be established. 

 

 

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

['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 
  [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> > 
Gesendet: Montag, 12. August 2019 12:17
An: firebird-support@yahoogroups.com  
; 'Check_Mail' 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

 

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

['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

[firebird-support] CTE Spaces between every day

['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.

 

 

 

AW: [firebird-support] CTE, tricky request

['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

[firebird-support] CTE, tricky request

['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

AW: [firebird-support] Firebird 64 Bit Superserver

['Check_Mail' check_m...@satron.de [firebird-support]]

Hallo Karol,

 

This dll contains many functions to calculate some results (working hour etc.) 
and the week of year. Last I can get with the extract function, but I don’t 
know if this is also the correct calculation (with ISO..).

 

Is it better to update to 64Bit or to install classic server?

 

Thanks.

 

Regards

Olaf

 

Von: firebird-support@yahoogroups.com  
Gesendet: Dienstag, 11. Juni 2019 10:40
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Firebird 64 Bit Superserver

 

  

Hi

 

>>We are using some Applications, which are not designed for 64 Bit Windows

 

All 32 bit applications should work without problem on 64bit windows

 

>>UDFs, there are not designed for 64 Bit Firebird

 

This is bigger problem. I do not know what your udf functions are, but there 
are now plenty of built in functions in FB3 version which can replace many old 
udf

 

Regards,

Karol Bieniaszewski

[firebird-support] Firebird 64 Bit Superserver

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

we are using the firebird 32 Bit Superserver on Server 2008 32 Bit with
approximately 64 Connections. Last week we had the problem, that the one
process increases to 2 GB in the RAM and some of the clients (Microsoft
Access with Firebird ODBC, both 32 Bit) works not without problems and
firebird wrote: "Operating System Call _beginthreadex failed. Error Code 8"

 

We are using some Applications, which are not designed for 64 Bit Windows
and UDFs, there are not designed for 64 Bit Firebird. In this case, is it
better to use the firebird classic server (with defaultdbcachepages 75) than
the firebird 64 Bit on Windows 64 Bit?

 

Thanks.

 

Best regards.

 

 

AW: [firebird-support] user manager?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Dmitry,

I'm using not the embedded mode, or ibexpert uses it? If I configure "local" 
and use the fbclient.dll, every password works. This is not fine, because on 
the server, every user has access to the database. How can I set the password 
on this way?

I was wondering too, why after the standard installation the legacy_auth is 
activated but not the legacy_usermanager? I can use the sysdba masterkey 
combination but not change the password, until the legacy_usermanager is 
activated.

Thanks again. 

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 12. April 2019 17:14
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] user manager?

12.04.2019 15:50, 'Check_Mail' check_m...@satron.de [firebird-support] wrote:
> ..if I use the fbclient.dll, I can connect with every password, why?

   Because you use embedded mode and password isn't checked.


-- 
   WBR, SD.






++

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] user manager?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Dmitry,

I'm using not the embedded mode, or ibexpert uses it? If I configure "local" 
and use the fbclient.dll, every password works. This is not fine, because on 
the server, every user has access to the database. How can I set the password 
on this way?

I was wondering too, why after the standard installation the legacy_auth is 
activated but not the legacy_usermanager? I can use the sysdba masterkey 
combination but not change the password, until the legacy_usermanager is 
activated.

Thanks again. 

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 12. April 2019 17:14
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] user manager?

12.04.2019 15:50, 'Check_Mail' check_m...@satron.de [firebird-support] wrote:
> ..if I use the fbclient.dll, I can connect with every password, why?

   Because you use embedded mode and password isn't checked.


-- 
   WBR, SD.






++

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: AW: [firebird-support] user manager?

['Check_Mail' check_m...@satron.de [firebird-support]]

 

Thank you, have a nice weekend.

 

Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 12. April 2019 15:17
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] user manager?

 

  

On 12-4-2019 07:27, 'Check_Mail' check_m...@satron.de 
  [firebird-support] 
wrote:
> okay, but how can I change both sysdba-passwords? The one from srp and 
> the other from legacy auth?
> 
> I don’t have change any settings, first, some processes works not 
> normally, after restarting the pc, I could not connect to the server 
> with the new user.

If you currently have AuthServer=Srp,Legacy_Auth (or Legacy_Auth,Srp), 
then you can login with either password for SYSDBA.

To change the password of both, ensure that 
UserManager=Srp,Legacy_UserManager and then use

alter user SYSDBA set password 'new password' using plugin Srp;
alter user SYSDBA set password 'new password' using plugin 
Legacy_UserManager;

Or consider using drop user SYSDBA using plugin Legacy_UserManager to 
only retain the Srp SYSDBA.

Mark
-- 
Mark Rotteveel

AW: [firebird-support] user manager?

['Check_Mail' check_m...@satron.de [firebird-support]]

...if I use the fbclient.dll, I can connect with every password, why?

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 12. April 2019 12:06
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] user manager?

 

  

Hello Dimitry,

now, I have installed firebird completely fresh, all settings are standard,
srp is only active (Usermanager, firebird.conf).

ALTER USER SYSDBA password 'Passw' using plugin srp works, but I can still
connect with the "masterkey" password. 

With " select * from sec$users" I get only the srp-sysdba-user. Some of my
databases show me users, some not. (ibexpert).

Does the standard value have to be changed for client_auth-settings? How can
I denied the access with the masterkey password? And should I set it for all
databases or for each?

Thank you!

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
  mailto:firebird-support@yahoogroups.com> > 
Gesendet: Freitag, 12. April 2019 11:15
An: firebird-support@yahoogroups.com
 
Betreff: Re: [firebird-support] user manager?

12.04.2019 7:27, 'Check_Mail' check_m...@satron.de
  [firebird-support] wrote:
> how can I change both sysdba-passwords? The one from srp and the other
from legacy auth?

"ALTER USER" clause has "USING" part which set user manager to be used.

-- 
WBR, SD.





++

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] user manager?

['Check_Mail' check_m...@satron.de [firebird-support]]

...if I use the fbclient.dll, I can connect with every password, why?

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 12. April 2019 12:06
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] user manager?

 

  

Hello Dimitry,

now, I have installed firebird completely fresh, all settings are standard,
srp is only active (Usermanager, firebird.conf).

ALTER USER SYSDBA password 'Passw' using plugin srp works, but I can still
connect with the "masterkey" password. 

With " select * from sec$users" I get only the srp-sysdba-user. Some of my
databases show me users, some not. (ibexpert).

Does the standard value have to be changed for client_auth-settings? How can
I denied the access with the masterkey password? And should I set it for all
databases or for each?

Thank you!

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
  mailto:firebird-support@yahoogroups.com> > 
Gesendet: Freitag, 12. April 2019 11:15
An: firebird-support@yahoogroups.com
 
Betreff: Re: [firebird-support] user manager?

12.04.2019 7:27, 'Check_Mail' check_m...@satron.de
  [firebird-support] wrote:
> how can I change both sysdba-passwords? The one from srp and the other
from legacy auth?

"ALTER USER" clause has "USING" part which set user manager to be used.

-- 
WBR, SD.





++

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] user manager?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Dimitry,

now, I have installed firebird completely fresh, all settings are standard,
srp is only active (Usermanager, firebird.conf).

ALTER USER SYSDBA password 'Passw' using plugin srp works, but I can still
connect with the "masterkey" password. 

With  " select * from sec$users" I get only the srp-sysdba-user. Some of my
databases show me users, some not. (ibexpert).

Does the standard value have to be changed for client_auth-settings? How can
I denied the access with the masterkey password? And should I set it for all
databases or for each?

Thank you!


-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 12. April 2019 11:15
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] user manager?

12.04.2019 7:27, 'Check_Mail' check_m...@satron.de [firebird-support] wrote:
> how can I change both sysdba-passwords? The one from srp and the other
from legacy auth?


   "ALTER USER" clause has "USING" part which set user manager to be used.


-- 
   WBR, SD.






++

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] user manager?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Dimitry,

now, I have installed firebird completely fresh, all settings are standard,
srp is only active (Usermanager, firebird.conf).

ALTER USER SYSDBA password 'Passw' using plugin srp works, but I can still
connect with the "masterkey" password. 

With  " select * from sec$users" I get only the srp-sysdba-user. Some of my
databases show me users, some not. (ibexpert).

Does the standard value have to be changed for client_auth-settings? How can
I denied the access with the masterkey password? And should I set it for all
databases or for each?

Thank you!


-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Freitag, 12. April 2019 11:15
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] user manager?

12.04.2019 7:27, 'Check_Mail' check_m...@satron.de [firebird-support] wrote:
> how can I change both sysdba-passwords? The one from srp and the other
from legacy auth?


   "ALTER USER" clause has "USING" part which set user manager to be used.


-- 
   WBR, SD.






++

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] user manager?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Mark,

 

okay, but how can I change both sysdba-passwords? The one from srp and the 
other from legacy auth? 

 

I don’t have change any settings, first, some processes works not normally, 
after restarting the pc, I could not connect to the server with the new user. 

 

Best regards.

 

Olaf

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Donnerstag, 11. April 2019 17:22
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] user manager?

 

  

On 11-4-2019 10:44, 'Check_Mail' check_m...@satron.de 
  [firebird-support] 
wrote:
> I’ had a difficult problem.
> 
> A few month before, we are using firebird 2.1. For 6 weeks, I have 
> installed the firebird 3 superserver and add a new user and changed the 
> sysdba-password. All works fine, till yesterday. Some applications could 
> not connect to the firebird database. We have taken a restart, after 
> this, only the old sysdba password works to connect to our database. I 
> have nothing changed! Now, I have tried to add the user again, in 
> ibexpert I have it delete before. I get the following error

It actually sounds like you did make a change to the AuthServer setting 
(from its default of AuthServer=Srp to AuthServer=Legacy_Auth) without 
ever restarting Firebird after making that change. You just forgot about it...

I vaguely recall that enabling legacy authentication made config changes 
after starting Firebird server, which caused them to only become 
effective after restarting Firebird, but I thought that was only in one 
of the beta releases (although I haven't recently used the installer, I 
usually use the zipkits).

If not, then it sounds like someone replaced your security database, 
which could point to a security problem.

> Commit nicht möglich
> 
> Unsuccessful execution caused by system error that does not preclude 
> successful execution of subsequent statements.
> 
> add record error.
> 
> violation of PRIMARY or UNIQUE KEY constraint "INTEG_5" on table "PLG$SRP".
> 
> Problematic key value is ("PLG$USER_NAME" = 'SATRON').

That indicates the user already exists (as a Srp user).

> The same message I get, If I try to change the sysdba password. I have 
> backup the database and restore it, the same. I have deinstalled the 
> server and installed it again, the same. Only the sysdba user with 
> masterkey could connect, not the new user.

By default the users and passwords are stored in a separate security 
database, so backing up and restoring your application database won't 
fix anything.

> We have changed after this the firebird.conf ..authserver.. and add srp 
> to legacy and now it works. But why worked it before and why sets the 
> installer not this flag? I am confused. After I deleted the new user, in 
> the access manager I have seen it strikethrough, after I have add this, 
> I have seen it normally. One time, the database works not at all, I cant 
> show some content, I must restore the database again.

It sounds like you enabled legacy authentication in the installer (or 
manually changed it), because AuthServer = Srp is the default.

> Now, the new user works, but If I change the sysdba passwort with
> 
> ALTER USER SYSDBA
> 
> PASSWORD 'newpassword…'
> 
> The new password works, but why works the masterkey again?

It is hard to tell from your problem description exactly what went 
wrong, but it sounds like you were using Srp just fine, and now you did 
something that disabled Srp and enabled Legacy_Auth.

Firebird 3 introduced authentication plugins: secure remote password 
(Srp) and legacy authentication (Legacy_Auth). Users are created per 
authentication plugin. The default security database contains a SYSDBA 
user with password masterkey for the Legacy_Auth SYSDBA user (and no Srp 
user).

If a SYSDBA account was created for Srp as well, it can have a different 
password than the Legacy_Auth. When you authenticate, plugins will be 
tried until the first successful login (or no more plugins). This means 
that if you have AuthServer = Srp,Legacy_Auth (or AuthServer = 
Legacy_Auth,Srp), then - for a client that supports both - two login 
attempts can be made for SYSDBA: one with Srp and one with Legacy_Auth.

Using `ALTER USER` or `CREATE USER` without explicitly specifying the 
user manager plugin (Srp or Legacy_UserManager) will use the default 
plugin (the first in the UserManager list). So if you use `ALTER USER 
SYSDBA ...` with setting `UserManager = Legacy_UserManager,Srp`, then 
the password of the Legacy_Auth SYSDBA will be changed, but the Srp 
SYSDBA keeps it previous password.

You should prefer to use Srp and drop the Legacy_Auth SYSDBA ASAP, 
because it is simply less safe. This even applies if you need 
Legacy_Auth users for applications that use clients without Srp support.

In any case, you should carefully review the settings for AuthServer and 
UserManager. If you don't need Legacy_Auth, then set it to 
AuthServer=Srp and UserManager=Srp. If 

[firebird-support] user manager?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

I’ had a difficult problem.

 

A few month before, we are using firebird 2.1. For 6 weeks, I have installed
the firebird 3 superserver and add a new user and changed the
sysdba-password. All works fine, till yesterday. Some applications could not
connect to the firebird database. We have taken a restart, after this, only
the old sysdba password works to connect to our database. I have nothing
changed! Now, I have tried to add the user again, in ibexpert I have it
delete before. I get the following error

 

Commit nicht möglich

Unsuccessful execution caused by system error that does not preclude
successful execution of subsequent statements.

add record error.

violation of PRIMARY or UNIQUE KEY constraint "INTEG_5" on table "PLG$SRP".

Problematic key value is ("PLG$USER_NAME" = 'SATRON').

 

The same message I get, If I try to change the sysdba password. I have
backup the database and restore it, the same. I have deinstalled the server
and installed it again, the same. Only the sysdba user with masterkey could
connect, not the new user.

 

We have changed after this the firebird.conf ..authserver.. and add srp to
legacy and now it works. But why worked it before and why sets the installer
not this flag? I am confused. After I deleted the new user, in the access
manager I have seen it strikethrough, after I have add this, I have seen it
normally. One time, the database works not at all, I cant show some content,
I must restore the database again.

 

Now, the new user works, but If I change the sysdba passwort with

 

ALTER USER SYSDBA

PASSWORD 'newpassword…'

 

The new password works, but why works the masterkey again?

 

Thanks for your help.

 

Best regards

 

Olaf

 

 

 

 

 

AW: AW: Re[2]: [firebird-support] Link system tables to trigger

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Karol,

 

perfekt, thank you! 

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Dienstag, 12. März 2019 14:43
An: firebird-support@yahoogroups.com
Betreff: Re: AW: Re[2]: [firebird-support] Link system tables to trigger

 

  

Hi,

 

you can do this by:

 

Select 

* 

from 

mon$attachments a 

where a.mon$attachment_id=CURRENT_CONNECTION

 

 

Regards,

Karol Bieniaszewski

AW: Re[2]: [firebird-support] Link system tables to trigger

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

I thought similar, would call a stored procedure in my application and set the 
current user. 

 

But there was a little bit hope, that I can get it by using monitor tables, the 
attachment-table includes all informations and perhaps there is a reference to 
the statements or transactions..

 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Dienstag, 12. März 2019 11:26
An: firebird-support@yahoogroups.com
Betreff: Re[2]: [firebird-support] Link system tables to trigger

 

  

Olaf

 

If you use same firebird user for all application users, I assume that you have 
some list of users within your database.

Then you can set a variable for connection named i.e. my_current_user, fill it 
from the application and then use it in i.e triggers in the same manner that 
you would use current_user.

 

HTH

Marcin

 

-- Wiadomość oryginalna --

Od: "Svein Erling Tysvær setys...@gmail.com   
[firebird-support]" mailto:firebird-support@yahoogroups.com> >

Do: firebird-support@yahoogroups.com  

Data: 12.03.2019 11:15:54

Temat: Re: [firebird-support] Link system tables to trigger

 

O, I see, Olaf, you want to get the Windows user (or similar) and not the 
Firebird user (since all use the same Firebird user through ODBC). I have no 
clue whether that is possible to do in a trigger or not, but there's hopefully 
someone else on this list that knows the answer.

 

Sorry,

Set

 

Den tir. 12. mar. 2019 kl. 10:48 skrev 'Check_Mail' check_m...@satron..de 
  [firebird-support] 
mailto:firebird-support@yahoogroups.com> >:

 

Hello Set,

 

all users connecting over odbc with the same user, so I cannot separate the 
user, which changed the record. It is possible to get it over the 
transactions-table?

 

Von: firebird-support@yahoogroups.com  
 mailto:firebird-support@yahoogroups.com> > 
Gesendet: Dienstag, 12. März 2019 09:52
An: firebird-support@yahoogroups.com  
Betreff: Re: [firebird-support] Link system tables to trigger

 

  

Isn't current_user 
(https://firebirdsql.org/refdocs/langrefupd21-current_user.html) sufficient?

 

Den tir. 12. mar. 2019 kl. 09:20 skrev 'Check_Mail' check_m...@satron.de 
  [firebird-support] 
mailto:firebird-support@yahoogroups.com> >:

 

Hello,

I would like to log some changes in tables, in germany we must save this for 
dsgvo (customer data).

 

Now, I can do this with trigger, but can I link this changing with the system 
tables, that I could get the user? The mon$attachment shows me all active 
attachments with the user and the ip of the client. Can I link this with the 
changing?

 

Thank you.

 

Best regards

 

Olaf

 

 

 

AW: [firebird-support] Link system tables to trigger

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Set,

 

all users connecting over odbc with the same user, so I cannot separate the 
user, which changed the record. It is possible to get it over the 
transactions-table?

 

Von: firebird-support@yahoogroups.com  
Gesendet: Dienstag, 12. März 2019 09:52
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Link system tables to trigger

 

  

Isn't current_user 
(https://firebirdsql.org/refdocs/langrefupd21-current_user.html) sufficient?

 

Den tir. 12. mar. 2019 kl. 09:20 skrev 'Check_Mail' check_m...@satron.de 
  [firebird-support] 
mailto:firebird-support@yahoogroups.com> >:

 

Hello,

I would like to log some changes in tables, in germany we must save this for 
dsgvo (customer data).

 

Now, I can do this with trigger, but can I link this changing with the system 
tables, that I could get the user? The mon$attachment shows me all active 
attachments with the user and the ip of the client. Can I link this with the 
changing?

 

Thank you.

 

Best regards

 

Olaf

 

 

[firebird-support] Link system tables to trigger

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

I would like to log some changes in tables, in germany we must save this for
dsgvo (customer data).

 

Now, I can do this with trigger, but can I link this changing with the
system tables, that I could get the user? The mon$attachment shows me all
active attachments with the user and the ip of the client. Can I link this
with the changing?

 

Thank you.

 

Best regards

 

Olaf

 

[firebird-support] Formatting currency

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

how can I format a value to Euro with decimal pointers like 123.456,44 Euro
?

 

If I do this: select cast(1112223.444 as decimal(18,2)) from rdb$database -
I get 1.112.223,44, but if I select this and store it in a varchar-variable,
I get 1112223.44, with the point, not comma. 

 

Thank you.

 

Regards

 

Olaf

 

 

AW: [firebird-support] long condition faster?

['Check_Mail' check_m...@satron.de [firebird-support]]

Thank you, Andreas.

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Donnerstag, 3. Januar 2019 14:42
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] long condition faster?

Hi Olaf!

select bon.*
  from (select case when t.price > 100 then 'EXPENSIVE'
   else 'CHEAP'
   end buy_or_not
  from  t) bon
  where bon.buy_or_not = 'CHEAP'

András

From: firebird-support@yahoogroups.com 
Sent: Thursday, January 3, 2019 2:27 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] long condition faster?


Hello,

if I select a value:

.a.standort,
  case when(a.fertigstellung <= :fstichtag) then 4 when ((fertigstellung >=
:fstichtag or fertigstellung is null) and abnahme <= :fstichtag) then 3 end
  from ...

and I would check this in the where-condition, should I write the entire
text again or can I do it shorter?

Example:

Where case... is not null

If I set the Field "as fielda", I cannot do this: "where fielda is not null"

Thanks a lot!

Best regards

Olaf





__ Information from ESET Mail Security, version of virus signature
database 18646 (20190103) __

The message was checked by ESET Mail Security.
http://www.eset.com



__ Information from ESET Mail Security, version of virus signature
database 18646 (20190103) __

The message was checked by ESET Mail Security.
http://www.eset.com



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







++

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

[firebird-support] long condition faster?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

if I select a value:

 

..a.standort,

  case when(a.fertigstellung <= :fstichtag) then 4 when ((fertigstellung >=
:fstichtag or fertigstellung is null) and abnahme <= :fstichtag) then 3 end

  from .

 

and I would check this in the where-condition, should I write the entire
text again or can I do it shorter?

 

Example: 

 

Where case. is not null

 

If I set the Field "as fielda", I cannot do this: "where fielda is not null"

 

Thanks a lot!

 

Best regards

 

Olaf

 

 

AW: [firebird-support] exponential, string to double precision

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello András,

so simple, thank you very much 


-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 28. Februar 2018 07:50
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] exponential, string to double precision

Hi Olaf!

Try this:
select cast('+0.200E+01' as double precision) from rdb$database

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups...com]
Sent: Wednesday, February 28, 2018 7:44 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] exponential, string to double precision


Hello,

is there a simple function to convert a string "+0.200E+01" to a number, 
double precision, in this case to 2.0?

Thank you

Best regards

Olaf



__ Information from ESET Mail Security, version of virus signature 
database 16977 (20180228) __

The message was checked by ESET Mail Security.
http://www.eset.com



__ Information from ESET Mail Security, version of virus signature 
database 16977 (20180228) __

The message was checked by ESET Mail Security.
http://www.eset.com



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







++

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

[firebird-support] exponential, string to double precision

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

is there a simple function to convert a string "+0.200E+01" to a number,
double precision, in this case to 2.0?

 

Thank you

 

Best regards

 

Olaf

[firebird-support] In() Question

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello @ll,

 

I have a configuration table with stations, who can be only one order active
and stations, where it isn't so. The list contains all stations, who only
one order can be active. For example, if there an order in station 2, the
user cannot start an order on station 1, 2 and 3. On stations 4 it isn't so,
on station 5 only station 5 will be checked.

 

Please not with a sql statement.

 

Station, onlyone, list

1 11,2,3

2 11,2,3

3 11,2,3

4 0

5 15

6 0

 

The sql where station in(1,2,3) works, but not if there an variable where
station in(:varname). An cursor message occurs. 

 

How can I solve this? Perhaps per sql where station, the current station is
2 for example (..where station in (select station from table where onlyone
..ends to 1 from 2 to 1 and ..onlyone ends while 1 is ending (station 4)..?

 

Thank you.   

 

 

AW: [firebird-support] crosstab pivot query

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Set,

 

Thank you, exactly, what I’m looking for.

 

Completet and with changed the d1 to tx:

 

with tmpPerson(manr, name) as

(select distinct a.manr, b.name

from tplanung a left join tsachbearbeiter b on a.manr = b.sbnr

where jahr = 2017 and monat = 9 order by b.name),

tmpDays(tag, manr, z) as

(select tag, manr, z

from tplanung

where jahr = 2017 and monat = 9)

select p.name, d1.z "1", d2.z "2", d3.z "3", d4.z "4", d5.z "5", d6.z "6",d7.z 
"7",

d8.z "8", d9.z "9",d10.z "10", d11.z "11", d12.z "12",d13.z "13", d14.z "14", 
d15.z "15",

d16.z "16", d17.z "17", d18.z "18",d19.z "19", d20.z "20", d21.z "21",d22.z 
"22", d23.z "23",

d24.z "24",d25.z "25", d26.z "26", d27.z "27",d28.z "28", d29.z "29", d30.z 
"30", d31.z "31"

from tmpPerson p

left join tmpDays d1 on p.manr = d1.manr and d1.tag = 1

left join tmpDays d2 on p.manr = d2.manr and d2.tag = 2

left join tmpDays d3 on p.manr = d3.manr and d3.tag = 3

left join tmpDays d4 on p.manr = d4.manr and d4.tag = 4

left join tmpDays d5 on p.manr = d5.manr and d5.tag = 5

left join tmpDays d6 on p.manr = d6.manr and d6.tag = 6

left join tmpDays d7 on p.manr = d7.manr and d7.tag = 7

left join tmpDays d8 on p.manr = d8.manr and d8.tag = 8

left join tmpDays d9 on p.manr = d9.manr and d9.tag = 9

left join tmpDays d10 on p.manr = d10.manr and d10.tag = 10

left join tmpDays d11 on p.manr = d11.manr and d11.tag = 11

left join tmpDays d12 on p.manr = d12.manr and d12.tag = 12

left join tmpDays d13 on p.manr = d13.manr and d13.tag = 13

left join tmpDays d14 on p.manr = d14.manr and d14.tag = 14

left join tmpDays d15 on p.manr = d15.manr and d15.tag = 15

left join tmpDays d16 on p.manr = d16.manr and d16.tag = 16

left join tmpDays d17 on p.manr = d17.manr and d17.tag = 17

left join tmpDays d18 on p.manr = d18.manr and d18.tag = 18

left join tmpDays d19 on p.manr = d19.manr and d19.tag = 19

left join tmpDays d20 on p.manr = d20.manr and d20.tag = 20

left join tmpDays d21 on p.manr = d21.manr and d21.tag = 21

left join tmpDays d22 on p.manr = d22.manr and d22.tag = 22

left join tmpDays d23 on p.manr = d23.manr and d23.tag = 23

left join tmpDays d24 on p.manr = d24.manr and d24.tag = 24

left join tmpDays d25 on p.manr = d25.manr and d25.tag = 25

left join tmpDays d26 on p.manr = d26.manr and d26.tag = 26

left join tmpDays d27 on p.manr = d27.manr and d27.tag = 27

left join tmpDays d28 on p.manr = d28.manr and d28.tag = 28

left join tmpDays d29 on p.manr = d29.manr and d29.tag = 29

left join tmpDays d30 on p.manr = d30.manr and d30.tag = 30

left join tmpDays d31 on p.manr = d31.manr and d31.tag = 31

 

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 26. September 2017 10:33
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] crosstab pivot query

 

  

Hi Olaf!

 

Not quite crosstab, but knowing there is a maximum of 31 days in a month, you 
can simulate:

 

with tmpPerson(Person) as 

(select distinct person

 from tableA

 where month = 9),

tmpDays("day", person, sign) as

(select "day", person, sign

 from tableA

 where month = 9)

select p.person, list(d1.sign) day1, list(d2.sign) day2, list(d3.sign) day3, 
... list(d30.sign) day30, list(d31.sign) day31

from tmpPerson p

left join tmpDays d1 on p.person = d1.person and d1."day" = 1

left join tmpDays d2 on p.person = d1.person and d1."day" = 2

left join tmpDays d3 on p.person = d1.person and d1."day" = 3

...

left join tmpDays d30 on p.person = d1.person and d1."day" = 30

left join tmpDays d31 on p.person = d1.person and d1."day" = 31 /*Not 
applicable for September, but I assume you want other months as well and it 
doesn't harm...*/

group by p.person

 

If no person has two records any given day, then you don't need 'list' and 
'group by'.

 

Since SQL isn't very suitable for crosstab (*), I normally prefer to select - 
possibly with grouping - in Firebird and "crosstabbing" in Excel.

 

HTH,

Set

 

(*) My experience is more or less limited to Fb 2.5, but I cannot imagine how 
to prepare a statement with an unknown number of columns, in theory that could 
also mean an unknown number of parameters

 

 

[firebird-support] crosstab pivot query

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

I'm looking for an opportunity to create a crosstab (pivot) query, but I
can't get many examples from google.

 

For example:

 

Table A

Year, month, day, person, sign

2017  9 1  1A

2017  9 2  1B

2017  9 3  1B

2017  9 ..30  1C

2017  9 1  2A

2017  9 ,,30  2   B

 

I woult like to get this:

 

Person day1, day2, day3, day4, day5.day30

1 A   B   B  C

2 A   B

 

Is this possible?

 

Many thanks.

 

Best regards

 

Olaf

 

 

AW: [firebird-support] increment

['Check_Mail' check_m...@satron.de [firebird-support]]

Thanks a lot 


Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 15. Juni 2017 15:42
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] increment

  
It is possible to do this with a complex, but straight UPDATE statement. 
However, my hunch is that the below is simpler:

execute block as
  declare variable i integer;
  declare variable PK ;
  i = 1;
  for select  
  from table
  where year = 2010
  order by month, 
  into :PK do
  begin
  update table
 set "POS" = i
  where  = :PK;
  i = i + 1;
  end
end

HTH,
Set

2017-06-15 15:33 GMT+02:00 'Check_Mail' mailto:check_m...@satron.de 
[firebird-support] :
Hello,

How can I increment consisting records like this:

Year  month   order pos
2010  2 1null
2010  2 1null
2010  2 1null
2010  3 2   null

Pos should be auto increment for all 2010 2 records, null to 1, the second
record null to 2 til 4

Update table set pos = coalesce(pos,0)+1 where year = 2010 and month = 2
works not, all records have the pos=1 value

Thank you

Best regards

Olaf



Posted by: "Check_Mail" 


++

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

[firebird-support] increment

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

How can I increment consisting records like this:

 

Year  month   order pos

2010  2 1null

2010  2 1null

2010  2 1null

2010  3 2   null

 

Pos should be auto increment for all 2010 2 records, null to 1, the second
record null to 2 til 4

 

Update table set pos = coalesce(pos,0)+1 where year = 2010 and month = 2
works not, all records have the pos=1 value

 

Thank you

 

Best regards

 

Olaf

[firebird-support] increment

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

How can I increment consisting records like this:

Year  month   order     pos
2010  2 1    null
2010  2 1    null
2010  2 1    null
2010  3 2   null

Pos should be auto increment for all 2010 2 records, null to 1, the second
record null to 2 til 4

Update table set pos = coalesce(pos,0)+1 where year = 2010 and month = 2
works not, all records have the pos=1 value

Thank you

Best regards

Olaf

[firebird-support] complicated recursion or other option?

['Check_Mail' check_m...@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] complicated recursion or other option?

['Check_Mail' check_m...@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

AW: [firebird-support] join question

['Check_Mail' check_m...@satron.de [firebird-support]]

Helle SET,

 

perfect, many thannks 

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 11. April 2017 13:24
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] join question

 

  

Sorry, answered slightly to quickly:

 

Select a.nrworker, b.valueworkermonth 

from table A 

left join table B on a.nrworker = b.nrworker and b.year = 2017 and b.month = 4

where a.active = 1

 

2017-04-11 13:23 GMT+02:00 Svein Erling Tysvær  >:

Simple, just make sure you only refer to table b in the left join and not in 
the where clause:

 

Select a.nrworker, b.valueworkermonth from table A left join table B on 
a.nrworker = b.nrworker and a.active = 1 and b.year = 2017 and b.month = 4

 

HTH,

Set

 

2017-04-11 12:14 GMT+02:00 'Check_Mail' check_m...@satron.de 
  [firebird-support] 
 >:

 

Hello @ll,

 

I have a small question.

 

Table A

 

NrWorker ineger

Name

Active 0 or 1

…

 

Table B

 

NrWorker integer

Year

Month

ValueWorkerMonth

 

Now I would like to get all NrWorker from Table A with Active 1 and the 
ValueWorkerMonth from the Table B (from the current month)

 

This is like

 

Select a.nrworker, b.valueworkermonth from table A left join table B on 
a.nrworker = b.nrworker where a.active = 1 and b.year = 2017 and b.month = 4

 

Fine, but I would like also all Worker (NrWorker from Table A) where are not 
present in Table B in this month.

 

Table A

NrWorker 12

NrWorker 13 

NrWorker 14

 

Table B

NrWorker 12

Year  2017

Month 4

 

I would like to get not only Worker 12,  rather 13 and 14 too.

 

How can I realize it in Firebird?

 

Thank you

 

 

 

[firebird-support] simply CTE

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

at the moment I get all material of a product with a recoursion, but in some
case, I get it manyfold and not grouped. (it gets all Material of the
material in each level)

How can I create a CTE (grouped)?

create or alter procedure P_GETMATERIALTEST (
TEILENR varchar(16),
ANZAHLT double precision,
I integer,
VTEILENR varchar(16),
VBSNR integer)
returns (
BSNR integer,
MATERIALNR varchar(16),
ANZAHLM double precision,
K integer,
TTEILENR varchar(16),
TBSNR integer)
AS
/**/BEGIN
 materialnr = teilenr;TTEILENR=vteilenr;TBSNR = VBSNR;
anzahlm = anzahlt; k=i+1; suspend;
for select bsnr, matteilenr, anzahl from tmaterial a
left join tteile b on(a.matteilenr = b.teilenr)
where a.teilenr = :teilenr order by a.bsnr
into :bsnr, :materialnr, :anzahlm do
begin anzahlm = anzahlm * anzahlt;vteilenr = teilenr;VBSNR = BSNR;
if (k < 800) then
for select materialnr, anzahlm, k,tteilenr,tbsnr from
P_GETMATERIALTEST(:materialnr, :anzahlm, :k, :vteilenr, :vbsnr)
into :materialnr, :anzahlm, :k,:TTEILENR,:TBSNR do suspend;
else
insert into terrorlog(tabelleproz, zeitstempel, wert, fehler) values
('P_GETMATERIAL3', 'now', :k, :teilenr || ' Wert Zhlervariable zu hoch!');
end

END

AW: [firebird-support] Sequence

['Check_Mail' check_m...@satron.de [firebird-support]]

.. and can I order all active records completly?

 

(without a loop)

Update table set prio = 1 to n where prio is not null order by prio?

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 2. Dezember 2016 10:44
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] Sequence

 

  

Okay, 

 

I would simply change the prio new in steps of 10.

 

Also I can do the following:

 

Priority in steps of 1

 

ID 10 Prio 1

ID 20 Prio 2

ID 23 Prio 3

ID 11 Prio 4

 

Now the user can Set the ID 23 to Prio 2, how can I realize this without a
loop?

 

Update table set prio = newprio(1) where id = 20;

Update table set prio = prio - 1 where prio < newprio;

Update table set prio = prio + 1 where prio > newprio;

 

..more simplier? 

 

 

 

Von: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 1. Dezember 2016 18:47
An: firebird-support@yahoogroups.com
 
Betreff: RE: [firebird-support] Sequence

 

  

Olag,

> Before
> Record 1 prio 3
> Record 2 prio 10
> Record 3 prio 18
> Record 4 prio 20
> Record 5 prio 30
> 
> The user set the record 4 to prio 15, I would like to do this:
> 
> Record 1 from 3 to 10
> Record 2 from 10 to 20
> Record 3 from 20 (should 15, Destination between record 2 and record 3))
to
> 30
> Record 4 to 40
> Record 5 to 50

You example is confusing.

If a user can never change the position of Record 4 ahead of Record 3 (as is
the case in your example) what purpose does priority serve.

If you had said that the outcome you wanted was:

Record 1 from 3 to 10
Record 2 from 10 to 20
Record 4 to 30 <-*
Record 3 from 20 (should 15, Destination between record 2 and record 3)) to
40 <-*
Record 5 to 50

Then that would have made sense.

Please clarify, the problem domain does matter to the solution.

Sean

AW: [firebird-support] Sequence

['Check_Mail' check_m...@satron.de [firebird-support]]

Okay, 

 

I would simply change the prio new in steps of 10.

 

Also I can do the following:

 

Priority in steps of 1

 

ID 10 Prio 1

ID 20 Prio 2

ID 23 Prio 3

ID 11 Prio 4

 

Now the user can Set the ID 23 to Prio 2, how can I realize this without a
loop?

 

Update table set prio = newprio(1) where id = 20;

Update table set prio = prio - 1 where prio < newprio;

Update table set prio = prio + 1 where prio > newprio;

 

..more simplier? 

 

 

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 1. Dezember 2016 18:47
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] Sequence

 

  

Olag,

> Before
> Record 1 prio 3
> Record 2 prio 10
> Record 3 prio 18
> Record 4 prio 20
> Record 5 prio 30
> 
> The user set the record 4 to prio 15, I would like to do this:
> 
> Record 1 from 3 to 10
> Record 2 from 10 to 20
> Record 3 from 20 (should 15, Destination between record 2 and record 3))
to
> 30
> Record 4 to 40
> Record 5 to 50

You example is confusing.

If a user can never change the position of Record 4 ahead of Record 3 (as is
the case in your example) what purpose does priority serve.

If you had said that the outcome you wanted was:

Record 1 from 3 to 10
Record 2 from 10 to 20
Record 4 to 30 <-*
Record 3 from 20 (should 15, Destination between record 2 and record 3)) to
40 <-*
Record 5 to 50

Then that would have made sense.

Please clarify, the problem domain does matter to the solution.

Sean

[firebird-support] Sequence

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello @ll,

 

in a table there a many records with prioritys 1, 4, ..

 

Now the user can set one record to 5 and after this, I would set all other
records before in steps of 10 and after this again:

 

For Example:

 

Before

Record 1 prio 3

Record 2 prio 10

Record 3 prio 18

Record 4 prio 20

Record 5 prio 30

 

The user set the record 4 to prio 15, I would like to do this:

 

Record 1 from 3 to 10

Record 2 from 10 to 20

Record 3 from 20 (should 15, Destination between record 2 and record 3)) to
30

Record 4 to 40

Record 5 to 50

 

How can I realize this without a loop? Update table set prio = .? Or with
cte?

 

Thank you.

 

Best regards.

 

Olaf

[firebird-support] CTE

['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