need help with error in view

[Nifrabar]

Hi,
This is my view

SELECT Tcontactpers.ncont_id AS id, Tcontactpers.cpgn AS bsn,;
  PADR(combineer(Taanhef.aanhef,Tcontactpers.cvnaam,IIF(.NOT.EMPTY(Tcontactpers.crnaam),"[",""),Tcontactpers.crnaam,IIF(.NOT.EMPTY(Tcontactpers.crnaam),"]",""),Tcontactpers.ctnaam,Tcontactpers.canaam),40) AS naam,;
  Tcontactpers.dgebdatum AS gebdat,;
  leeftijd(Tcontactpers.dgebdatum,DATE()) AS leeftijd,;
  ICASE(Tcontactpers.cgeslacht="M","Man     ",Tcontactpers.cgeslacht="V","Vrouw   ","Onbekend") AS geslacht,;
  IIF(Tcontactpers.noptnl=1,"In NL-pcd",SPACE(9)) AS nlpcd,;
  PADR(combineer(IIF(Tcontactpers.noptnl=1,Straat.straat,Tcontactpers.cstraat),Tcontactpers.chuisnum,Tcontactpers.chuislet,Tcontactpers.chuistoe),40) AS adres,;
  PADR(IIF(Tcontactpers.noptnl=1,Tcontactpers.cpcd,Tcontactpers.cpcdint),10) AS pcd,;
  PADR(IIF(Tcontactpers.noptnl=1,NVL(Plaats.plaats,""),Tcontactpers.cplaats),30) AS plaats,;
  NVL(Plaats.provincie,SPACE(15)) AS provincie,;
  NVL(Tland.land,SPACE(20)) AS land,;
  PADR(ALLTRIM(Tcontactpers.ctelnetnum)+"-"+ALLTRIM(Tcontactpers.ctelefoon),12) AS telefoon1,;
  Tcontactpers.cgsm AS mobiel, Tcontactpers.cemail AS email,;
  Tcontactpers.curl AS www, Tcontactpers.crekening AS rekening,;
  PADR(NVL(Tkenmerk.ckenmerk,SPACE(25)),25) AS kenmerk,;
  Tcontactpers.nstatus AS cont_status,;
  NVL(Taanhef.nstatus,0) AS aanhef_status,;
  NVL(Tland.nstatus,0) AS land_status,;
  NVL(Tkenmerk.nstatus,0) AS kenmerk_status,;
  NVL(Tprintpref.cprintpref,SPACE(20)) AS prnprflijst,;
  NVL(Tprintpref_a.cprintpref,SPACE(20)) AS prnprfetiket,;
  Tcontactpers.copmerking AS opmerking,;
  IIF(Tcontactpers.noptnl=1,.T.,.F.) AS nlpcdgebied,;
  T_cont_ken.ncont_id;
 FROM ;
     dbc_prive!tcontactpers ;
    LEFT OUTER JOIN dbc_prive!taanhef ;
   ON  Tcontactpers.naanhef = Taanhef.aanhef_id ;
    LEFT OUTER JOIN dbc_prive!tprintpref Tprintpref_a ;
   ON  Tcontactpers.ndisplijst = Tprintpref_a.printpref_id ;
    LEFT OUTER JOIN dbc_prive!tprintpref ;
   ON  Tcontactpers.ndisplabel = Tprintpref.printpref_id ;
    LEFT OUTER JOIN plaats ;
   ON  Tcontactpers.nplaats = Plaats.plaatsid ;
    LEFT OUTER JOIN straat ;
   ON  Tcontactpers.nstraat = Straat.straatid ;
    LEFT OUTER JOIN dbc_prive!tland ;
   ON  Tcontactpers.nland = Tland.landid ;
    LEFT OUTER JOIN dbc_prive!t_cont_ken ;
   ON  Tcontactpers.ncont_id = T_cont_ken.ncont_id ;
    LEFT OUTER JOIN dbc_prive!tkenmerk ;
   ON  T_cont_ken.nkenm_id = Tkenmerk.nkenm_id;
 WHERE  Tcontactpers.ncont_id = ( ?lnCont_ID );
   AND  (  Tkenmerk.nstatus <> ( 2 );
   OR  Tkenmerk.nstatus IS NULL )

When I skip a record (and requiry) there pops up an error saying view definition has been changed.
I tried with many NVL's and PadR's to keep all as it should be.
Did I overlook some?

TIA
-Bart

 

[Olaf Doschke]

I spot several fields you neither NVL() nor PADR().

You're maybe sure those fields don't contain any NULLs or are expression resulting ion varying lengths. But varchars could be a culprit in todays tables.

Try to put NVL() everywhere, even where you think it's not needed.

And you could try NVL(Taanhef.nstatus,Cast(0 as int)) for example, to make it a 0 integer, and not a N(1) numeric field. Depending on the type of the source field. In such situations the pure could also be handled better, as a NULL coming directly from a field still is keeping its original field type, while NVL() is an expression resulting in a new field derived by the first value.

Bye, Olaf.

 

[markros]

Does the view work OK if you get rid of the first UDF? E.g. keep all fields except for the first one?

Having UDF in a view most likely leads to the problem you mentioned.

PluralSight Learning Library

 

[Mike Lewis]

Having UDF in a view most likely leads to the problem you mentioned.

It looks like the UDF is merely calculating the person's age from their date of birth. That should be OK. In general, UDFs in views only give problems when they do things with tables or cursors.

