>>CREATE SQL VIEW "LV_VACCINEBRAND" ;
>>AS ;
>> SELECT ;
>> Imc_bran.iid, ;
>> Imc_bran.imanufacturervaccineid, ;
>> Imc_bran.cname, ;
>> Imc_bran.dAvailable, ;
>> Imc_bran.iminage, ;
>> Imc_bran.imaxage, ;
>> Imc_bran.imaxdose, ;
>> Imc_bran.imindose, ;
>> Imc_mava.imanufacturerid, ;
>> Imc_mava.ivaccineid, ;
>> Imc_manu.cname AS cmanufacturername ;
>> FROM ;
>> impact!imc_mava ;
>> INNER JOIN impact!imc_bran ;
>> INNER JOIN impact!imc_manu ;
>> ON Imc_manu.iid = Imc_mava.imanufacturerid ;
>> ON Imc_mava.iid = Imc_bran.imanufacturervaccineid ;
>> WHERE ;
>> Imc_Bran.iManufacturerVaccineid = ?vp_iManufacturerVaccineId
>
>Are these all tables or are any of them a view? Basing views on views can be a little tricky sometimes. If not, then I can't see a reason why you get this error.
>
>Doug

Doug,

Below is the code for 2 files, IMPACT.PRG and MAKEVIEW.PRG.
Copy both files in an empty directory and run IMPACT.PRG.
Run MAKEVIEW.PRG
Try to edit or run LV_VaccineBrand
This view was created via the View Designer.
As you can see, even programmatically these views are a problem.

Any ideas?

Rex

IMPACT.PRG

* *********************************************************
* *
* * 10/01/99 IMPACT.DBC 14:37:10
* *
* *********************************************************
* *
* * Description:
* * This program was automatically generated by GENDBC
* * Version 2.26.67
* *
* *********************************************************


DisplayStatus([Creating database...])
CLOSE DATA ALL
CREATE DATABASE 'IMPACT.DBC'
DisplayStatus([Creating table SBRAND...])
MakeTable_SBRAND()
DisplayStatus([Creating table SCPT...])
MakeTable_SCPT()
DisplayStatus([Creating table SDRUG...])
MakeTable_SDRUG()
DisplayStatus([Creating table SMANUFAC...])
MakeTable_SMANUFAC()
DisplayStatus([Creating table SMANVAC...])
MakeTable_SMANVAC()
DisplayStatus([Creating table SVACCINE...])
MakeTable_SVACCINE()
DisplayStatus([Creating table UALIAS...])
MakeTable_UALIAS()
DisplayStatus([Creating persistent relations...])
MakeRelation_1()
MakeRelation_2()
MakeRelation_3()
MakeRelation_4()
MakeRelation_5()
MakeRelation_6()
DisplayStatus([Finished.])


********* Procedure Re-Creation *********
IF !FILE([IMPACT.krt])
? [Warning! No Procedure File Found!]
ELSE
CLOSE DATABASE
USE 'IMPACT.DBC'
g_SetSafety = SET('SAFETY')
SET SAFETY OFF
LOCATE FOR Objectname = 'StoredProceduresSource'
IF FOUND()
APPEND MEMO Code FROM [IMPACT.krt] OVERWRITE
REPLACE Code WITH SUBSTR(Code, 70, 18376)
ENDIF
LOCATE FOR Objectname = 'StoredProceduresObject'
IF FOUND()
APPEND MEMO Code FROM [IMPACT.krt] OVERWRITE
REPLACE Code WITH SUBSTR(Code, 18446)
ENDIF
SET SAFETY &g_SetSafety
USE
OPEN DATABASE [IMPACT.DBC]
ENDIF

FUNCTION MakeTable_SBRAND
***** Table setup for SBRAND *****
CREATE TABLE 'SBRAND.DBF' NAME 'SBRAND' (IID I NOT NULL DEFAULT newid(), ;
IMANUFACTURERVACCINEID I NOT NULL, ;
CNAME C(20) NOT NULL, ;
DAVAILABLE D NOT NULL, ;
IMINAGE I NOT NULL, ;
IMAXAGE I NOT NULL, ;
IMINDOSE I NOT NULL, ;
IMAXDOSE I NOT NULL)

