append errors on null value

[Judi201]

Hi!

In the process of 'normalizing' my database created from DOS FP free tables, I am creating a table with the structure I want and then SELECTING-SQL to pull data into a temp table. I then use the table with the correct structure to append from the temp table. (because I didn't know how to send it to the new table with different structure.

The customer table is drawing data from two other tables. All goes well untill I append and get the error 'ship via does not accept null values'. Shipvia is pulled from a table by the select and is C(12) there and in the table I am appending to. Browsing the temp table shows data in some shipvia fields and NULL in some. How can I avoid the NULL.

Using VFP 6.0 which I am guessing would matter in doing this.

SELECT DISTINCT Oldbill.*, Oldorder.shipvia,;
       Oldorder.shipfob ; 
   FROM  Oldbill LEFT OUTER JOIN Oldorder ;
   ON  Oldbill.custno = Oldorder.custno ;
   GROUP BY Oldbill.custno ;
   ORDER BY Oldbill.custno ;
   INTO  TABLE  Custtemp
   
***  got temp table with customer info - append to table with new structure   
USE (lcDirBase) + (lcTable)   
APPEND FROM (lcDirOut) + "Custtemp"

I have three fields added that did not exist before. Is there a way that I can use select to put empty fields in the temp table. Then I can do away with the append.[Smile]

Still need to get rid of the NULLs tho.

Any suggestions appreciated.

Judi

 

[Mike Lewis]

Hi Judi,

The easiest way to do this is to change the NULLs to "empty" values before you do the append.

First, check the data types of the fields that contain the NULLs.

Then, add this extra code between the SELECT and the USE / APPEND FROM. Assuming the fields in question are called Field1, Field2, .... and are all character fields:

SELECT CustTemp
REPLACE Field1 WITH "" FOR ISNULL(Field1)
REPLACE Field2 WITH "" FOR ISNULL(Field2)
etc.

If any of the fields is a numeric, change "" to 0 in the above code. If it is a date, change it to {}. And so on.

Of course, this assumes that you want empty fields in place of the NULLs. You really need to ask yourself what the NULLs represent. The reason they are there is because your are doing an outer join. You can avoid the issue by changing that to an inner join, but then you'd have to be sure that was giving the result you want.

Hope this helps.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[bborissov]

SELECT DISTINCT Oldbill.*, NVL(Oldorder.shipvia, SPACE(12) AS shipvia,;
       Oldorder.shipfob ;
   FROM  Oldbill LEFT OUTER JOIN Oldorder ;
   ON  Oldbill.custno = Oldorder.custno ;
   GROUP BY Oldbill.custno ;
   ORDER BY Oldbill.custno ;
   INTO  TABLE  Custtemp
   
***  got temp table with customer info - append to table with new structure   
USE (lcDirBase) + (lcTable)   
APPEND FROM (lcDirOut) + "Custtemp"

Borislav Borissov

 

[bborissov]

Oops, I forgot to close NVL() function, just add one more close bracket at the end of SPACE(12)

Borislav Borissov

 

[Judi201]

Mike, Borislav,

Thanks for the response. You have given me some new functions ,comcepts to think about. I am going to read what I can find and then come back with any problems. Thank you for all of your help. I have so many tables with duplicated data in so many that this is really my biggest problem. Getting a reasonable database to do the job. Thus lots of moving fields from table to table. Dozens of tables with a hundred fields or more duplicating data everywhere. Somethings I wonder why I thought this would be fun, but with the help of this forum, most days it is.

I've got to say that I browse 3 other forums trying to leann and this is the only one I post on because of what I will call 'atmosphere' - friendly and helpful. I have said it before but I must say it again.....You are the best!!

Judi

 

[Mike Lewis]

Judi,

I know what you mean about this forum. I also occasionally visit other VFP-related sites, but this is the one that seems to attract the best questions -- and the best answers, of course.

By the way, Borislav's idea of using NVL() to get rid of NULLs will have the same effect as my suggestion, except that his should be faster. If you do as I suggested, you will need to make an extra pass of the table, which will take time.

Let us know how you get on.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Judi201]

Mike,

I am back to tell you that after spending a hour or two with your and Borislav's replies, "1001 Things....", and "Hacker's Guide", I have tried the following approach. It seems to work. At least on this example it gave me the perfect table the first time through. This is one I can easily verify so I am testing my strategy and will then use it on other situations not so easily verifeid. Please advise if you see any 'gotchas'. I can recover from a weak user interface better than lost data!!

SELECT DISTINCT Oldbill.*, NVL(Oldorder.shipvia, SPACE(12)) AS shipvia,;
       NVL(Oldorder.shipfob, SPACE(12)) AS shipfob, ;
       SPACE(40) AS bemail, NTOM(0) AS crlimit ;
   FROM  Oldbill LEFT OUTER JOIN Oldorder ;
   ON  Oldbill.custno = Oldorder.custno ;
   GROUP BY Oldbill.custno ;
   ORDER BY Oldbill.custno ;
   INTO  TABLE  Customer

I am excited about this because it seems that I can include this in the install and very effectively take care of what was bothering me most.

At one time, I would have gone on site to do this kind of stuff off the top of my head; not any more. Older, wiser, lazier, worn out ... take your pick. <g>

Thank you does not really cover it.

Judi

Afterthought: I use the Query Designer to help me write the queries (I love these queries!) This is the only way I could see to get ALL of the bill file with matching info. Am I right in this??

J

 

[Mike Lewis]

Judi,

Looks good to me. Let's see what Borislav says.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[bborissov]

Looks good to me too, one simple note, there is no need to convert 0 o money, becuase you could append numeric/integer data to money field w/o explicit convertion.
On the other hand this convertion make code more redable, because you always know what type of field you expext in other table. I am not sure if this will speed you code but you could test:

m.lmMoney = NTOM(0)
m.lcSpace = SPACE(40) ???

SELECT DISTINCT Oldbill.*,;
       NVL(Oldorder.shipvia, SPACE(12)) AS shipvia,;
       NVL(Oldorder.shipfob, SPACE(12)) AS shipfob,;
       m.lcSpace                        AS bemail,;
       m.lmMoney                        AS crlimit ;
   FROM  Oldbill;
LEFT JOIN Oldorder ON  Oldbill.custno = Oldorder.custno ;
GROUP BY Oldbill.custno ;
ORDER BY Oldbill.custno ;
INTO  TABLE  Customer

BTW this SELECT will run only on VFP 6 or 7, in bigger version you must put SET ENGINEBEHAVIOUR 70 before it, becuase of GROUP BY clause.

Borislav Borissov

 

[Judi201]

Hi!

Thanks for the feedback.

<< BTW this SELECT will run only on VFP 6 or 7 >>

I have run into that before. What do I need to do for it to run in later verwions. How should I change GROUP BY? I know I have seen that but can't remember.

Thanks for all.

Judi

 

[bborissov]

VFPT made changes to SELECT statemet so it to be more ANSI compatible. Whan you have GROUP BY you must include in it ALL non agregate fields from SELECT.

Borislav Borissov

 

[Mike Lewis]

Judi,

What do I need to do for it to run in later verwions.

The easy answer is SET ENGINEBEHAVIOR 70.

The better answer is to include all the expressions in the SELECT list (that is, the two NVLs and the other two fields) in the GROUP BY clause.

The are some other solutions as well. Tamar Granor wrote an illuminating article on exactly this issue in the August 03 issue of FoxPro Advisor, which you should read if you have it. If Tamar sees this post, I'm sure she will give you a better answer than I can.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Judi201]

In VFP 8.0 and above, the rule is: if you do grouping, the expressions in the SELECT list must be either the fields that take part in the grouping or aggregate functions. So, you can do grouping without aggregates, provided you are grouping on all the columns in the result set.

Pretty clear to me, Mike. I found this after reading the replies from you and Borislov. This illustrates why I am amazed at the patience displayed on this forum. The answer is usually here, but it takes the right keyword to find them. I am spending some time studying before going forward with this.

Many thanks to you both.

Judi