Erik,

No, generally I have an initial updatable view, one or more intermediate views, and a final display view.

In the following lv_OpenInventoryForPatient code, I get information regarding vaccines due based on several conditions.

In addition I need to add a iPriority field to the table to further sort the results. but since the 10 join has been reached, a create a new SQL.

The lv_OpenInventoryForPatientAddPriority view is the "broken" one. If I use the view, all is well. If I open the view NODATA and later REQUERY it, it gives me the above message.

Could it be the ISNULL function? If so, is there another way to keep the .NULL.s out of the view?

TIA

Rex

*-------------------------------------------------------------------------------

CREATE SQL VIEW "lv_OpenInventoryForPatient" ;
AS ;
SELECT ;
UAlias.cName AS cAlias,;
UAlias.iId AS iInventoryAliasId,;
SVaccine.iId AS iVaccineId, ;
SVaccine.cName AS cVaccine, ;
SVacGrou.iId AS iVaccineGroupId,;
SVacVacG.iId AS iVaccineVaccineGroupId,;
UVGrpDue.iNextDose AS iDose,;
UVGrpDue.dLastDose AS dMinInterval,;
UVGrpDue.dLastDose AS dRecInterval,;
UVGrpDue.dLastDose AS dODInterval,;
UVGrpDue.iSeriesId,;
UInvLot.cLotNumber,;
UInvLot.dExpiration,;
UInvLot.iId AS iInventoryLotId,;
SVacGrou.iOrder,;
SVacGrou.cName AS cVaccineGroup ;
FROM ;
Impact!UInvntry ;
INNER JOIN Impact!UInvDet ;
ON UInvntry.iId = UInvDet.iInventoryId ;
INNER JOIN Impact!UInvLot ;
ON UInvLot.iId = UInvDet.iInventoryLotId ;
INNER JOIN Impact!UAlias ;
ON UAlias.iId = UInvLot.iInventoryAliasId ;
INNER JOIN Impact!SBrand ;
ON SBrand.iId = UAlias.iBrandId ;
INNER JOIN Impact!SManVac ;
ON SManVac.iId = SBrand.iManufacturerVaccineId ;
INNER JOIN Impact!SVaccine ;
ON SVaccine.iId = SManVac.iVaccineId ;
INNER JOIN SVacVacG ;
ON SVaccine.iId = SVacVacG.iVaccineId ;
INNER JOIN SVacGrou ;
ON SVacGrou.iId = SVacVacG.iVaccineGroupId ;
INNER JOIN UVGrpDue ;
ON UVGrpDue.iVaccineGroupId = SVacGrou.iId ;
WHERE ;
NOT UInvLot.lComplete AND ;
?vp_dNextDue BETWEEN UInvntry.dTransaction AND UInvLot.dExpiration AND ;
?vp_dNextDue BETWEEN SBrand.dAvailable AND SBrand.dExpiration AND ;
?vp_iPatientAge BETWEEN SBrand.iMinAge AND SBrand.iMaxAge AND ;
UVGrpDue.iPatientId = ?vp_iPatientId AND ;
! EMPTY(UVGrpDue.dNextDue) AND ;
UVGrpDue.dNextDue <= ?vp_dNextDue

*-----------------------------------------------------------------------------
CREATE SQL VIEW "lv_OpenInventoryForPatientAddPriority" ;
AS ;
SELECT ;
v_OpenInventoryForPatient.cAlias,;
v_OpenInventoryForPatient.iInventoryAliasId,;
v_OpenInventoryForPatient.iVaccineId,;
v_OpenInventoryForPatient.cVaccine,;
v_OpenInventoryForPatient.iVaccineGroupId,;
v_OpenInventoryForPatient.iVaccineVaccineGroupId,;
v_OpenInventoryForPatient.iDose,;
v_OpenInventoryForPatient.dMinInterval + IIF(ISNULL(SDose.iMinInterval),0,SDose.iMinInterval) AS dMinInterval,;
v_OpenInventoryForPatient.dRecInterval + IIF(ISNULL(SDose.iRecInterval),0,SDose.iRecInterval) AS dRecInterval,;
v_OpenInventoryForPatient.dODInterval + IIF(ISNULL(SDose.iODInterval ),0,SDose.iODInterval ) AS dODInterval,;
v_OpenInventoryForPatient.iSeriesId,;
v_OpenInventoryForPatient.cLotNumber,;
v_OpenInventoryForPatient.dExpiration,;
v_OpenInventoryForPatient.iInventoryLotId,;
v_OpenInventoryForPatient.iOrder,;
v_OpenInventoryForPatient.cVaccineGroup,;
IIF(ISNULL(UVGPref.iPriority),10,UVGPref.iPriority) AS iPriority ;
FROM ;
v_OpenInventoryForPatient ;
LEFT JOIN UVGPref ;
ON UVGPref.iVaccineVaccineGroupId = v_OpenInventoryForPatient.iVaccineVaccineGroupId AND ;
UVGPref.iDose = v_OpenInventoryForPatient.iDose ;
LEFT JOIN SDose ;
ON SDose.iSeriesId = v_OpenInventoryForPatient.iSeriesId AND ;
SDose.iDose = v_OpenInventoryForPatient.iDose - 1


*-----------------------------------------------------------------------

>>Yep,
>>
>>10 Joins max
>>
>>What a bummer!
>>
>>Rex
>>
>>>>Erik,
>>>>
>>>>Well the first reason is I need to have more tables joined than is allowed in a single SQL.
>>>>
>>>
>>>I didn't know that there was a limit. Are you saying that the combined SQL would be too long?
>
>Do you have to update from the resulting cursor?