***** Create each index for SBRAND *****
SET COLLATE TO 'MACHINE'
INDEX ON CNAME TAG CNAME
INDEX ON DAVAILABLE TAG DAVAILABLE
ALTER TABLE 'SBRAND' ADD PRIMARY KEY IID TAG IID
INDEX ON IMANUFACTURERVACCINEID TAG MANVACID

***** Change properties for SBRAND *****
DBSETPROP('SBRAND.IID', 'Field', 'Caption', "Iid")
DBSETPROP('SBRAND.IID', 'Field', 'Comment', "Primary key")
DBSETPROP('SBRAND.IMANUFACTURERVACCINEID', 'Field', 'Caption', "Manufacturer/Vaccine Id")
DBSETPROP('SBRAND.IMANUFACTURERVACCINEID', 'Field', 'Comment', "Manufacturer Vaccine Id")
DBSETPROP('SBRAND.CNAME', 'Field', 'Caption', "Name")
DBSETPROP('SBRAND.CNAME', 'Field', 'Comment', "Name")
DBSETPROP('SBRAND.DAVAILABLE', 'Field', 'Caption', "Available")
DBSETPROP('SBRAND.DAVAILABLE', 'Field', 'Comment', "Available date")
DBSETPROP('SBRAND.IMINAGE', 'Field', 'Caption', "Min Age")
DBSETPROP('SBRAND.IMINAGE', 'Field', 'Comment', "Minimum age")
DBSETPROP('SBRAND.IMAXAGE', 'Field', 'Caption', "Max Age")
DBSETPROP('SBRAND.IMAXAGE', 'Field', 'Comment', "Maximum age")
DBSETPROP('SBRAND.IMINDOSE', 'Field', 'Caption', "Min Doses")
DBSETPROP('SBRAND.IMINDOSE', 'Field', 'Comment', "Minimum number of doses")
DBSETPROP('SBRAND.IMAXDOSE', 'Field', 'Caption', "Max Doses")
DBSETPROP('SBRAND.IMAXDOSE', 'Field', 'Comment', "Maximum number of doses")
DBSETPROP('SBRAND', 'Table', 'Comment', "Brand table")
ENDFUNC

FUNCTION MakeTable_SCPT
***** Table setup for SCPT *****
CREATE TABLE 'SCPT.DBF' NAME 'SCPT' (IID I NOT NULL DEFAULT newid(), ;
CCODE C(5) NOT NULL, ;
CNAME C(20) NOT NULL)

***** Create each index for SCPT *****
SET COLLATE TO 'MACHINE'
INDEX ON CCODE TAG CCPTCODE
INDEX ON CNAME TAG CNAME
ALTER TABLE 'SCPT' ADD PRIMARY KEY IID TAG IID

***** Change properties for SCPT *****
DBSETPROP('SCPT.IID', 'Field', 'Caption', "Id")
DBSETPROP('SCPT.IID', 'Field', 'Comment', "Primary key")
DBSETPROP('SCPT.CCODE', 'Field', 'Caption', "CPT Code")
DBSETPROP('SCPT.CCODE', 'Field', 'Comment', "CPT Code")
DBSETPROP('SCPT.CNAME', 'Field', 'Caption', "Cname")
DBSETPROP('SCPT.CNAME', 'Field', 'Comment', "Name")
DBSETPROP('SCPT', 'Table', 'Comment', "CPT Code table")
ENDFUNC

FUNCTION MakeTable_SDRUG
***** Table setup for SDRUG *****
CREATE TABLE 'SDRUG.DBF' NAME 'SDRUG' (IID I NOT NULL DEFAULT newid(), ;
CNAME C(20) NOT NULL)

