Outer Join problems and Calculations with Null Values

[montypython1]

Greetings,

I have linked 2 tables together using "OUTER JOIN", and find that I have 2 problems: (1) the customer number in each joined record shows the last customer # in table 1 (ex: custnum 444); (2) any computations that are being done involving NULL values do not calculate, even if I "SET NULL TO [0]"

Any ideas?

Thanks,
Dave

 

[VolkerStamme]

Dave,
as for 1)
any help requires the SQL code for that
as for 2)
SET NULL TO doesn't exist, there's only SET NULL ON/OFF. Did you mean NVL()?

Volker/

 

[montypython1]

Hi Volker,

You are correct that "SET NULL TO" doesn't exist. I meant to say "SET NULLDISPLAY TO [0.00]".

Also, I should have included my program in my previous question (please see below). In the 2 tables, all calculated fields have been defined as NUMERIC 10 characters wide, with 2 decimal positions:

*********************************************************
** SET NULLDISPLAY TO [0.00] && this does NOT fix the calcs, it just shows 0.00 &&

SELECT table1.*, table2.* ;
FROM table1 ;
LEFT OUTER JOIN table2 ;
ON table1.custnum = table2.custnum ;
ORDER BY table1.custnum ;
INTO CURSOR csrTest

SELECT table1.custnum AS custnumber, price, otherprice1, otherprice2, otherprice3, ;
price + otherprice1 + otherprice2 + otherprice3 AS Calc1, ;
price - cost AS profit ;
FROM csrTest

SET NULLDISPLAY TO

RETURN
*********************************************************

Thanks for any suggestions.
Dave

 

[alvechurchdata]

SET NULLDISPLAY To 0 just makes Fox display a zero instead of displaying Null. It still does its arithmetic with the values stored in the table. Use NVL in the calculations.

As for your first problem, You're saying "SELECT table1.custnum" in the SQL statement when you're trying to pull data from cstTest. The SQL is doing exactly what you've told it to do and it's displaying the current value from table1. Delete the "table1." from the second SQL.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Dave,

SET NULLDISPLAY does not affect the way a SQL statement operates. It only affects what nulls look like in the user interface.

What you should be seeing in your final result set is one or more records for each customer in table 1; if a given customer does not have a matching record in table 2, then there should be one output record for that customer, in which all the table 2 fields are null.

Is that now what you're seeing?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[montypython1]

Hi Geoff and Mike,

Thanks ... The NVL function worked perfectly, but when I tried Geoff's suggestion to delete "table1" from my second SQL (SELECT table1.custnum AS custnumber,....) I receive an error message "SQL: Column CustNum is not found".

Dave

 

[alvechurchdata]

error message "SQL: Column CustNum is not found"

Are you saying that this SQL statement is failing?

[TT]
SELECT custnum AS custnumber, price, otherprice1, otherprice2, otherprice3, ;
price + otherprice1 + otherprice2 + otherprice3 AS Calc1, ;
price - cost AS profit ;
FROM csrTest
[/TT]

Add a Browse after your first SQL statement and check that custnum does exist as a field in csrTest. You are putting two fields named custnum into this cursor, one from table1 and one from table2, and they might be being renamed.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[JimWWinter]

In addition, it would probably improve performance on the first query if you listed just the fields you want to work with in the second query in the SELECT clause instead of the * which will pull all the fields in each table over the wire.

Regards,
Jim

 

[montypython1]

Greetings Jim, Geoff, Mike and Volker,

I just realized that I forgot to thank you for your assistance with my programming issue (I went on vacation and forgot to check Tek-Tips until now). My apologies for the delay.

Thanks,
Dave

 

[alvechurchdata]

vacation

Is this a technical term? I've not seen one recently.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[crewchiefpro6]

My brother takes waaaay to many vacations.

His wife will beat him if he doesn't take one...



Don Higgins

http://www.crewchiefpro.com