: !/bin/sh if [ -n "$1" ]; then outfile="$1" else outfile="tbspersonservicelist.unl" fi # Testing cp tbspersonservicelist.unl.test "$outfile" exit 0 ## view created by Robyn 08/0562010 #tbssql >"$outfile" <<SQLCMD0 #select * from vwRDBilmaxPersonServiceList #order by 1 #SQLCMD0 # #exit 0 #---------------------------- # SQL for view is... tbssql >"$outfile" <<SQLCMD0 SELECT dbo.tPerson.ID AS PersonID, dbo.tPerson.Person, dbo.tServiceID.ID, dbo.tServiceID.ServiceID, dbo.tCustomer.ShipTo, dbo.tCustomer.FullName, dbo.tLocation.ID, dbo.tLocation.Description, dbo.tDepartment.ID, dbo.tDepartment.Description, dbo.tSupplier.ID, dbo.tSupplier.SupplierName FROM dbo.tPerson LEFT OUTER JOIN dbo.tGroup INNER JOIN dbo.tCentre INNER JOIN dbo.tServiceID LEFT OUTER JOIN dbo.tLocation ON dbo.tLocation.ID = dbo.tServiceID.LocationID ON dbo.tCentre.ID = dbo.tServiceID.CustomerCentreID LEFT JOIN tServiceType ON tServiceType.ID = tServiceID.ServiceTypeID LEFT JOIN tSupplier ON tServiceType.SupplierID = tSupplier.ID INNER JOIN dbo.tDepartment ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID ON dbo.tGroup.ID = dbo.tDepartment.GroupID INNER JOIN dbo.tCustomer ON dbo.tGroup.CustomerID = dbo.tCustomer.ID ON dbo.tPerson.ID = dbo.tServiceID.PersonID WHERE (dbo.tPerson.Active = 1) AND (dbo.tServiceID.Active = 1) AND (NOT (dbo.tServiceID.ServiceID LIKE 'kamco%')) AND (NOT (dbo.tServiceID.ServiceID LIKE 'annual%')) ORDER BY dbo.tPerson.Person SQLCMD0