At 15:19 28/01/2004 +1100, you wrote:

>...snip...
>EXTRACT() can't do anything with NULL ("expression evaluation not
>supported") so don't write queries that pass NULLs to EXTRACT().
>
>What is the problem you want to solve? Do you want to find a way to
>prevent EXTRACT() being called if there is a null there? If so, try using
>CASE().
>
>select
> t1.id, t1.foo,
> t2.id, t2.test_month,
> t3.id, t3.test_date,
> (CASE
> t3.test_date WHEN NULL THEN NULL
> ELSE Extract(Month from t3.test_date)
> END) AS M,
> (CASE
> t3.test_date WHEN NULL THEN NULL
> ELSE Extract(Year from t3.test_date)
> END) AS Y
>
>/heLen

Hi Helen, Arno, Alexander

As Alexander said, I have no problems when extract month from a null in a
field.

The problems occurs when I extract month from a field that has null because
of a left join...

if I have a record on table_3:

id test_date
1 null
2 '2004-01-01'

I can do select extract(month from test_date) from table_3;

but if I have on table_1 the follwoing records

id foo
1 horse
2 cow
3 duck

If I do

Select
t1.id, t1.foo,
t3.id, extract(month from t3)
from
table_1 t1 left join
table_3 t3 on (t3.id = t1.id)

I would expect to get

id foo id extract
1 horse 1 null
2 cow 2 1
3 duck null null

but I get

id foo id extract
1 horse 1 null
2 cow 2 1
expression evaluation not
supported

what I want to do is:

I have 3 tables, and want a query to compare estimated budget and realized
budget (I hope I can explain, I don't know english financial terms...)

In the first table (Account) I have the accounts:

AccountID Description
1 Software
2 Hardware
3 R&D

On the Second Table (Forecast) I will have the expenses forecast for each
month/year pair (not necessarily I should have one record here for each
account on "account table")

ForecastID AccountID Month Year EstimatedValue
1 1 1 2004 -100
2 2 1 2004 -200
3 3 1 2004 -90
4 1 2 2004 -100
5 2 2 2004 -150

See... I do not have a forecast for AccountID 3 on February/2004.

On the 3rd Table (BankMoviment), this table holds every debit/credit that
happens on my Bank Account, so here I have exactly the values I spent. This
table should reflect the "Bank Statement".

BankMovimentID AccountID MovimentDate Value Type
1 1 '2004-01-01' 10 D
2 1 '2004-01-01' 2 C
3 2 '2004-01-01' 300 D
4 1 '2004-02-01' 100 D
5 2 '2004-02-01' 20 D
6 3 '2004-02-01' 300 D

See I don't have moviments on january for AccountID 3 <- this will cause me
trouble in the query...

if I try:

select
A.AccountID, A.Description,
BM.Date, BM.Value, extract(month from BM.Date), extract(year from BM.Date)
from
Account A left join
BankMoviment BM on (BM.AccountID = A.AccountID)

I get the error....

So I need to compare how much I estimated to spend and how much I really
spented...

I need something like this

AccountID Description Month Year Estimated Credits Debits
1 Software 1 2004 -100 2 10
2 Hardware 1 2004 -200 null 300
3 R&D 1 2004 -90 null null

or

AccountID Description Month Year Estimated Credits Debits
1 Software 2 2004 -100 null -100
2 Hardware 2 2004 -150 null -20
3 R&D 2 2004 null null -300


So I tried the following query:

Select
A.AccountID, A.Description,
F.Month, F.Year,
(Select
Sum(BM.Value)
from
BankMoviment BM
where
BM.AccountID = A.AccountID and
extract(month from BM.Date) = 1 and
extract(year from CB.Data) = 2004 and
BM.Type = 'C'
),
(Select
Sum(BM.Value)
from
BankMoviment BM
where
BM.AccountID = A.AccountID and
extract(month from BM.Date) = 1 and
extract(year from CB.Data) = 2004 and
BM.Type = 'D'
)
from
Account A left join
Forecast F on (F.ForecastID = A.AccountID and F.Month = 1 and F.Year =
2004)


This querie works... But I have hard-coded the month and year into the
statment, so I could not create it as a view, and I cant use this kind of
query inside Crystal Reports, when I have such queries I create a view, and
use the view in Crystal... So I need to find antoher solution... I was
trying to write a query to return something like this:

AccountID Month Year Estimated Value Type
1 1 2004 -100 10 D
1 1 2004 -100 2 C
2 1 2004 -200 300 D
3 1 2004 -90 null null
1 2 2004 -100 100 D
2 2 2004 -150 20 D
3 2 2004 null 300 D

and sumarize the values on the client (Crystal in the case). If I get this
result I do the groups and sums on Crystal Reports.

Trying to write this query I got the error and started to write different
queries until find a pattern. And as I said on the original post, my
thoughts are that the error occurs when field contains null generated by
left join.

Did you try to run the queries I sent in the original post ? You will find
that extract month from null does not generate an error....

I thought using "case", but the productions databases still uses FB 1.0.3...

I am just running 1.5 for test proposes on my develompent machine (WinXP)..
even my main development server runs FB 1.0.3 on Linux

Thanks for the time (and the patience to read my long and bad written message)


Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004


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