[firebird-support] Unable to execute large text inside stored procedures.

2016-09-28 Thread 'Joje' j...@codework-solutions.com [firebird-support] 
Hello,

 

I have a stored procedure that takes  a parameter(a1) that has  length
excess of 25k. Inside this stored procedure I have declared a variable with
a query , when this parameter(a1) combined with variable total length
exceeds 32k and  FB returns as error of concatenation. 

 

Below is error returned by stored procedure 

   "Concatenation overflow. Resulting string cannot exceed 32K in length."

 

I used BLOB SUB_TYPE 1 for parameter(a1) and variable in stored procedure.
Then also I am getting this same error.

 

Thank you.

Re: [firebird-support] Can merge return AffecterRows?

2016-09-28 Thread 渡辺 watan...@noveltte.jp [firebird-support] 


Thank you ,Thomas!

The count is right.I feel easy.

I didn't think there was other ticket.

I hope Reference will be changed.

Sincerely.

On 2016/09/28 22:16, 'Thomas Steinmaurer' t...@iblogmanager.com 
[firebird-support] wrote:


> hi,I am Nobuo Watanabe.
> I use FireBird 2.5.6 and FireBird2.5.3.
>
> Firebird2.5Language Reference about 'merge command' says:
> Currently, the ROW_COUNT variable returns the value 1, even if more than one
> record is modified or inserted. For details and progress, refer to Tracker
> ticket CORE-4400 .
>
> I use ISQL,and set count on;
> When I use 'merge' on ISQL2.5.3,then return 1 always.
> but when I use 'merge' on ISQL2.5.6,then affected row count return.
>
> Is this right count?or is there any condition to get right row_count?

Sounds a bit like http://tracker.firebirdsql.org/browse/CORE-4817 fixed in 
2.5.6 and 3.0

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

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





--
/ﾉﾍﾞﾙｯﾄ///
　株式会社　ノベルット　ソフトウェア　インダストリー
　　代表取締役　渡辺 伸雄
　〒144-0043　東京都 大田区 羽田２－１２－３
　Tel:03-5705-2595
　Fax:03-6423-9505
　mobile-phone:080-3430-2595 070-5582-6540
  Email:watan...@noveltte.jp
///ﾉﾍﾞﾙｯﾄ/

[firebird-support] I create View in FB 2.5 but does not work in FB 1.5

2016-09-28 Thread 'Israel Pinheiro' israel.ip...@gmail.com [firebird-support] 


Create a view FB 2.5 but does not create in FB 1.5

 

Message error ==> Line 201 | Invalid Token. Dynamic SQL Error code = -104.
Invalid command. Data Type unknown

 

 



CREATE VIEW ESPELHO(

DTMOVPROD,

TIPOMOV,

CODNAT,

CODVENDA,

CODCOMPRA,

CODPROD,

DESCPROD,

CODFISC,

TIPOMOVPROD,

DOCMOVPROD,

CODALMOX,

CODLOTE,

QTDMOVPROD,

PRECOMOVPROD,

ESTOQMOVPROD,

SLDMOVPRODAX,

TIPOPROD,

CUSTOMPMMOVPRODAX,

SLDMOVPROD,

ESTOQUE,

CUSTOMPMMOVPROD,

CODMOVPROD,

CODEMPTM,

CODEMP,

CODFILIALTM,

CODFILIAL,

CODTIPOMOV,

SALDO,

CUSTOMPMPROD,

PRECOITVENDA,

SLDPROD)

AS

SELECT 

  MP.DTMOVPROD,

  TM.TIPOMOV,

  MP.CODNAT,

  MP.CODVENDA,

  MP.CODCOMPRA,

  MP.CODPROD,

  EQ.DESCPROD,

  EQ.CODFISC,

  MP.TIPOMOVPROD,

  MP.DOCMOVPROD,

  MP.CODALMOX,

  MP.CODLOTE,

  MP.QTDMOVPROD,

  MP.PRECOMOVPROD,

  MP.ESTOQMOVPROD,

  MP.SLDMOVPRODAX,

  EQ.TIPOPROD,

  MP.CUSTOMPMMOVPRODAX,

  MP.SLDMOVPROD,

  CAST(MP.SLDMOVPROD AS NUMERIC(15, 2)) AS ESTOQUE,

  MP.CUSTOMPMMOVPROD,

  MP.CODMOVPROD,

  MP.CODEMPTM,

  TM.CODEMP,

  MP.CODFILIALTM,

  TM.CODFILIAL,

  cast(CLI.NOMECLI as char(60)) AS CODTIPOMOV,

  CAST((MP.SLDMOVPROD * MP.CUSTOMPMMOVPROD) AS NUMERIC(15, 2)) AS SALDO,

  EQ.CUSTOMPMPROD,

  sum(VDITVENDA.PRECOITVENDA) AS PRECOITVENDA,

  ES.SLDPROD