***** Create each index for SDRUG *****
SET COLLATE TO 'MACHINE'
INDEX ON CNAME TAG CNAME
INDEX ON DELETED() TAG DELETED
ALTER TABLE 'SDRUG' ADD PRIMARY KEY IID TAG IID

***** Change properties for SDRUG *****
DBSETPROP('SDRUG.IID', 'Field', 'Caption', "Iid")
DBSETPROP('SDRUG.IID', 'Field', 'Comment', "Primary key")
DBSETPROP('SDRUG.CNAME', 'Field', 'Caption', "Name")
DBSETPROP('SDRUG.CNAME', 'Field', 'Comment', "Name")
DBSETPROP('SDRUG', 'Table', 'Comment', "Drug file")
ENDFUNC

FUNCTION MakeTable_SMANUFAC
***** Table setup for SMANUFAC *****
CREATE TABLE 'SMANUFAC.DBF' NAME 'SMANUFAC' (IID I NOT NULL DEFAULT newid(), ;
CNAME C(20) NOT NULL)

***** Create each index for SMANUFAC *****
SET COLLATE TO 'MACHINE'
INDEX ON CNAME TAG CNAME
ALTER TABLE 'SMANUFAC' ADD PRIMARY KEY IID TAG IID

***** Change properties for SMANUFAC *****
DBSETPROP('SMANUFAC.IID', 'Field', 'Caption', "Iid")
DBSETPROP('SMANUFAC.IID', 'Field', 'Comment', "Primary key")
DBSETPROP('SMANUFAC.CNAME', 'Field', 'Caption', "Name")
DBSETPROP('SMANUFAC.CNAME', 'Field', 'Comment', "Name")
DBSETPROP('SMANUFAC', 'Table', 'Comment', "Manufacturer")
ENDFUNC

FUNCTION MakeTable_SMANVAC
***** Table setup for SMANVAC *****
CREATE TABLE 'SMANVAC.DBF' NAME 'SMANVAC' (IID I NOT NULL DEFAULT newid(), ;
IMANUFACTURERID I NOT NULL, ;
IVACCINEID I NOT NULL)

***** Create each index for SMANVAC *****
SET COLLATE TO 'MACHINE'
ALTER TABLE 'SMANVAC' ADD PRIMARY KEY IID TAG IID
INDEX ON IMANUFACTURERID TAG IMANUFACTU
INDEX ON IVACCINEID TAG IVACCINEID

***** Change properties for SMANVAC *****
DBSETPROP('SMANVAC.IID', 'Field', 'Caption', "Iid")
DBSETPROP('SMANVAC.IID', 'Field', 'Comment', "Primary key")
DBSETPROP('SMANVAC.IMANUFACTURERID', 'Field', 'Caption', "Manufacturer Id")
DBSETPROP('SMANVAC.IMANUFACTURERID', 'Field', 'Comment', "Manufacturer identifier")
DBSETPROP('SMANVAC.IVACCINEID', 'Field', 'Caption', "Vacccine Id")
DBSETPROP('SMANVAC.IVACCINEID', 'Field', 'Comment', "Vaccine identifier")
DBSETPROP('SMANVAC', 'Table', 'Comment', "Manufacturer/Vaccine cross reference")
ENDFUNC

FUNCTION MakeTable_SVACCINE
***** Table setup for SVACCINE *****
CREATE TABLE 'SVACCINE.DBF' NAME 'SVACCINE' (IID I NOT NULL DEFAULT newid(), ;
IDRUGID I NOT NULL, ;
ICPTID I NOT NULL, ;
CNAME C(20) NOT NULL, ;
DAVAILABLE D NOT NULL, ;
IMINDOSE I NOT NULL, ;
IMAXDOSE I NOT NULL, ;
IMINAGE I NOT NULL, ;
IMAXAGE I NOT NULL)

***** Create each index for SVACCINE *****
SET COLLATE TO 'MACHINE'
INDEX ON CNAME TAG CNAME
INDEX ON DELETED() TAG DELETED
INDEX ON ICPTID TAG ICPTID
INDEX ON IDRUGID TAG IDRUGID
ALTER TABLE 'SVACCINE' ADD PRIMARY KEY IID TAG IID

