ODP: [firebird-support] Firebird schema questions

2016-03-07 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 


hi,
odbc is a standard connection layer for many databases. It contain then many 
features not supportet by every rdbms. And schemas is one of them.
regards,Karol Bieniaszewski

 Oryginalna wiadomość 
Od: "heineferre...@yahoo.com [firebird-support]" 
 
Data: 07.03.2016  14:44  (GMT+01:00) 
Do: firebird-support@yahoogroups.com 
Temat: [firebird-support] Firebird schema questions 


 



  



  
  
  Hi,
What is a schema and how do I use it?
I saw in the odbc driver settings they mention schemas.

Thanks

Heine

[firebird-support] UDF install in FB3.0 RC2

2016-03-07 Thread russ...@belding.co.nz [firebird-support] 
I have a FB 2.5.5 database which I have migrated to FB3 RC2. I use the older 
FreeUDFLib.dll. All the procedures of this DLL are in the database reported by 
Database Workbench 5. The restore log for the migration shows no errors. This 
DLL is in the FB 3 UDF folder. When running a stored procedure that calls 
F_YEAR I get error message 
 "invalid request BLR at offset 45. function F_YEAR is not defined. module name 
or entrypoint could not be found."
 

 I try to make a simpler test case. I install FreeUDFLib into the Employee DB 
which comes with FB 3. It seems to install OK. F_YEAR and its companions now 
show in the UDF list for the Employee DB. Now run a stored procedure
 "myString = cast(F_YEAR(current_timestamp) as varchar(12)));"
 I get the same error.
 It does not seem to be a permission problem with the UDF folder.
 I am using Win 10 (64) and FB3 (32).
 

 Any help will be appreciated.

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread 'Carlos H. Cantu' lis...@warmboot.com.br [firebird-support] 
LSSBcfs> It has not!
LSSBcfs> RC releases should not be used for production purposes.

Just a note: FB 3 RC2 will become the final release, unless someone
finds a critical bug or regression that would require another RC.

[]s
Carlos
www.firebirdnews.org - www.FireBase.com.br

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 
Il 07/03/2016 17.28, Thomas Steinmaurer t...@iblogmanager.com 
[firebird-support] ha scritto:
> Although not that flexible as an ad-hoc query, but perhaps your
> particular use case can be achieved with a selectable stored procedure
> ...

Thank you Thomas, I modified my query like this:

select
DT.DATA,
   DT.DOCUMENTO_ID,
   DT.NUMERO,
   DT.SERIE,
   DC.CARICO,
   DC.SCARICO,
   SUM(dc2.carico - DC2.SCARICO) AS saldo
from
   DOC_TESTA DT
   JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID
   left JOIN DOC_CORPO DC2 ON (DC.DOC_TESTA_ID >= DC2.DOC_TESTA_ID)
AND 
(DC.ARTICOLO_ID = DC2.ARTICOLO_ID)
WHERE
DC.ARTICOLO_ID = :ID
GROUP BY
DATA,
   DOCUMENTO_ID,
   NUMERO,
   SERIE
ORDER BY
DATA,
   DOCUMENTO_ID,
   NUMERO,
   SERIE

but I have an issue:
if I have a row with CARICO = 0 and SCARICO = 0, SALDO is added or 
subtracted by the number present in last row with
CARICO > 0 or SCARICO > 0

if I add "(AND(DC.CARICO + DC.SCARICO) > 0) IN a LEFT JOIN I obtain 
almost correct result but the rows that contain "CARICO = 0 and SCARICO 
= 0" now contain SALDO = NULL, the others contains correct progressive sum.

What I'm wrong?

Thanks
-- 

Luigi Siciliano
--

-- 

Luigi Siciliano
--







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] 