FROM

  EQMOVPROD MP

  INNER JOIN EQPRODUTO EQ ON (MP.CODPROD = EQ.CODPROD)

  INNER JOIN VDVENDA VD ON (MP.CODVENDA = VD.CODVENDA)

  INNER JOIN VDCLIENTE CLI ON (VD.CODCLI = CLI.CODCLI)

  INNER JOIN VDITVENDA ON (VD.CODEMP = VDITVENDA.CODEMP)

  AND (VD.CODFILIAL = VDITVENDA.CODFILIAL)

  AND (VD.TIPOVENDA = VDITVENDA.TIPOVENDA)

  AND (VD.CODVENDA = VDITVENDA.CODVENDA)

  AND (VDITVENDA.CODEMPPD = EQ.CODEMP)

  AND (VDITVENDA.CODFILIALPD = EQ.CODFILIAL)

  AND (VDITVENDA.CODPROD = EQ.CODPROD)

  INNER JOIN EQSALDOPROD ES ON (EQ.CODEMP = ES.CODEMP)

  AND (EQ.CODFILIAL = ES.CODFILIAL)

  AND (EQ.CODPROD = ES.CODPROD),

  EQTIPOMOV TM

WHERE

  MP.CODEMPTM = TM.CODEMP AND 

  MP.CODFILIALTM = TM.CODFILIAL AND 

  MP.CODTIPOMOV = TM.CODTIPOMOV AND 

  TIPOPROD = 'P' AND 

  MP.CODCOMPRA IS NULL

GROUP BY

  MP.DTMOVPROD,

  TM.TIPOMOV,

  MP.CODNAT,

  MP.CODVENDA,

  MP.CODCOMPRA,

  MP.CODPROD,

  EQ.DESCPROD,

  EQ.CODFISC,

  MP.TIPOMOVPROD,

  MP.DOCMOVPROD,

  MP.CODALMOX,

  MP.CODLOTE,

  MP.QTDMOVPROD,

  MP.PRECOMOVPROD,

  MP.ESTOQMOVPROD,

  MP.SLDMOVPRODAX,

 EQ.TIPOPROD,

  MP.CUSTOMPMMOVPRODAX,

  MP.SLDMOVPROD,

  MP.CUSTOMPMMOVPROD,

  MP.CODMOVPROD,

  MP.CODEMPTM,

  TM.CODEMP,

  MP.CODFILIALTM,

  TM.CODFILIAL,

  CLI.NOMECLI,

  EQ.CUSTOMPMPROD,

  ES.SLDPROD

 

UNION ALL

 

SELECT 

  MP.DTMOVPROD,

  TM.TIPOMOV,

  MP.CODNAT,

  MP.CODVENDA,

  MP.CODCOMPRA,

  MP.CODPROD,

  EQ.DESCPROD,

  EQ.CODFISC,

  MP.TIPOMOVPROD,

  MP.DOCMOVPROD,

  MP.CODALMOX,

  MP.CODLOTE,

  MP.QTDMOVPROD,

  MP.PRECOMOVPROD,

  MP.ESTOQMOVPROD,

  MP.SLDMOVPRODAX,

  EQ.TIPOPROD,

  MP.CUSTOMPMMOVPRODAX,

  MP.SLDMOVPROD,

  CAST(MP.SLDMOVPROD AS NUMERIC(15, 2)) AS ESTOQUE,

  MP.CUSTOMPMMOVPROD,

  MP.CODMOVPROD,

  MP.CODEMPTM,

  TM.CODEMP,

  MP.CODFILIALTM,

  TM.CODFILIAL,

  CAST((MP.SLDMOVPROD * MP.CUSTOMPMMOVPROD) AS NUMERIC(15, 2)) AS SALDO,

  FORN.RAZFOR AS CODTIPOMOV,

EQ.CUSTOMPMPROD,

  ES.SLDPROD,

  sum(CPITCOMPRA.PRECOITCOMPRA) AS PRECOITCOMPRA