***** Change properties for SVACCINE *****
DBSETPROP('SVACCINE.IID', 'Field', 'Caption', "Iid")
DBSETPROP('SVACCINE.IID', 'Field', 'Comment', "Primary key")
DBSETPROP('SVACCINE.IDRUGID', 'Field', 'Caption', "Drug Id")
DBSETPROP('SVACCINE.IDRUGID', 'Field', 'Comment', "Drug identifier")
DBSETPROP('SVACCINE.ICPTID', 'Field', 'Caption', "CPTId")
DBSETPROP('SVACCINE.ICPTID', 'Field', 'Comment', "CPT Identifier")
DBSETPROP('SVACCINE.CNAME', 'Field', 'Caption', "Name")
DBSETPROP('SVACCINE.CNAME', 'Field', 'Comment', "Name")
DBSETPROP('SVACCINE.DAVAILABLE', 'Field', 'Caption', "Available")
DBSETPROP('SVACCINE.DAVAILABLE', 'Field', 'Comment', "Available date")
DBSETPROP('SVACCINE.IMINDOSE', 'Field', 'Caption', "Min Doses")
DBSETPROP('SVACCINE.IMINDOSE', 'Field', 'Comment', "Minimum number of doses")
DBSETPROP('SVACCINE.IMAXDOSE', 'Field', 'Caption', "Max Doses")
DBSETPROP('SVACCINE.IMAXDOSE', 'Field', 'Comment', "Maximum number of doses")
DBSETPROP('SVACCINE.IMINAGE', 'Field', 'Caption', "Min Age")
DBSETPROP('SVACCINE.IMINAGE', 'Field', 'Comment', "Minimum age")
DBSETPROP('SVACCINE.IMAXAGE', 'Field', 'Caption', "Max Age")
DBSETPROP('SVACCINE.IMAXAGE', 'Field', 'Comment', "Maximum age")
DBSETPROP('SVACCINE', 'Table', 'Comment', "Vaccine table")
ENDFUNC

FUNCTION MakeTable_UALIAS
***** Table setup for UALIAS *****
CREATE TABLE 'UALIAS.DBF' NAME 'UALIAS' (IID I NOT NULL DEFAULT newid(), ;
IBRANDID I NOT NULL, ;
CNAME C(20) NOT NULL)

***** Create each index for UALIAS *****
SET COLLATE TO 'MACHINE'
INDEX ON CNAME TAG CNAME
INDEX ON IBRANDID TAG IBRANDID
ALTER TABLE 'UALIAS' ADD PRIMARY KEY IID TAG IID

***** Change properties for UALIAS *****
DBSETPROP('UALIAS.IID', 'Field', 'Caption', "Iid")
DBSETPROP('UALIAS.IID', 'Field', 'Comment', "Primary key")
DBSETPROP('UALIAS.IBRANDID', 'Field', 'Caption', "Brand Id")
DBSETPROP('UALIAS.IBRANDID', 'Field', 'Comment', "Brand identifier")
DBSETPROP('UALIAS.CNAME', 'Field', 'Caption', "Name")
DBSETPROP('UALIAS.CNAME', 'Field', 'Comment', "Name")
DBSETPROP('UALIAS', 'Table', 'Comment', "Alias table")
ENDFUNC

*************** Begin Relations Setup **************

FUNCTION MakeRelation_1
ALTER TABLE 'SBRAND' ADD FOREIGN KEY TAG MANVACID REFERENCES SMANVAC TAG IID
ENDFUNC


FUNCTION MakeRelation_2
ALTER TABLE 'SMANVAC' ADD FOREIGN KEY TAG IVACCINEID REFERENCES SVACCINE TAG IID
ENDFUNC


FUNCTION MakeRelation_3
ALTER TABLE 'SMANVAC' ADD FOREIGN KEY TAG IMANUFACTU REFERENCES SMANUFAC TAG IID
ENDFUNC


