.NULL. Values after a select statement 1

[bebbo]

I have the following select statement which appears to work ok. However I do appear to have some .NULL. values in Totlevy.TotSpent. What would represent. Does it mean zero ??

Select TmpLevy2.TmpNum, ;
TmpLevy2.TotCredit, ;
TmpSpent1.TotSpent ;
From TmpLevy2 ;
LEFT OUTER JOIN TmpSpent1 ;
ON TmpLevy2.TmpNum = TmpSpent1.Cardnum ;
INTO CURSOR TOTLEVY ;
Order by TmpNum

 

[monikai]

The records are in table TmpLevy2 but aren't in the second table (TmpSpent1)

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[bebbo]

Thanks

Can this be a numeric value of zero ? Or how would I account for it. Basically I need TotCredit - TotSpent value.

If I do TotCredit - TotSpent and there is a NULL value in TotSpent the answer comes to NULL. The answer should be the value in TotCredit.

 

[ramani]

Hi

.NULL. is emptiness, it is not even zero or empty. It is equivalent to vacuum. Zero has value 0.

But for practical purpose, you can treat its value as 0, if numeric and empty spaces if char type field.

You can display its value using the function
NVL(myField,0) if you want 0 in the place of NULL values.



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[monikai]

to check field uae function ISNULL(totspent)
but when you do: TotSpent+value=.NULL. (where TotSpent=.NULL.)

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[bebbo]

Thanks

However can I change the select statement to enter "0" instead of .NULL. Or would I have to go throught the table after the select statement to change .NULL. Values?

 

[bebbo]

Hi

I'm being really daft here. I can't appear to replace the NULL valuse with 0. How would I do it?

 

[bebbo]

Even if I use a filter such as

SET FILTER to ISNULL(Totspent) = .T.
replace TotSpent with 0 ALL

I get the following message "cannot update the cursor"

 

[jimoo]

2 parts.

1. NULL is not empty, blank, false, or zero. It is NULL. It takes a bit to get used to.

2. It appears your SQL statement is VFP SQL accessing VFP tables. You can use an inline-if (IIF) to edit the NULL data.

SELECT ee.name, IIF(ISNULL(ee.address),"",ee.address), dob FROM ee ORDER BY ee.name

* ee = employee

 

[bebbo]

Thanks

As you replied I was just trying the following and it worked.

Select TmpLevy2.TmpNum, ;
TmpLevy2.TotCredit, ;
IIF(ISNULL(Totspent),0.00, TmpSpent1.TotSpent) AS TotSpent , ;
IIF(ISNULL(Totspent),TmpLevy2.TotCredit,(TmpLevy2.TotCredit ;
- TmpSpent1.TotSpent)) AS Deduct ;
From TmpLevy2 ;
LEFT OUTER JOIN TmpSpent1 ;
ON TmpLevy2.TmpNum = TmpSpent1.Cardnum ;
INTO CURSOR TOTLEVY ;
WHERE TotCredit > TotSpent ;
Order by TmpNum

 

[jimoo]

Great. On other tip to remember is once you use function on the SQL statement you cannot sort on it unless you tell its postion is the SQL Statement.

If you wish to sort by totalspend, you would have to change the Order by to 2

Select TmpLevy2.TmpNum, ;
TmpLevy2.TotCredit, ;
IIF(ISNULL(Totspent),0.00, TmpSpent1.TotSpent) AS TotSpent , ;
IIF(ISNULL(Totspent),TmpLevy2.TotCredit,(TmpLevy2.TotCredit ;
- TmpSpent1.TotSpent)) AS Deduct ;
From TmpLevy2 ;
LEFT OUTER JOIN TmpSpent1 ;
ON TmpLevy2.TmpNum = TmpSpent1.Cardnum ;
INTO CURSOR TOTLEVY ;
WHERE TotCredit > TotSpent ;
Order by 2

The same is true with all other functions including date converters and if you use the AS statement on a field in the SQL statement.

Jim Osieczonek
Delta Business Group, LLC
Lansing, MI

http://www.deltabg.com

jimo@deltabg.com

[Independent company and always looking for work]

 

[craigsboyd]

Use the NVL() funciton instead:


Select TmpLevy2.TmpNum, ;
TmpLevy2.TotCredit, ;
NVL(Totspent,0.00) AS TotSpent, ;
TmpLevy2.TotCredit ;
- NVL(TmpSpent1.TotSpent,0) AS Deduct ;
From TmpLevy2 ;
LEFT OUTER JOIN TmpSpent1 ;
ON TmpLevy2.TmpNum = TmpSpent1.Cardnum ;
INTO CURSOR TOTLEVY ;
WHERE TotCredit > NVL(TmpSpent1.TotSpent,0) ;
Order by TmpNum

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]