> I'm sure, IBReplicator does its job very well. I simply noted that V3 RC 
> should
> be treated as not production-ready (like any other RC in Firebird's history),
> but perhaps the Firebird project philosophy changed.

It has not!

RC releases should not be used for production purposes.


> > If he is able to write queries to the database, he knows for sure
> > which tables he needs. Extract definitions of these tables in
> > FlameRobin or IBExpert is easy. Create a new database with these
> > tables inside and copy data for these tables is also easy. After that 
> > setting
> up replication will take less than an hour, including reading of manual.
> 
> Na, replication is an additional (complex) layer, which needs to be
> maintained, monitored etc ...

Exactly my point!

"Hacks" don't require consideration of these issue, "solutions" need to 
consider all factors (including, for many like BroadView, 
deployment/configuration considerations for client/install sites).

This mailing list is about providing input/answers for solutions, not hacks.


Sean

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
07.03.2016 17:21, Thomas Steinmaurer t...@iblogmanager.com [firebird-support] 
wrote:
>> > Nothing prevents him from installing of an additional server for 
>> > report purposes and
>> >setting up replication to it from production one.
> 
> Yeah. Sure. ;-)
> 

   Well, if setting up standby server for reporting purposes with IBLogManager 
is too had 
task, he always can try IBPhoenix Replicator which is easy to install and 
configure.
   If he is able to write queries to the database, he knows for sure which 
tables he 
needs. Extract definitions of these tables in FlameRobin or IBExpert is easy. 
Create a new 
database with these tables inside and copy data for these tables is also easy. 
After that 
setting up replication will take less than an hour, including reading of manual.

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Thomas Steinmaurer t...@iblogmanager.com [firebird-support] 
> Il 07/03/2016 11.32, Dimitry Sibiryakov s...@ibphoenix.com
> [firebird-support] ha scritto:
>> sum(saldo) over (order by data)
>
> Thank You for your response but I can't do: sum(SALDO) because SALDO is
> not a field of my table.
>
> I badly explained myself: I have a table with only this fields:
>
> DATA DOCUMENTO_ID   NUMERO   SERIE  CARICO   SCARICO
>
>
> I need add SALDO fields in the result of the query to get a progressive
> sum of CARICO - SCARICO.

Although not that flexible as an ad-hoc query, but perhaps your 
particular use case can be achieved with a selectable stored procedure ...

I'm not good in writing PSQL out of my mind at the moment, but execute 
your SQL (without the SUM aggregate field) in a stored procedure, 
iterate over the result set, remember the computation for the previous 
row in a local variable and use that for the addition with the current 
row and call SUSPEND for each result set iteration.



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Thomas Steinmaurer t...@iblogmanager.com [firebird-support] 
> 07.03.2016 12:55, 'Thomas Steinmaurer' t...@iblogmanager.com 
> [firebird-support] wrote:
>> Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants 
>> to run 3.0 in
>> production yet. ;-)
>
> Nothing prevents him from installing of an additional server for report 
> purposes and
> setting up replication to it from production one.


Yeah. Sure. ;-)



-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
07.03.2016 16:57, 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] 
wrote:
> You think that users can spin up servers/replication on a whim to solve a 
> problem in a
> deployed system???

   A lot of them does so.

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] 


> > Needless to say, 3.0 is in release candidate state. Not sure if Luigi
> > wants to run 3.0 in production yet. ;-)
> 
>Nothing prevents him from installing of an additional server for report
> purposes and setting up replication to it from production one.

Seriously!??

Do you read your posts before you send them?

You think that users can spin up servers/replication on a whim to solve a 
problem in a deployed system???

What about managing such a deployment?

How about you spend more time providing real life answers/solutions to 
issues/problems posted here.


Sean

[firebird-support] Firebird schema questions

2016-03-07 Thread heineferre...@yahoo.com [firebird-support] 
Hi,
What is a schema and how do I use it?
I saw in the odbc driver settings they mention schemas.

Thanks

Heine

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2016-03-07 11:32, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] wrote:
> 07.03.2016 11:16, Luigi Siciliano luigi...@tiscalinet.it
> [firebird-support] wrote:
>> How I do?
>
>sum(saldo) over (order by data)

That only works with Firebird 3.

Mark

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
07.03.2016 12:53, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
wrote:
>
> Not so hurry.. FB3 is on RC2 stage not final.

   According to the announce it is stable enough for reporting/OLAP server.

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
07.03.2016 12:55, 'Thomas Steinmaurer' t...@iblogmanager.com [firebird-support] 
wrote:
> Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants 
> to run 3.0 in
> production yet. ;-)

   Nothing prevents him from installing of an additional server for report 
