🚀 go-pugleaf

Insert debugging information into some table.

Isaac wrote:
> Hello,
> How I can debug stored procedures called by a trigger from inside my 
> application ?
> 
> Thanks

another question please,
why when I sum a value with a variable hold a null value the result 
evaluate to null not the first value ??

this mess a lot with my calculations when a select return <null> all my 
calculation evaluate also to null how I can avoid this ???

Thanks

sql server has isnull() function but interbase did not has it , I really 
did not want to use alot of if to test for null I hope also not to us UDF!!

update:
I get this error:

Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.


when I try to execute this SQL

select COALESCE(sum(QTY),0), COALESCE(sum(QTY * COST_PRICE),0) from 
purchase_inv_item
     left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
     where (purchase_inv.purch_return <> 1)
       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
       into :pur_sum_qty, :pur_sum_cost;

I successfully compiled the procedure but I get the error at run time !!
I also tried to make like this sum(COALESCE(QTY,0)) but I was not able 
to even compile

please advice

Isaac wrote:

> why when I sum a value with a variable hold a null value the result
> evaluate to null not the first value ??

Because the SQL standard says that null + anything is null. Null is not
a value. Null is a state indicating that there is no value. All SQL
compliant databases work this way.

-- 
Bill Todd (TeamB)

InterBase has NULLIF, CASE and COALESCE.

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> InterBase has NULLIF, CASE and COALESCE.
> 
well I tried COALESCE but I get error message
Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.

please see my last post in this thread

Thanks alot

Your last post in this thread is:

"sql server has isnull() function but interbase did not has it , I
really did not want to use alot of if to test for null I hope also not
to us UDF!!"

which I understood to mean that you are looking for a way to do what
ISNULL does in Transact SQL. In my reply I provided the alternatives
that are available in IB. Now you are telling us that COALESCE gave you
an error but you do not show us the code that caused the error. No one
here can read your mind. If you want help you must provide enough
information.

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> Your last post in this thread is:
> 
> "sql server has isnull() function but interbase did not has it , I
> really did not want to use alot of if to test for null I hope also not
> to us UDF!!"
> 
> which I understood to mean that you are looking for a way to do what
> ISNULL does in Transact SQL. In my reply I provided the alternatives
> that are available in IB. Now you are telling us that COALESCE gave you
> an error but you do not show us the code that caused the error. No one
> here can read your mind. If you want help you must provide enough
> information.
> 
I am crying here, always misunderstand me

here is my last post again:
update:
I get this error:

Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.


when I try to execute this SQL

select COALESCE(sum(QTY),0), COALESCE(sum(QTY * COST_PRICE),0) from 
purchase_inv_item
     left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
     where (purchase_inv.purch_return <> 1)
       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
       into :pur_sum_qty, :pur_sum_cost;

I successfully compiled the procedure but I get the error at run time !!
I also tried to make like this sum(COALESCE(QTY,0)) but I was not able 
to even compile

please advice

Sorry. I did not scroll down far enough to see that you had replied to
one of your earlier messages.

Apparently you cannot use COALESCE in two SUM aggregates in the same
SELECT. Try running

select COALESCE(sum(QTY),0) from purchase_inv_item
   left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code)
    where (purchase_inv.purch_return <> 1)
      and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
      and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)

in isql after replacing the parameters with literal values and see if
you still get an error.

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> Sorry. I did not scroll down far enough to see that you had replied to
> one of your earlier messages.
> 
> Apparently you cannot use COALESCE in two SUM aggregates in the same
> SELECT. Try running
> 
> select COALESCE(sum(QTY),0) from purchase_inv_item
>    left outer join purchase_inv on (purchase_inv_item.pur_code =
> purchase_inv.pur_code)
>     where (purchase_inv.purch_return <> 1)
>       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
>       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
> 
> in isql after replacing the parameters with literal values and see if
> you still get an error.
> 

news sql

select COALESCE(sum(QTY),0) from purchase_inv_item
     left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
     where (purchase_inv.purch_return <> 1)
       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
       into :pur_sum_qty;

result: same error -902 !! the error also appear without using sum at 
all !!! is that a bug ?

Isaac wrote:

> is that a bug ?

I do not know since I do not know what caused the error. Can you post
the actual SQL statement that you ran in isql?

-- 
Bill Todd (TeamB)

Isaac wrote:

> select COALESCE(sum(QTY),0) from purchase_inv_item
>    left outer join purchase_inv on (purchase_inv_item.pur_code =
> purchase_inv.pur_code)     where (purchase_inv.purch_return <> 1)
>       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
>       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
>       into :pur_sum_qty;

	There's not really a need to use COALESCE here. Since you're doing
this in a context of a stored procedure, you can check against NULL
after the query executes.

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Borland newsgroup denizen Sergio Gonz�lez has a new CD of 
  Irish music out, and it's good: http://tinyurl.com/7hgfr

Isaac wrote:

> select COALESCE(sum(QTY),0) from purchase_inv_item
>    left outer join purchase_inv on (purchase_inv_item.pur_code =
> purchase_inv.pur_code)     where (purchase_inv.purch_return <> 1)
>       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
>       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)

I posted the wrong SQL. The SQL should have been:

select SUM(COALESCE(QTY),0)) from purchase_inv_item
   left outer join purchase_inv on (purchase_inv_item.pur_code =
purchase_inv.pur_code)
    where (purchase_inv.purch_return <> 1)
      and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
      and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)

Again, make sure that you replace the two parameters with actual values
before you try to execute it using isql.

-- 
Bill Todd (TeamB)

Note that per SQL Standard,

NULL + anything = NULL
NULL - anything = NULL
NULL * anything = NULL
NULL / anything = NULL

Isaac wrote:
> 
> another question please,
> why when I sum a value with a variable hold a null value the result 
> evaluate to null not the first value ??
> 
> this mess a lot with my calculations when a select return <null> all my 
> calculation evaluate also to null how I can avoid this ???
> 
> Thanks

Quinn Wildman (CodeGear Developer Support) wrote:
> Note that per SQL Standard,
> 
> NULL + anything = NULL
> NULL - anything = NULL
> NULL * anything = NULL
> NULL / anything = NULL
> 

Yes I know that after search the net and read an article about NULL, 
Many thanks for your concern

there is other calculations in this proc all evaluate to null if one var 
was null

> I posted the wrong SQL. The SQL should have been:
> 
> select SUM(COALESCE(QTY),0)) from purchase_inv_item
>    left outer join purchase_inv on (purchase_inv_item.pur_code =
> purchase_inv.pur_code)
>     where (purchase_inv.purch_return <> 1)
>       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
>       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
> 

not working cant compile because of wrong number of parenthesis I tried 
also to use this SUM(COALESCE(QTY,0)) but also I did not able to compile

now my brain hold a NULL value :), any idea ??

 > Again, make sure that you replace the two parameters with actual values
 > before you try to execute it using isql.

dont worry I always supply real values

Craig Stuntz [TeamB] wrote:
> Isaac wrote:
> 
>> there is other calculations in this proc all evaluate to null if one
>> var was null
> 
> 	Read my post again.
> 
oh you mean I can use if <> is null ? no I dont want to use ifs and this 
proc is called by a trigger

Thanks

Isaac wrote:

> there is other calculations in this proc all evaluate to null if one
> var was null

	Read my post again.

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Borland newsgroup denizen Sergio Gonz�lez has a new CD of 
  Irish music out, and it's good: http://tinyurl.com/7hgfr

Isaac wrote:

> 
> not working cant compile because of wrong number of parenthesis I
> tried also to use this SUM(COALESCE(QTY,0)) but also I did not able
> to compile

When you say "compile" I assume you are changing the SELECT in your
stored procedure. That is not what I asked you to do. The way to
troubleshoot a SELECT is to run it in isql, not in a stored procedure.

To solve your problem you need to find out 1) if it is the SELECT that
is causing the error or something else in the stored proc and 2) if it
is the SELECT you need to determine exactly what in the SELECT is
causing the error.

-- 
Bill Todd (TeamB)

Isaac wrote:

> oh you mean I can use if <> is null ? no I dont want to use ifs and
> this proc is called by a trigger

	You can do this *in* *the* *proc.*

	Inside the proc, not in the trigger.

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt 
  your DBs!  Open Edition users, get 6.0.1.6 from http://mers.com

> When you say "compile" I assume you are changing the SELECT in your
> stored procedure. That is not what I asked you to do. The way to
> troubleshoot a SELECT is to run it in isql, not in a stored procedure.
> 
> To solve your problem you need to find out 1) if it is the SELECT that
> is causing the error or something else in the stored proc and 2) if it
> is the SELECT you need to determine exactly what in the SELECT is
> causing the error.
> 

