-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
A view is meant to be used many times. E.g., if you want to find the
current price of a product you'd set up a CurrentProductPrice view
instead of creating & running a query:
CREATE VIEW CurrentProductPrice
AS
SELECT P.ProductName, PP.PriceEndDate, PP.Price
FROM Products As P INNER JOIN ProductPrices As PP
ON P.ProductID = PP.ProductID
WHERE PP.PriceEndDate IS NULL
-- NULL PriceEndDAte indicates this is the current price
Now, instead of creating the JOIN query you'd just run this command:
SELECT ProductName, Price FROM CurrentProductPrice
If the users want to do adhoc queries they should have access to Query
Analyzer (since you are developing in SQL Server [an .adp file]) - that
is, if the users can program in SQL.
If you are trying to use some sort of criteria form that the users can
select various criteria before running a query, then you can create the
SQL string on the fly (string concatenation) & run it via ADO - the
result would have to be displayed in a form.
Or, you could design a stored procedure that has all the criteria
available, but set up to ignore any criteria that is not used. E.g.,
set up the WHERE clause of a query like this:
CREATE PROCEDURE SomeName (
@criteriaA INT,
@criteriaB, VARCHAR(33),
....
)
AS
....
WHERE colA = COALESCE(@criteriaA,colA)
AND colB = COALESCE(@criteriaB,colB)
....
The COALESCE() function will force the expression to return the value of
colA whenever @criteriaA is NULL. This, in effect, causes the NULL
criteria to be ignored. When the criteria has data the expression will
be evaluated using the criteria data.
The result could show in a datasheet.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQ/IrP4echKqOuFEgEQLvSwCfWhsLpV8aoFTYIfN4VW9n7UxyTGoAoJRQ
iITvqj1yWeG6M5X34TPOrUin
=7hO/
-----END PGP SIGNATURE-----