FROM

  EQMOVPROD MP

  INNER JOIN EQPRODUTO EQ ON (MP.CODPROD = EQ.CODPROD)

  INNER JOIN CPCOMPRA CP ON (MP.CODCOMPRA = CP.CODCOMPRA)

  INNER JOIN CPFORNECED FORN ON (CP.CODFOR = FORN.CODFOR)

  INNER JOIN CPITCOMPRA ON (CP.CODEMP = CPITCOMPRA.CODEMP)

  AND (CP.CODFILIAL = CPITCOMPRA.CODFILIAL)

  AND (CP.CODCOMPRA = CPITCOMPRA.CODCOMPRA)

  AND (CPITCOMPRA.CODEMPPD = EQ.CODEMP)

  AND (CPITCOMPRA.CODFILIALPD = EQ.CODFILIAL)

  AND (CPITCOMPRA.CODPROD = EQ.CODPROD)

  INNER JOIN EQSALDOPROD ES ON (EQ.CODEMP = ES.CODEMP)

  AND (EQ.CODFILIAL = ES.CODFILIAL)

  AND (EQ.CODPROD = ES.CODPROD),

  EQTIPOMOV TM

WHERE

  MP.CODEMPTM = TM.CODEMP AND 

  MP.CODFILIALTM = TM.CODFILIAL AND 

  MP.CODTIPOMOV = TM.CODTIPOMOV AND 

  TIPOPROD = 'P' AND 

  MP.CODVENDA IS NULL

GROUP BY

  MP.DTMOVPROD,

  TM.TIPOMOV,

  MP.CODNAT,

  MP.CODVENDA,

  MP.CODCOMPRA,

  MP.CODPROD,

  EQ.DESCPROD,

  EQ.CODFISC,

  MP.TIPOMOVPROD,

  MP.DOCMOVPROD,

  MP.CODALMOX,

  MP.CODLOTE,

  MP.QTDMOVPROD,

  MP.PRECOMOVPROD,

  MP.ESTOQMOVPROD,

  MP.SLDMOVPRODAX,

  EQ.TIPOPROD,

  MP.CUSTOMPMMOVPRODAX,

  MP.SLDMOVPROD,

  MP.CUSTOMPMMOVPROD,

  MP.CODMOVPROD,

  MP.CODEMPTM,

  TM.CODEMP,

  MP.CODFILIALTM,

  TM.CODFILIAL,

  FORN.RAZFOR,

  EQ.CUSTOMPMPROD,

  ES.SLDPROD;

 





---
Este email foi escaneado pelo Avast antivírus.
https://www.avast.com/antivirus


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

Re: [firebird-support] Can merge return AffecterRows?

2016-09-28 Thread 'Thomas Steinmaurer' t...@iblogmanager.com [firebird-support] 
> hi,I am Nobuo Watanabe.
> I use FireBird 2.5.6 and FireBird2.5.3.
> 
> Firebird2.5Language Reference about 'merge command' says:
> Currently, the ROW_COUNT variable returns the value 1, even if more than one
> record is modified or inserted. For details and progress, refer to Tracker
> ticket CORE-4400 .
> 
> I use ISQL,and set count on;
> When I use 'merge' on ISQL2.5.3,then return 1 always.
> but when I use 'merge' on ISQL2.5.6,then affected row count return.
> 
> Is this right count?or is there any condition to get right row_count?

Sounds a bit like http://tracker.firebirdsql.org/browse/CORE-4817 fixed in 
2.5.6 and 3.0


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

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

Re: Re: [firebird-support] Left join derived table

2016-09-28 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Ach Arno - i see now - he need "LATERAL JOIN"
your solution is good one 
 
regards,
Karol Bieniaszewski
 
W dniu 2016-09-28 13:30:24 użytkownik 'Arno Brinkman' fbsupp...@abvisie.nl 
[firebird-support]  napisał:
 
Hi,
 
> SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2
> FROM TABLE_A A
> LEFT JOIN (SELECT FIRST 1 D.FIELD_D1, D.FIELD_D2, D.FIELD_3  FROM TABLE_D D
>  ORDER BY D.ID DESC) D ON D.FIELD_3 = A.FIELD_3
  
I assume this is not what he wants.
The reason is the FIRST 1 in the derived table! The (D.FIELD_3 = A.FIELD_3) 
clause will be executed after the FIRST 1 on the derived table is applied.
  
  
If TABLE_D.ID is unique this could be a solution:
  
SELECT
  A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2
FROM
  TABLE_A A
  LEFT JOIN TABLE_D D ON (D.ID = (SELECT FIRST D2.ID FROM TABLE_D D2 WHERE 
D2.FIELD_3 = A.FIELD_3 ORDER BY D2.ID DESC))
  
  
Kind Regards,
Arno Brinkman
ABVisie
 
 
  
From: liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
Sent: Wednesday, September 28, 2016 12:39 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Left join derived table
  
  
W dniu 2016-09-28 09:04:07 użytkownik 'Marcin Bury' marcin.b...@studio-delfi.pl 
[firebird-support]  napisał:
 
