Odd behaviour with SQL

[StewartUK]

In VFP 9 SP1, I have the following SQL query:

SELECT obiz.ReturnFinancialYearEnd(DonDate) AS YearEnd, ROUND(SUM(Amount*22/78),2) AS TaxAmount ;
    FROM donation ;
    WHERE dondate>{} AND Claimed={^2007-01-18} ;
INTO CURSOR curTemp2 GROUP  BY 1

The object method ReturnFinancialYearEnd looks like this:

LPARAMETERS ForDate

IF VARTYPE(ForDate)=[D] 
    RETURN IIF(MONTH(ForDate)<10 ;
             , DATE(YEAR(IIF(VARTYPE(ForDate)=[N] ;
                          , DATE()+ForDate;
                          , ForDate)),9,30) ;
             , DATE(YEAR(IIF(VARTYPE(ForDate)=[N] ;
                          , DATE()+ForDate;
                          , ForDate)) +1,9,30))
ELSE
    RETURN IIF(MONTH(DATE())<10 ;
             , DATE(YEAR(DATE())+ForDate,9,30) ;
             , DATE(YEAR(DATE())+ForDate +1,9,30))
ENDIF

I find the above query causes error 11 (Function argument value, type, or count is invalid) in the method code because the parameter is being fed with an empty date.

I don't understand this because the SQL WHERE clause specifies date>{}.

Can someone explain this?

Thanks,

Stewart

 

[StewartUK]

Sorry, I should have said that there are records in table donation with empty DonDate field.

Stewart

 

[bborissov]

Change this:

LPARAMETERS ForDate

IF VARTYPE(ForDate)=[D]

to

LPARAMETERS ForDate

IF VARTYPE(ForDate)=[D]  AND NOT EMPTY(ForDate)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[StewartUK]

Thanks Borislav,

Ah yes, I was coming at this from the wrong direction. I took your suggestion and changed it to...

LPARAMETERS ForDate

IF VARTYPE(ForDate)=[D]
  IF NOT EMPTY(ForDate)
    ....
  ELSE
    RETURN ForDate
  ENDIF

...because changing the first IF test means that ForDate can be of date type and then the code in the ELSE fails because there it expects ForDate to be numeric.

I still don't understand why there was the problem with it in this context because the WHERE command excluded empty dates.

If anyone can correct me on that point, I'd be grateful.

Thanks,

Stewart

 

[Mike Lewis]

Stewart,

Interesting question.

I would guess that it needs to call ReturnFinancialYearEnd() in order to determine the width of the column within the result set, and that it does this before it applies the WHERE clause. That's why your original code failed.

If I'm right, it's definitely worth keeping in mind. I wonder if it works the same way in earlier VFP versions. Do you have any earlier version on your system that you could try it with (just out of curiosity)?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

> WHERE dondate>{} ***

"{}" - May not be working due to Strict date complience. It may also be unpredictable. Run the query with "strict date level" in options set to Off, if it works there is your answer. Though I would use;

Where not Empty(dondate)

 

[bborissov]

Steward,
When you pass empty date the type of the variable is still [D] and the first condition of the IF is satisfied. But when you enter in DATE() function where you create new data, there the program BOMBS. This is because you can't have this:

DATE(0,9,30)

because you always will have:

RETURN DATE(YEAR(ForDate),9,30)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Olaf Doschke]

It's like Mike Lewis says, determining the needed result set structure VFP makes dummy calls to any UDF included. That's where that call with the empty date comes from. This is true for older VFP versions too.

Here's a simple setup to see it working:

create cursor curQueried (iID I)

* version 1
Select debugsql(),* From curQueried

* version 2
Select * From curQueried where debugsql()

procedure debugsql()
   set step on
   return .T.
endproc

It's also a nice way to look behind the scenes of SQL and see what's happening.

curQueried is empty and so the expected behaviour would be, that debugsql() is never called, yet it is.

In version 2 it seems logic, that debugsql() is called, as the sql engine will need to evaluate the where clause, but there is still no record for which the where clause needs to be evaluated.

Bye, Olaf.