purposes and 
setting up replication to it from production one.

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread 'Thomas Steinmaurer' t...@iblogmanager.com [firebird-support] 
> 07.03.2016 12:29, Luigi Siciliano luigi...@tiscalinet.it [firebird-support]
> wrote:
>> Ok, I understand but, "over" is unknow in firebird 2.5.5.
> 
>   Upgrade to 3.0.

Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants to 
run 3.0 in production yet. ;-)



--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.


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

Re: Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
> 07.03.2016 12:29, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 
> wrote:
> > Ok, I understand but, "over" is unknow in firebird 2.5.5.
> 
>Upgrade to 3.0.
> 
> -- 
>WBR, SD.

Not so hurry.. FB3 is on RC2 stage not final.


regards,
Karol Bieniaszewski

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
07.03.2016 12:29, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 
wrote:
> Ok, I understand but, "over" is unknow in firebird 2.5.5.

   Upgrade to 3.0.

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 
Il 07/03/2016 11.58, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] ha scritto:
> sum(carcio-scarcio) over () as SALDO.

Ok, I understand but, "over" is unknow in firebird 2.5.5.

I modified my query like this:

select
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE,
DC.CARICO,
DC.SCARICO,
SUM(dc2.carico - DC2.SCARICO) AS saldo
from
DOC_TESTA DT
JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID
left JOIN  DOC_CORPO DC2 ON (DC.DOC_TESTA_ID >= DC2.DOC_TESTA_ID)
 AND (DC.ARTICOLO_ID = DC2.ARTICOLO_ID)
WHERE
DC.ARTICOLO_ID = :ID
GROUP BY
   DATA,
   DOCUMENTO_ID,
   NUMERO,
   SERIE
ORDER BY
DATA,
DOCUMENTO_ID,
NUMERO,
SERIE

but I have an issue:

if I have a row with CARICO = 0 and SCARICO = 0

then SALDO is added or subtracted of number present in last row with 
CARICO > 0 or SCARICO > 0

Thanks
-- 

Luigi Siciliano
--

[firebird-support] Slow query with FB 2.5.5 - Why ?

2016-03-07 Thread adrien.rebois...@yahoo.com [firebird-support] 
Hi,
 

 I'm using FB 2.5.5 CS under Windows 8.1. Selectivity of indices has been 
updated before asking for help :)
 

 I'm trying to optimize the following query :
 

 UPDATE TVERSIONS v1 SET ... WHERE RD_BACKUPID=:BackupID
  AND NOT EXISTS (SELECT 1 FROM TVERSIONS v2 WHERE 
v2.FILEID=v1.FILEID AND DDATE IS NULL)
  AND NOT EXISTS (SELECT 1 FROM TVERSIONS v3 WHERE 
v3.FILEID=v1.FILEID AND v3.DDATE >= :DelTreshold)
  
 Plan
 PLAN (TBACKUPVERSIONS INDEX (PK_BACKUPVERSIONS))
 PLAN (V2 INDEX (RDB$FOREIGN10))
 PLAN (TBACKUPVERSIONS INDEX (PK_BACKUPVERSIONS))
 PLAN (V3 INDEX (RDB$FOREIGN10))
 PLAN (V1 INDEX (TVERSIONS_ID_RDBACKUPID))
 

 Adapted Plan
 PLAN (TBACKUPVERSIONS INDEX (PK_BACKUPVERSIONS))
 PLAN (V2 INDEX (INTEG_18))
 PLAN (TBACKUPVERSIONS INDEX (PK_BACKUPVERSIONS))
 PLAN (V3 INDEX (INTEG_18))
 PLAN (V1 INDEX (TVERSIONS_ID_RDBACKUPID))
 

 35 record(s) was(were) updated in TVERSIONS
 

 -- Performance info --
 Prepare time = 0ms
 Execute time = 14s 875ms
 Current memory = 2 907 024
 Max memory = 3 022 112
 Memory buffers = 90
 Reads from disk to cache = 1 012 731
 Writes from cache to disk = 33
 Fetches from cache = 8 677 201
 

 There is 21 895 records in the TVERSIONS table satisfying the condition 