FUNCTION MakeRelation_4
ALTER TABLE 'SVACCINE' ADD FOREIGN KEY TAG IDRUGID REFERENCES SDRUG TAG IID
ENDFUNC


FUNCTION MakeRelation_5
ALTER TABLE 'SVACCINE' ADD FOREIGN KEY TAG ICPTID REFERENCES SCPT TAG IID
ENDFUNC


FUNCTION MakeRelation_6
ALTER TABLE 'UALIAS' ADD FOREIGN KEY TAG IBRANDID REFERENCES SBRAND TAG IID
ENDFUNC


FUNCTION DisplayStatus(lcMessage)
WAIT WINDOW NOWAIT lcMessage
ENDFUNC

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

MAKEVIEW.PRG


CREATE SQL VIEW "LV_VACCINEBRAND" ;
AS SELECT Sbrand.*, Smanvac.imanufacturerid, Smanvac.ivaccineid, Smanufac.cname AS cmanufacturer FROM impact!smanvac INNER JOIN impact!sbrand INNER JOIN impact!smanufac ON Smanufac.iid = Smanvac.imanufacturerid ON Smanvac.iid = Sbrand.imanufacturervaccineid WHERE Sbrand.imanufacturervaccineid = ?vp_iManufacturerVaccineId

DBSetProp('LV_VACCINEBRAND', 'View', 'UpdateType', 1)
DBSetProp('LV_VACCINEBRAND', 'View', 'WhereType', 3)
DBSetProp('LV_VACCINEBRAND', 'View', 'FetchMemo', .T.)
DBSetProp('LV_VACCINEBRAND', 'View', 'SendUpdates', .T.)
DBSetProp('LV_VACCINEBRAND', 'View', 'UseMemoSize', 255)
DBSetProp('LV_VACCINEBRAND', 'View', 'FetchSize', 100)
DBSetProp('LV_VACCINEBRAND', 'View', 'MaxRecords', -1)
DBSetProp('LV_VACCINEBRAND', 'View', 'Tables', 'impact!sbrand')
DBSetProp('LV_VACCINEBRAND', 'View', 'Prepared', .F.)
DBSetProp('LV_VACCINEBRAND', 'View', 'CompareMemo', .T.)
DBSetProp('LV_VACCINEBRAND', 'View', 'FetchAsNeeded', .F.)
DBSetProp('LV_VACCINEBRAND', 'View', 'FetchSize', 100)
DBSetProp('LV_VACCINEBRAND', 'View', 'ParameterList', "vp_iManufacturerVaccineId,'I'")
DBSetProp('LV_VACCINEBRAND', 'View', 'Comment', "")
DBSetProp('LV_VACCINEBRAND', 'View', 'BatchUpdateCount', 1)
DBSetProp('LV_VACCINEBRAND', 'View', 'ShareConnection', .F.)

