Home > Themen 200001 - 300000 > Themen 274001 - 276000 > Themen 275081 - 275120 > NULL with HF

NULL with HF

Startbeitrag von Jeff Grahamam 31.12.2009 17:10

Hello WinDevers,

I have enabled NULLs in a file and set the default to NULL on a date field. However, if I do a HReadSeek.. or HFilter with NULL as any of the values, at execution I get an error saying NULL is not allowed.

Also, comparisons to NULL are not valued correctly. It seems you have to use "" as the value in place of NULL. Is this only a date problem or does WinDev just not handle NULL well in general.

Any suggestions or comments would be appreciated. I have sent a request to PC Soft as well.

Thanks,

Antworten:

Geoff,

My experience when searching on NULL is that I have to use MyValue..ISNULL=True rather than MyValue=NULL

HTH

Milton

von Milton - am 31.12.2009 19:49

Hi Milton,

I can't find any reference to an ISNULL property in the help except with SQL Language.

I also tried ...ISNULL and it is not recognized. It gives and error of "ISNULL property not found".

Of course that does not solve the problem for specifying NULL in HFReadSeek.. value fields.

If that property were available, that would be similar to our previous development language that handled Nulls very well.

Thanks for the response,

von Jeff Graham - am 01.01.2010 14:49

Hello Jeff

There is a ..Null property. That might be what Milton was referring to.

Regards
Al

von Al - am 02.01.2010 03:40

Hi Jeff and Thanks Al,

Sorry - I was away on holidays and repleid off the top of my head - I am now back home and checked my code, an example below -

tblCustomers.cusRsnID..Null=True

I found I had to use this when working with MSSQL tables rather than using tblCustomers.cusRsnID=NULL

otherwise it would not work when trying to write to the MSSQL table - I guess therefore that using Hyperfile SQL would be much the same.

Milton

von Milton - am 02.01.2010 05:39

Hi Milton and Al,

It sure seems that should work with HF as well, but my quick tests indicate that it does not value the ..NULL property correctly. I will have to test more and see why using = "" works but ..NULL = true does not.

I did test more and found I had a field linked to the date without the 'Null if empty' checked. That was messing up the data. It works as expected now.

HOWEVER, I still need a way to do HFilter and HReadSeek on the index of dates to select those with NULL property. What is the best way to do that?

Also, are records with NULL property at the low or high end of the index? Are they in the index at all?

I will do some tests and post the findings for those that might be interested.

Thanks for your help,

von Jeff Graham - am 03.01.2010 17:09

Hi to those interested,

I did a test project and the indexed date fields do have all records, NULL or not.

Dates with ..NULL = True are at the low end, i.e. first if ascending. The value is "".

I did not know that you can assign null value in WDMap with a right click on the field. That is useful.

Another useful item is that FieldError(ceNull,"Null Value") will show the string "Null Value" in controls that have ..NULL = True, event numeric and date fields. Useful for testing as it is program wide when specified.

Cheers,

von Jeff Graham - am 04.01.2010 19:39

Hello Jeff,

I do not know if the is relevant but if you are trying to find a null date couldn't you just do a search on a date field less then 0001101 or so?

von DW - am 04.01.2010 20:58

Thanks Jeff,

Milton

von Milton - am 05.01.2010 03:27

I don't know how NULLs are treated in HF but be careful when working with more than one kind of database engine, they treat nulls in diferent ways.

For instance in MSSQL NULL is the smallest values but in ORACLE is the bigest.

If you use MSSQL or ORACLE in the when clause you have to use field is null instead of field=""

von Paulo Oliveira - am 05.01.2010 16:44

I have been told by PC Soft support:

Quote
Patrick PHILIPOT
Your best option is to use an SQL query with "IS NULL" or with "IS NOT NULL". HFilter, HReadSeek do not handle the NULL value.

The Query Editor does support the conditions "IS NULL" and "IS NOT NULL" so that is the way I will go for reporting and more complex selections.

However, since my testing shows that null values always have a defined value, you can use that value with HFilter and HRead... functions. Then you have to further exclude/include records based on the ..NULL property. For example:



		HFilter(Invoice,PostedDate,"","")  // NULL dates are always ""

		HReadFirst(Invoice,PostedDate)

                // Check for any unposted Invoices

		IF HFound(Invoice) AND Invoice.PostedDate..Null = True THEN 

			Open(WIN_Table_Invoice)

		ELSE // Default to a NEW Invoice

			HReset(Invoice)

		END

		Open(WIN_Receiving)

		HDeactivateFilter(Invoice)

This should also be independent of whether the data base engine treats the NULL as low or high value as Paulo pointed out.

Thanks to all for the comments and suggestions,

von Jeff Graham - am 05.01.2010 18:22

Jeff
I could be wrong here, not what you would call an SQL guru, but I believe that your example query is actually woking on the date being " " (spaces) and not NULL.

I played around some time ago with MySQL and this issue and this was my conclusion then.

The reason for Paulos caution and Patrick's reply is expanded in this snippet........



SELECT  id, fname, lname from CLIENT

  where lname = NULL

When ANSI_NULL setting is set to ON, the ANSI SQL-92 standard states that any equal (=) or non equal () statement must equate to FALSE when compared against a NULL. For this reason the WHERE clause in the above statement will always be false when the ANSI_NULL setting of a database or connection is ON. To find all the CLIENTS who have a NULL LNAME you need to use the IS NULL expression, like so when the ANSI_NULL setting is ON:

I found that queries were the way to go.

Google Null Values, enough material to keep you occupied for weeks.

Regards

DerekT

von DerekT - am 05.01.2010 19:03

Hi Derek,

Yes, that ANSI standard is what I am used to as well. However, I have been experimenting with HF only and found that the code I gave for HFilter, HReadFirst does work. If PC Soft changes the HFilter to exclude all records with NULL property, then that would no longer work. The HFilter would then only return non-Null dates with "" as the value which is allowed as a value.

In my experience, records with NULL property would not even be in the index allowing the creation of a "sparse index". However, PC Soft's HF engine does not work that way.

The problem arises from the fact that all the HFilter and HRead... functions do not support NULL value in any way. The best solution would be to add that support to those functions. I will make that suggestion and reference this thread.

Thanks,

von Jeff Graham - am 06.01.2010 15:18

->>Weiter mit dem kompletten Thema (13 Beiträge) unter NULL with HF

Diese Seite auf Ihrer Homepage verlinken: Verwenden Sie dazu einfach folgenden Code:
<a href="http://www.mysnip.de/forum-archiv/thema-27131-43860/NULL+with+HF.html">NULL with HF</a>

Meistgelesen:

NULL with HF

Startbeitrag von Jeff Grahamam 31.12.2009 17:10