Populate Missing Value in View

[Auguy]

The following is part of a local view. I'm trying to populate the Process (a description) even when there is no matching record in the process table. 20 is the length of the process field in the process table. This works, but I would like to is have the "SPACE(20)" replaced by something dynamic so that if the length of the process field changes the view doesn't have to be changed. I originally had

""

where the SPACE(20) is, but that caused an error (The view has changed) whenever the missing process was the first part number. I hope I have explained this well enough that you can understand my problem.

SELECT Shippart.shippartpk, Shippart.shipfk, Shippart.linenumber, ;
  IIF(ISNULL(Process.process),SPACE(20),Process.process) AS process ;
 FROM ;
    DATAFILES!SHIPPART ;
    LEFT OUTER JOIN datafiles!process ;
    ON Shippart.processfk = Process.procpk ;
 WHERE  Shippart.shipfk = ( ?ViewPK );
 ORDER BY Shippart.linenumber

Auguy
Sylvania/Toledo Ohio

 

[Dan Freeman]

Instead of Space(20), how about Space(Len(process.process))?

To be honest, I wouldn't use IIF() here. Instead, use:

NVL(Process.process,SPACE(Len(Process.Process)))

It's a little easier to read.

 

[Auguy]

I tried Len(Process.Process) but it didn't work, gave an error (View definition has been changed). I just tried your suggestion and I get the same error.

Auguy
Sylvania/Toledo Ohio

 

[JRB-Bldr]

One problem with Views is that they are built on rigid criteria based on the 'real' data table(s). When something changes in the 'real' table, that needs to be reflected in the View.

If you get the error message: View definition has been changed then you will need to rebuild the View to match the new definition.

Good Luck,
JRB-Bldr

 

[Auguy]

Thanks to both of you. I've pretty much come to the conclusion that I will have to rebuild views whenever the structures change. Fortunately that doesn't happen very often.


Auguy
Sylvania/Toledo Ohio

 

[Dan Freeman]

There are a couple of options to handle that, too.

1) Run gendbc.prg (in the VFP tools folder) against your DBC. It'll generate code to create your database and the schema it contains. Copy the code for your view into a maintenance program, and when your data changes you only need to change/run that prg.

2) Or (a much more sensible way to deal with schema changes), get Stonefield's SDT (

http://www.stonefield.com)

and make ALL data changes through metadata in the first place.

 

[Auguy]

Thanks Dan, I'm already using your 1st suggestion and have used it on my test data and at the client's site quite a few times. Works great.

Auguy
Sylvania/Toledo Ohio