*!* Field Level Properties for LV_VACCINEBRAND
* Props for the LV_VACCINEBRAND.iid field.
DBSetProp('LV_VACCINEBRAND.iid', 'Field', 'KeyField', .T.)
DBSetProp('LV_VACCINEBRAND.iid', 'Field', 'Updatable', .T.)
DBSetProp('LV_VACCINEBRAND.iid', 'Field', 'UpdateName', 'impact!sbrand.iid')
DBSetProp('LV_VACCINEBRAND.iid', 'Field', 'DataType', "I")
* Props for the LV_VACCINEBRAND.imanufacturervaccineid field.
DBSetProp('LV_VACCINEBRAND.imanufacturervaccineid', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.imanufacturervaccineid', 'Field', 'Updatable', .T.)
DBSetProp('LV_VACCINEBRAND.imanufacturervaccineid', 'Field', 'UpdateName', 'impact!sbrand.imanufacturervaccineid')
DBSetProp('LV_VACCINEBRAND.imanufacturervaccineid', 'Field', 'DataType', "I")
* Props for the LV_VACCINEBRAND.cname field.
DBSetProp('LV_VACCINEBRAND.cname', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.cname', 'Field', 'Updatable', .T.)
DBSetProp('LV_VACCINEBRAND.cname', 'Field', 'UpdateName', 'impact!sbrand.cname')
DBSetProp('LV_VACCINEBRAND.cname', 'Field', 'DataType', "C(20)")
* Props for the LV_VACCINEBRAND.davailable field.
DBSetProp('LV_VACCINEBRAND.davailable', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.davailable', 'Field', 'Updatable', .T.)
DBSetProp('LV_VACCINEBRAND.davailable', 'Field', 'UpdateName', 'impact!sbrand.davailable')
DBSetProp('LV_VACCINEBRAND.davailable', 'Field', 'DataType', "D")
* Props for the LV_VACCINEBRAND.iminage field.
DBSetProp('LV_VACCINEBRAND.iminage', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.iminage', 'Field', 'Updatable', .T.)
DBSetProp('LV_VACCINEBRAND.iminage', 'Field', 'UpdateName', 'impact!sbrand.iminage')
DBSetProp('LV_VACCINEBRAND.iminage', 'Field', 'DataType', "I")
* Props for the LV_VACCINEBRAND.imaxage field.
DBSetProp('LV_VACCINEBRAND.imaxage', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.imaxage', 'Field', 'Updatable', .T.)
DBSetProp('LV_VACCINEBRAND.imaxage', 'Field', 'UpdateName', 'impact!sbrand.imaxage')
DBSetProp('LV_VACCINEBRAND.imaxage', 'Field', 'DataType', "I")
* Props for the LV_VACCINEBRAND.imindose field.
DBSetProp('LV_VACCINEBRAND.imindose', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.imindose', 'Field', 'Updatable', .T.)
DBSetProp('LV_VACCINEBRAND.imindose', 'Field', 'UpdateName', 'impact!sbrand.imindose')
DBSetProp('LV_VACCINEBRAND.imindose', 'Field', 'DataType', "I")
* Props for the LV_VACCINEBRAND.imaxdose field.
DBSetProp('LV_VACCINEBRAND.imaxdose', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.imaxdose', 'Field', 'Updatable', .T.)
DBSetProp('LV_VACCINEBRAND.imaxdose', 'Field', 'UpdateName', 'impact!sbrand.imaxdose')
DBSetProp('LV_VACCINEBRAND.imaxdose', 'Field', 'DataType', "I")
* Props for the LV_VACCINEBRAND.imanufacturerid field.
DBSetProp('LV_VACCINEBRAND.imanufacturerid', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.imanufacturerid', 'Field', 'Updatable', .F.)
DBSetProp('LV_VACCINEBRAND.imanufacturerid', 'Field', 'UpdateName', 'impact!smanvac.imanufacturerid')
DBSetProp('LV_VACCINEBRAND.imanufacturerid', 'Field', 'DataType', "I")
* Props for the LV_VACCINEBRAND.ivaccineid field.
DBSetProp('LV_VACCINEBRAND.ivaccineid', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.ivaccineid', 'Field', 'Updatable', .F.)
DBSetProp('LV_VACCINEBRAND.ivaccineid', 'Field', 'UpdateName', 'impact!smanvac.ivaccineid')
DBSetProp('LV_VACCINEBRAND.ivaccineid', 'Field', 'DataType', "I")
* Props for the LV_VACCINEBRAND.cmanufacturer field.
DBSetProp('LV_VACCINEBRAND.cmanufacturer', 'Field', 'KeyField', .F.)
DBSetProp('LV_VACCINEBRAND.cmanufacturer', 'Field', 'Updatable', .F.)
DBSetProp('LV_VACCINEBRAND.cmanufacturer', 'Field', 'UpdateName', 'impact!smanufac.cname')
DBSetProp('LV_VACCINEBRAND.cmanufacturer', 'Field', 'DataType', "C(20)")