I use IBExpert so I can debug and modify my proc quickly

please see those pictures I used the two possible syntax of COALESCE and 
in both I get the same error I also tried
1-http://img525.imageshack.us/my.php?image=29721025fp5.gif
2-http://img501.imageshack.us/my.php?image=39724324nm5.gif

I tried the SQL also in other tools I get the same error

Isaac wrote:

> I use IBExpert so I can debug and modify my proc quickly
> 
> please see those pictures I used the two possible syntax of COALESCE
> and in both I get the same error I also tried
> 1-http://img525.imageshack.us/my.php?image=29721025fp5.gif
> 2-http://img501.imageshack.us/my.php?image=39724324nm5.gif
> 
> I tried the SQL also in other tools I get the same error

InterBase does not have a debugging interface. Tools like IBExpert use
there own execution code to help you debug procedures. When you are
debugging the procedure is _not_ being executed by IB. While tools like
IBExpert are very good there have been cases reported where things
execute differently in IB than they do in a third party debugger. I do
not know if that is happening to you but it is possible.

Since you are not willing to follow my suggestions there is nothing
else I can do. Good luck!

-- 
Bill Todd (TeamB)

> Since you are not willing to follow my suggestions there is nothing
> else I can do. Good luck!
> 

NO!! I followed your suggestions and still get the same error
see this screenshot:
http://img88.imageshack.us/my.php?image=33474886fm1.gif

What version of InterBase do you have? The writers in this thread have 
assumed you have at least version 7.5. If you have an earlier version 
much of what has been said in this thread does not apply as CASE, NULLIF 
and COALLESE were introduced in version 7.5.

Isaac wrote:
> Hello,
> How I can debug stored procedures called by a trigger from inside my 
> application ?
> 
> Thanks

I posted the wrong SQL the first time then I posted a correction which
you may have missed. Change the SQL to

SELECT SUM(COALESCE(QTY,0)) ...

I know that COALESCE(SUM... does not work.

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> SELECT SUM(COALESCE(QTY,0)) ...

did not work

http://img160.imageshack.us/my.php?image=42158773zl3.gif

Quinn Wildman (CodeGear Developer Support) wrote:
> What version of InterBase do you have? The writers in this thread have 
> assumed you have at least version 7.5. If you have an earlier version 
> much of what has been said in this thread does not apply as CASE, NULLIF 
> and COALLESE were introduced in version 7.5.
> 
> Isaac wrote:
>> Hello,
>> How I can debug stored procedures called by a trigger from inside my 
>> application ?
>>
>> Thanks
InterBase 2007

Start taking things out  of the SELECT until you find out what is
causing the error.

-- 
Bill Todd (TeamB)

Here we go

I tried to make it like this
SUM(COALESCE(QTY, cast(0 AS FLOAT)))

and get this error
data type not supported for arithmetic.!!

it seems SUM can not be used with COALESCE which is very insufficiency 
situation , another bug ???

I think I found another bug or a defect that I cant use SUM with COALESCE

I tried SUM(COALESCE(QTY, cast(0 AS FLOAT)))

and get this error:
Unsuccessful execution caused by system error that does not preclude 
successful execution of subsequent statements.
data type not supported for arithmetic.

Quinn Wildman (CodeGear Developer Support) wrote:
> I don't understand why you even need coalesce here. NULL's do not effect 
> SUM. Let's consider a trivial example
> 
> create table t1 (f1 float);
> insert into t1 values (NULL);
> insert into t1 values (1.2);
> insert into t1 values (2.3);
> 
> Now, we know that NULL + 1.2 + 2.3 = NULL. However, select sum(f1) from 
> t1 returns 3.5. Isn't that what you want?
> 
> Isaac wrote:
>> I think I found another bug or a defect that I cant use SUM with COALESCE
>>
>> I tried SUM(COALESCE(QTY, cast(0 AS FLOAT)))
>>
>> and get this error:
>> Unsuccessful execution caused by system error that does not preclude 
>> successful execution of subsequent statements.
>> data type not supported for arithmetic.
no I introduced this sql as a part of a stored procedure contain many 
selects some tables are empty so if one table is empty it will return 
null and in my final equation

ex:
e = table_1_result_value (5) + table_1_result_value (20) + 
table_1_result_value (null) = NULL not (25) I will go and use IFs for now