(RD_BACKUPID=:BackupID), but IBExpert shows 1 251 930 indexed reads from this 
table while it contains only 378 595 records... 
 

 * RD_BACKUPID is indexed,
 * FILEID is a foreign key so its also indexed,
 * DDATE is a computed field : DDATE = (SELECT TBACKUPVERSIONS.SERVERSTARTDATE 
FROM TBACKUPVERSIONS WHERE ID = TVERSIONS.CVERSION)
 

 I don't understand why this query takes so much time (most of the other 
queries take less than 500ms to execute). Is it because of the use of a 
computed field in the subqueries ? What can I do in order make things faster ?
 

 
 Best regards,
 

 Adrien

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
07.03.2016 11:51, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 
wrote:
> I need add SALDO fields in the result of the query to get a progressive
> sum of CARICO - SCARICO.

   sum(carcio-scarcio) over () as SALDO.

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 
Hallo,

Il 07/03/2016 11.32, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] ha scritto:
> sum(saldo) over (order by data) 

Thank You for your response but I can't do: sum(SALDO) because SALDO is 
not a field of my table.

I badly explained myself: I have a table with only this fields:

DATA DOCUMENTO_ID   NUMERO   SERIE  CARICO   SCARICO


I need add SALDO fields in the result of the query to get a progressive 
sum of CARICO - SCARICO.

Thanks

-- 

Luigi Siciliano
--







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
07.03.2016 11:16, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 
wrote:
> How I do?

   sum(saldo) over (order by data)

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Re: query to return the last entry in detail table for each entry in master table

2016-03-07 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
 
i suppose that you sholuld change
r.review_date > r2.review_date
 
to
 
r.review_date < r2.review_date
 
regards,
Karol Bieniaszewski
 
W dniu 2016-03-07 11:02:35 użytkownik talorigo...@yahoo.co.uk 
[firebird-support]  napisał:
 
Hi Set
 
Thanks for helping me with this.  Your answer has been a great help.  However 
there is a problem with the result set.  In the Assignment table I have the 
following data: (of interest is assignid 9 which is projectid 19)
ASSIGNID ASSIGN_DATE MANAGER PROJECTID
1 03/03/2016 11
2 03/03/2016 12
3 03/03/2016 13
4 03/03/2016 14
5 03/03/2016 15
6 03/03/2016 16
7 05/02/2012 17
8 05/02/2012 18
9 05/02/2012 19
10 03/03/2012 20
 
In the review table I have the following: (Note that assignid 9 has two 
entries.  One with a review date of 5/2/2012 and the other with a review date 
of 5/5/2015)
 
REVIEWID  ASSIGNID REVIEW_DATE  PROGRESS_NOTE
1 1  03/03/2016  
2 2 03/03/2016  
3 3 03/03/2016  
4 4 03/03/2016  
5 5 03/03/2016  
6 6 03/03/2016  
7 7 05/02/2012  
8 8 05/02/2012  
9 9 05/02/2012  
10 1 05/02/2012  
11 10 03/03/2012  
12 9 05/05/2015  
 
using your queries give me the following result:
 
PROJECTID ASSIGN_DATE REVIEW_DATE PROGRESS_NOTE
11 03/03/2016 05/02/2012
12 03/03/2016 03/03/2016
13 03/03/2016 03/03/2016
14 03/03/2016 03/03/2016
15 03/03/2016 03/03/2016
16 03/03/2016 03/03/2016
17 05/02/2012 05/02/2012
18 05/02/2012 05/02/2012
19 05/02/2012 05/02/2012
20 03/03/2012 03/03/2012
 
The result set has Projectid 19 (assignid 9) with the a review date of 
05/02/2012 instead of 5/5/2015.  I'm not sure which bit of the query to tweak 
to get the expected result. Can I enlist your help again please.
 

 

[firebird-support] How write a query with a progressive sum field

2016-03-07 Thread Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 
Hallo,
   I need to write a query with a computed field that contain a 
progressive sum like this table:

DATA DOCUMENTO_ID   NUMERO   SERIE  CARICO   SCARICO   SALDO
01/01   A  1 A  
10 1
02/01   A  2  A  
30 4
02/01   V 33 B  
02 2
02/01   V 35 C  
01 1

I need to modify this query by adding SALDO field to get progressive sum 
of CARICO - SCARICO:
select
   DT.DATA,
   DT.DOCUMENTO_ID,
   DT.NUMERO,
   DT.SERIE,
   DC.CARICO,
   DC.SCARICO
from
   DOC_TESTA DT
   JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID
WHERE
   DC.ARTICOLO_ID = :ID
ORDER BY
   DATA,
   DOCUMENTO_ID,
   NUMERO,
   SERIE


How I do?

Thanks
-- 

Luigi Siciliano
--







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Re: query to return the last entry in detail table for each entry in master table

2016-03-07 Thread talorigo...@yahoo.co.uk [firebird-support] 
Hi Set
 

 Thanks for helping me with this.  Your answer has been a great help.  However 
there is a problem with the result set.  In the Assignment table I have the 
following data: (of interest is assignid 9 which is projectid 19)
 
 
 
 
 ASSIGNID ASSIGN_DATE MANAGER PROJECTID 1 03/03/2016 11 2 03/03/2016 12 3 
03/03/2016 13 4 03/03/2016 14 5 03/03/2016 15 6 03/03/2016 16 7 05/02/2012 17 8 
05/02/2012 18 9 05/02/2012 19 10 03/03/2012 20 
 

In the review table I have the following: (Note that assignid 9 has two 
entries.  One with a review date of 5/2/2012 and the other with a review date 
of 5/5/2015)
 

 REVIEWID ASSIGNID REVIEW_DATE PROGRESS_NOTE 1 1 03/03/2016   2 2 03/03/2016   
3 3 03/03/2016   4 4 03/03/2016   5 5 03/03/2016   6 6 03/03/2016   7 7 
05/02/2012   8 8 05/02/2012   9 9 05/02/2012   10 1 05/02/2012   11 10 
03/03/2012   12 9 05/05/2015   
 

 using your queries give me the following result:
 

 PROJECTID ASSIGN_DATE REVIEW_DATE PROGRESS_NOTE 11 03/03/2016 05/02/2012 12 
03/03/2016 03/03/2016 13 03/03/2016 03/03/2016 14 03/03/2016 03/03/2016 15 
03/03/2016 03/03/2016 16 03/03/2016 03/03/2016 17 05/02/2012 05/02/2012 18 
05/02/2012 05/02/2012 19 05/02/2012 05/02/2012 20 03/03/2012 03/03/2012 
 

 The result set has Projectid 19 (assignid 9) with the a review date of 
05/02/2012 instead of 5/5/2015.  I'm not sure which bit of the query to tweak 
to get the expected result. Can I enlist your help again please.

AW: [firebird-support] differences between firebird 2.5 cs and 3.0 rc2 ss

2016-03-07 Thread 'Checkmail' check_m...@satron.de [firebird-support] 
Many Thanks, Ann :)

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Samstag, 5. März 2016 20:29
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] differences between firebird 2.5 cs and 3.0 rc2 
ss

 

  

On Fri, Mar 4, 2016 at 3:22 AM, 'Checkmail' check_m...@satron.de 
  [firebird-support] 
 > 
wrote:

 

 

If I must go back tot he 2.5 version of firebird, how can I gbak the new 
backup-file to the old version?

 

If you haven't used V3 features in data definitions or the new authorization 
options, reverting is pretty easy.  Leave V3 installed for the backup.   Find a 
copy of gbak from a 2.5 distribution and use it to create a backup file.  
Install Firebird 2.5.x and use the 2.5.x gbak to restore the database.  If 
you've used 3.0 features including the various authorization options, you'll 
probably need to extract the database metadata, edit out references to new 
features and create a new V2.5.x database in a separate environment.  You'll 
need to have both versions of Firebird available simultaneously.  Use one of 
the database migration tools to copy the data from thoe V3 database to the V 
2.5  You can find migration tools here: 
http://ibphoenix.com/download/tools/migration

 

Good luck,

 

Ann