Browse statement help

[juhlar]

I will admit that I am not very familiar with VFP, but I need to pull all rows from a table that have no date or time in a specific column. I have tried everything I can think of, however, I don't think I know the correct syntax and commands. When I look at the table the column with no value shows " / / : : AM"

I have gotten some sql commands to work such as:

SELECT * FROM table_name

and

SELECT * FROM table_name WHERE column_name = null (this only returns a blank query for me)

Please help!

Thank you in advance.

 

[bborissov]

SELECT * FROM YorTable;
WHERE NOT EVL(ColumName,NULL) IS NULL

That statement is NOT optimizable.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[juhlar]

That didn't work for me but:

SELECT * FROM YorTable;
WHERE EVL(ColumName,NULL) IS NULL

did

Thank you!!

 

[bborissov]

Sorry, I thought you need the opposite result
I should read your message more carefully.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Dan Freeman]

In VFP, an empty datetime is not null.

SELECT * FROM YorTable;
WHERE Empty(ColumName)

would do the same thing.

Note that this actually has nothing to do with BROWSE, the subject of the thread.

You could also

BROWSE FOR Empty(theColumn)

 

[JRB-Bldr]

The following might give you some hints to an approach

* --- Establish a Variable with an 'Empty' DateTime ---
mdEmptyDate = DTOT(CTOD("  /  /  "))
* --- Confirm it on-screen ---
?mdEmptyDate
* --- Check Various VFP Test Conditions ---
?EMPTY(mdEmptyDate)  && Is it 'EMPTY' ?
?ISNULL(mdEmptyDate)  && Is it NULL ?

You have not said what you want to do with these records.
* Delete them?
* Select them into a record set?
* Fill in some default value?
* What?

Assuming that you want to retrieve a record set you might try something like:

SELECT * ;
   FROM MyTable;
   WHERE EMPTY(DateTimeFld);
   INTO CURSOR NoDates READWRITE

SELECT NoDates
BROWSE

NOTE - The EMPTY() and ISNULL() functions are VFP functions. These will not work 'as is' within most 'alien' backend databases such as SQL Server, etc.
They CAN work on the Results of a SQLEXEC() query, but not within the SQL command itself since it executes within the 'backend' server.

Good Luck,
JRB-Bldr

 

[bborissov]

danfreeman,
EMPTY(Column) and EVL(Column, NULL) IS NULL is not the same.
try this:

CREATE CURSOR crsTest (Fld1 D NULL)
INSERT INTO crsTest VALUES (DATE())
INSERT INTO crsTest VALUES ({})
INSERT INTO crsTest VALUES (NULL)

SELECT * FROM crsTest WHERE EMPTY(Fld1)

SELECT * FROM crsTest WHERE EVL(Fld1, NULL) IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Dan Freeman]

Borislav,

I agree BUT you're assuming the column was created with NULL support. It's far less common than you might think since it isn't the default.

Dan

 

[bborissov]

Try empty dates in SQL Server (or any other RDBMS).
And you will find how quickly you will change your date/datetime fields to support NULL



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Mike Lewis]

You're making this much more difficult than it is - and probably confusing the original questioner into the bargain.

The questioner said:

When I look at the table the column with no value shows " / / : : AM"

So the answer he needs is simply:

SELECT * FROM table_name WHERE EMPTY(column_name)

Nothing more, nothing less.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

What he said.

 

[JRB-Bldr]

It would be nice to hear back from the original question poster to see if they got the answer they needed or not.

juhlar - Good Luck,
JRB-Bldr

 

[juhlar]

I posted up toward the top. The third post. I did get the answer I needed and I also got a secondary way of doing it. Thank you everyone for your help.