if you have

UPDATE TABLE SET F1 = :P1, F2 = :P2

is there a way to say don't do the update for F2 if :P2 is null?

Robert

Robert

> UPDATE TABLE SET F1 = :P1, F2 = :P2
>
> is there a way to say don't do the update for F2 if :P2 is null?

UPDATE TABLE SET F1=:P1, F2=IF(:P2=NULL,F2,:P2)

Not tested

Eduardo

"Jose Eduardo Helminsky" <contato@hpro.com.br> wrote in message
news:E401F2C2-9270-4890-A678-A3A4C69322AD@news.elevatesoft.com...
> Robert
>
>> UPDATE TABLE SET F1 = :P1, F2 = :P2
>>
>> is there a way to say don't do the update for F2 if :P2 is null?
>
> UPDATE TABLE SET F1=:P1, F2=IF(:P2=NULL,F2,:P2)
>
> Not tested
>

I don't think you can test the value of a parameter to determine the course
of action. I guess I'll have to test it

Robert
> Eduardo
>

Robert,

<< I don't think you can test the value of a parameter to determine the
course of action. I guess I'll have to test it >>

Sure you can.  It's just another constant value to DBISAM.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:ECA8E908-E933-4049-9BC7-8580D79A4627@news.elevatesoft.com...
> Robert,
>
> << I don't think you can test the value of a parameter to determine the
> course of action. I guess I'll have to test it >>
>
> Sure you can.  It's just another constant value to DBISAM.
>

It does not work in this case. I get a parsing error (!?) 11949

"at least one of the IF expression arguments must be a non-null value in
UPDATE SQL ..."

not sure why that is so, why should SQL care if both the parameter and the
original value are null.

Be that as it may, the value of the parameter :P2 can not be null if there
are null F2s in the table.

UPDATE TABLE SET F1=:P1, F2=IF(:P2=NULL,F2,:P2)

I faked it by adding another parameter, P3 which is set to 1 or zero
depending on whether I want to update the field. That way I can always set
P2 to a non-null value.

IF(:P3 = 0, F2, :P2)

Robert

Robert,

<< It does not work in this case. I get a parsing error (!?) 11949 >>

Hmm, it works here:

UPDATE customer SET State=IF(:P2 IS NULL, 'FL', :P2)
WHERE State='NY'

<< "at least one of the IF expression arguments must be a non-null value in
UPDATE SQL ..."

not sure why that is so, why should SQL care if both the parameter and the
original value are null. >>

The parser has to be able to perform a proper type-check at compile time.
If it can't, then it could allow an invalid type assignment.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:3EAF8D6A-90E2-469B-A722-EAEF13B69EF9@news.elevatesoft.com...
> Robert,
>
> << It does not work in this case. I get a parsing error (!?) 11949 >>
>
> Hmm, it works here:
>
> UPDATE customer SET State=IF(:P2 IS NULL, 'FL', :P2)
> WHERE State='NY'
>

because state = NY, by definition not null. In my case, I can have both the
parameter (:P2) AND the field (State) be null. And I want(ed) to do the
update ONLY if :P2 was not null.

It is only a problem when using parameters. For example, update atable set
f2 = f2 where f2 = null works just fine.

> << "at least one of the IF expression arguments must be a non-null value
> in UPDATE SQL ..."
>
> not sure why that is so, why should SQL care if both the parameter and the
> original value are null. >>
>
> The parser has to be able to perform a proper type-check at compile time.
> If it can't, then it could allow an invalid type assignment.
>

Uh? A null has no type. And it only complains if BOTH expressions are null.
Seems to me that you shuld be able to set any type to null.

I think it is failing at execution time, not at compile time, since it works
OK if I assign a non null value to the parameter.

Robert

Robert,

<< because state = NY, by definition not null. In my case, I can have both
the parameter (:P2) AND the field (State) be null. And I want(ed) to do the
update ONLY if :P2 was not null. >>

I understand, but my point is still valid.  You can compare against a NULL
and return the result so that it is assigned to a field.   The only
stipulation is that you must provide at least one non-NULL value in an IF
function's return values (params 2 and 3).

<< It is only a problem when using parameters. For example, update atable
set f2 = f2 where f2 = null works just fine. >>

Yes, because by their nature parameters are unknown at the initial
compile-time of the statement and are only provided during execution.

<< Uh? A null has no type. And it only complains if BOTH expressions are
null.  Seems to me that you shuld be able to set any type to null. >>

Correct, if at least one of the expressions is not NULL, then it knows which
type you would like to use for the result of the IF function.  Without that
information, you could conceivably pass in a value that is *initially* NULL,
but during execution changes to some other type based upon the IF test
condition, subsequently causing a failure in the engine due to an unexpected
(and mismatched) type assignment.  That is why DBISAM must know the result
type of the IF function at SQL compile time.

<< I think it is failing at execution time, not at compile time, since it
works OK if I assign a non null value to the parameter. >>

Do you mean compile-time for DBISAM, or compile-time for your application ?
I'm referring to the compilation of the SQL statement, not the Delphi code.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:A65DDDEB-B8FC-4E56-8B36-ECED61570489@news.elevatesoft.com...
>
> Do you mean compile-time for DBISAM, or compile-time for your application
> ? I'm referring to the compilation of the SQL statement, not the Delphi
> code.
>

I know what you meant. There was no way to know that my data contined nulls
ahead of time.

Robert