Example—Database view for the PROFJOB table

create view PROFILE_PJVIEW

as

select

PROFJOB.LABCODE,

PROFJOB.PRO_JOB,

PROFJOB.CLI_LABCODE,

PROFJOB.CLI_CODE,

PROFJOB.PROJ_CODE,

PROFJOB.ORDERNO,

PROFJOB.RECEIVED,

PROFJOB.REQUIRED,

PROFJOB.PRODUCTCODE,

PROFJOBUSER.JOBTYPE,

CLIENT.CLI_NAME,

COUNT(PROFJOB_CUID.CUID) as NOUNKS

from PROFJOB

left outer join PROFJOBUSER on

PROFJOB.LABCODE = PROFJOBUSER.LABCODE and

PROFJOB.PRO_JOB = PROFJOBUSER.PRO_JOB

left outer join PROFJOB_CUID on

PROFJOB.LABCODE = PROFJOB_CUID.LABCODE and

PROFJOB.PRO_JOB = PROFJOB_CUID.PRO_JOB

left outer join CLIENT on

PROFJOB.CLI_LABCODE = CLIENT.LABCODE and

PROFJOB.CLI_CODE = dbo.CLIENT.CLI_CODE

where

(PROFJOB_CUID.SAMPLETYPE = 'UNK')

group by

PROFJOB.LABCODE,

PROFJOB.PRO_JOB,

PROFJOB.CLI_LABCODE,

PROFJOB.CLI_CODE,

PROFJOB.PROJ_CODE,

PROFJOB.ORDERNO,

PROFJOB.RECEIVED,

PROFJOB.REQUIRED,

PROFJOB.PRODUCTCODE,

PROFJOBUSER.JOBTYPE,

CLIENT.CLI_NAME

go