Then again, you might possibly be right, Markros. I won't try to second guess it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[markros]

Looking a bit closer I see that there are several UDFs in this view. For the start, I'll get rid of all of them and verify, if the problem persists.

PluralSight Learning Library

 

[Dan Freeman]

The other problem with UDFs in a view definition is visibility of the UDF. If VFP can't find the UDF when you SKIP or requery(), you'll probably see this error.

 

[Nifrabar]

re the UDF combineer(); I used that many times before without any problem.
First I'm going to give Olaf's solution a try.
If that does not solve the error than I will copy the view and dismantle it 'field by field' to find out what's causing this prob.

Anyway thanks for your time.

I will postback the results.

-Bart

 

[Koen Piller]

Hi,

Please show us the code of your UDF combineer, it seems it does not do anything remarkable than concatating two of more fields into one, should be possible to apply the same in your select statement.

Why do you address the tables plaats and straat in a different way? All others are addressed with the DBC, these two not. Basicly it should not matter, just wondering.

Regards,
Jockey(2)

 

[Nifrabar]

Jockey(2)

Pls find hereunder my code for function combineer()

FUNCTION combineer
LPARAMETERS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12
LOCAL lc1,lc2,lc3,lc4,lc5,lc6,lc7,lc8,lc9,lc10,lc11,lc12, lcReturnValue
lc1 	= NVL(IIF(!EMPTY(t1) ,ALLTRIM(t1) +" ", ""),"")
lc2 	= NVL(IIF(!EMPTY(t2) ,ALLTRIM(t2) +" ", ""),"")
lc3 	= NVL(IIF(!EMPTY(t3) ,ALLTRIM(t3) +" ", ""),"")
lc4 	= NVL(IIF(!EMPTY(t4) ,ALLTRIM(t4) +" ", ""),"")
lc5 	= NVL(IIF(!EMPTY(t5) ,ALLTRIM(t5) +" ", ""),"")
lc6 	= NVL(IIF(!EMPTY(t6) ,ALLTRIM(t6) +" ", ""),"")
lc7 	= NVL(IIF(!EMPTY(t7) ,ALLTRIM(t7) +" ", ""),"")
lc8 	= NVL(IIF(!EMPTY(t8) ,ALLTRIM(t8) +" ", ""),"")
lc9 	= NVL(IIF(!EMPTY(t9) ,ALLTRIM(t9) +" ", ""),"")
lc10 	= NVL(IIF(!EMPTY(t10),ALLTRIM(t10)+" ", ""),"")
lc11 	= NVL(IIF(!EMPTY(t11),ALLTRIM(t11)+" ", ""),"")
lc12 	= NVL(IIF(!EMPTY(t12),ALLTRIM(t12)+" ", ""),"")

lcReturnValue = lc1+lc2+lc3+lc4+lc5+lc6+lc7+lc8+lc9+lc10+lc11+lc12

RETURN lcReturnValue

plaats and straat where both initially free tables.
While trying to solve this prob I also put them in teh DBC.
These do contain data which may be updated on regular base.

-Bart

 

[Mike Lewis]

Bart,

There should be no problem with that UDF. As I said earlier, UDFs in views are generally only a problem if they do things with tables or cursors (including opening or selecting a different table, switching data sessions, and so on).

That said, it would be a good idea to temporarily remove all the UDFs to see if the problem goes away.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Nifrabar]

Hi!
Problem solved.
In fact there where two errors in the view definition.

NVL(Plaats.provincie,SPACE(15)) AS provincie,;
Here I made a silly fault as in table plaats 'provincie' is a C(13)

2nd error was solved by Olaf's solution,
by changing:
NVL(Taanhef.nstatus,0) AS aanhef_status,; NVL(Tland.nstatus,0) AS land_status,; NVL(Tkenmerk.nstatus,0) AS kenmerk_status,;

into

NVL(Taanhef.nstatus,Cast(0 as int)) AS aanhef_status,; NVL(Tland.nstatus,Cast(0 as int)) AS land_status,; NVL(Tkenmerk.nstatus,Cast(0 as int)) AS kenmerk_status,;

Thanks for your advices!

-Bart

 

[Olaf Doschke]

In case of provincie fsize() may have helped keeping the size to the table definition, but fsize() behaviour depends on SET compatibility On/Off.

PADR() may be better in the perspective the field size may be altered, eg. PADR(NVL(Plaats.provincie,'),15) would have worked, too.

All the NVLs are not needed to make the view cursor schema unambigous for the main table fields, only for the joined table field, as you do outer joins. I even wonder if vfp wouldn't nevertheless take their field type as the view schema field type, so the NVLs are even not needed for joined tables. The NVLs just change NULLs to default values in case outer joins does find no join, do you really need that?

Bye, Olaf.

 

[Nifrabar]

Olaf,

I tend to use NVL's wherever Null's might happen.
So, if I have done right, these are only used for related tables.
tContactPers is the main table.

Using NVL's was adviced here once in this forum some years ago.

-Bart

 

[Olaf Doschke]

Yes, it's ok, but you can also work with NULLs on the frontend. It's not a must to suppress NULLS just because of the view schema problem, it is a solution, but as you see it has it's pitfalls, too.

You might also want an inner join to some of the tables you all outer join, but I can't decide that. Your number of joins is just quite overwhelming in a way.

Bye, Olaf.