Hello All
I'd like to ask how to get below statement working:
SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2
FROM TABLE_A A
LEFT JOIN (SELECT FIRST 1 FIELD_D1, FIELD_D2 FROM TABLE_D D WHERE
D.FIELD_3 = A.FIELD_3 ORDER BY D.ID DESC) D ON (1=1)
Firebird complains that A.FIELD_3: Column does not belong to referenced
table.
I have the 'one to many' relation between TABLE_A and TABLE_D, and I
would like to join a record from TABLE_A with latest detail entry from
TABLE_D.
Maybe derived tables are not the right solution here.
Thanks
Marcin
_
_._,_.___._,___
   

 

Re: [firebird-support] Left join derived table

2016-09-28 Thread 'Arno Brinkman' fbsupp...@abvisie.nl [firebird-support] 
Hi,

> SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2
> FROM TABLE_A A
> LEFT JOIN (SELECT FIRST 1 D.FIELD_D1, D.FIELD_D2, D.FIELD_3  FROM TABLE_D D 
>  ORDER BY D.ID DESC) D ON D.FIELD_3 = A.FIELD_3

I assume this is not what he wants.
The reason is the FIRST 1 in the derived table! The (D.FIELD_3 = A.FIELD_3) 
clause will be executed after the FIRST 1 on the derived table is applied.


If TABLE_D.ID is unique this could be a solution:

SELECT 
  A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2
FROM 
  TABLE_A A
  LEFT JOIN TABLE_D D ON (D.ID = (SELECT FIRST D2.ID FROM TABLE_D D2 WHERE 
D2.FIELD_3 = A.FIELD_3 ORDER BY D2.ID DESC))


Kind Regards,
Arno Brinkman
ABVisie




From: liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Sent: Wednesday, September 28, 2016 12:39 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Left join derived table




W dniu 2016-09-28 09:04:07 użytkownik 'Marcin Bury' marcin.b...@studio-delfi.pl 
[firebird-support]  napisał:

  Hello All

  I'd like to ask how to get below statement working:

  SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2
  FROM TABLE_A A
  LEFT JOIN (SELECT FIRST 1 FIELD_D1, FIELD_D2 FROM TABLE_D D WHERE 
  D.FIELD_3 = A.FIELD_3 ORDER BY D.ID DESC) D ON (1=1)

  Firebird complains that A.FIELD_3: Column does not belong to referenced 
  table.

  I have the 'one to many' relation between TABLE_A and TABLE_D, and I 
  would like to join a record from TABLE_A with latest detail entry from 
  TABLE_D.
  Maybe derived tables are not the right solution here.

  Thanks
  Marcin


  ._,___

Re: [firebird-support] Left join derived table

2016-09-28 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
W dniu 2016-09-28 09:04:07 użytkownik 'Marcin Bury' marcin.b...@studio-delfi.pl 
[firebird-support]  napisał:
 
Hello All
I'd like to ask how to get below statement working:
SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2
FROM TABLE_A A
LEFT JOIN (SELECT FIRST 1 FIELD_D1, FIELD_D2 FROM TABLE_D D WHERE
D.FIELD_3 = A.FIELD_3 ORDER BY D.ID DESC) D ON (1=1)
Firebird complains that A.FIELD_3: Column does not belong to referenced
table.
I have the 'one to many' relation between TABLE_A and TABLE_D, and I
would like to join a record from TABLE_A with latest detail entry from
TABLE_D.
Maybe derived tables are not the right solution here.
Thanks
Marcin
_
_._,_.___._,___
 
Hi,
 
SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2
FROM TABLE_A A
LEFT JOIN (SELECT FIRST 1 D.FIELD_D1, D.FIELD_D2, D.FIELD_3  FROM TABLE_D D 
 ORDER BY D.ID DESC) D ON D.FIELD_3 = A.FIELD_3
 
regards,
Karol Bieniaszewski

[firebird-support] Left join derived table

2016-09-28 Thread 'Marcin Bury' marcin.b...@studio-delfi.pl [firebird-support] 
Hello All

I'd like to ask how to get below statement working:

SELECT A.FIELD_1, A.FIELD_2, D.FIELD_D1, D.FIELD_D2
FROM TABLE_A A
LEFT JOIN (SELECT FIRST 1 FIELD_D1, FIELD_D2 FROM TABLE_D D WHERE 
D.FIELD_3 = A.FIELD_3 ORDER BY D.ID DESC) D ON (1=1)

Firebird complains that A.FIELD_3: Column does not belong to referenced 
table.

I have the 'one to many' relation between TABLE_A and TABLE_D, and I 
would like to join a record from TABLE_A with latest detail entry from 
TABLE_D.
Maybe derived tables are not the right solution here.

Thanks
Marcin