freshstartusa
asked on
Return greater of two fields
Hi There,
I am joining two tables, both have a price. I only want to return a single price field which on a record by record basis is the larger of the two.
For example:
Price 1 Price 2 Returned Price
0 7 7
2 5 5
7 1 7
9 0 9
etc.
I Need MSSQL syntax I can use in the view below. Note in the second half of the unity where I'm using a GREATEST() function. Unfortunately MSSQL doesn't have such a function! Need the equivalent funciton or a work around so I can put something like:
GREATEST(mp.ProductPrice,p p.Price) as ProductPrice
in the SELECT clause . . .
Here's my view:
CREATE VIEW ProjectProductView
AS
SELECT ProductID, ProductTitle, ProductName, ProductSKU ,ProductCategoryList, ProductOverview, ProductDescription, ProductMetaKeywords, ProductPrice, ProductSalePrice, ProductPetMedsPrice, ProductMainImage, ProductThumbnailImage, ProductWeight, ProductMinimumPrice, ProductProjectID, ProductID as DistinctID
FROM CUSTOM_PRODUCT mp
UNION ALL
SELECT mp.ProductID, ProductTitle, ProductName, ProductSKU ,ProductCategoryList, ProductOverview, ProductDescription, ProductMetaKeywords, GREATEST(mp.ProductPrice,p p.Price) as ProductPrice, ProductSalePrice, ProductPetMedsPrice, ProductMainImage, ProductThumbnailImage, ProductWeight, ProductMinimumPrice, pp.ProjectID as ProductProjectID, (pp.ProjectID * 100000 + mp.ProductID) as DistinctID
FROM MASTER_PRODUCT mp, PRODUCT_PROJECT pp
WHERE pp.ProductID = mp.ProductID
Best Wishes,
Peter
I am joining two tables, both have a price. I only want to return a single price field which on a record by record basis is the larger of the two.
For example:
Price 1 Price 2 Returned Price
0 7 7
2 5 5
7 1 7
9 0 9
etc.
I Need MSSQL syntax I can use in the view below. Note in the second half of the unity where I'm using a GREATEST() function. Unfortunately MSSQL doesn't have such a function! Need the equivalent funciton or a work around so I can put something like:
GREATEST(mp.ProductPrice,p
in the SELECT clause . . .
Here's my view:
CREATE VIEW ProjectProductView
AS
SELECT ProductID, ProductTitle, ProductName, ProductSKU ,ProductCategoryList, ProductOverview, ProductDescription, ProductMetaKeywords, ProductPrice, ProductSalePrice, ProductPetMedsPrice, ProductMainImage, ProductThumbnailImage, ProductWeight, ProductMinimumPrice, ProductProjectID, ProductID as DistinctID
FROM CUSTOM_PRODUCT mp
UNION ALL
SELECT mp.ProductID, ProductTitle, ProductName, ProductSKU ,ProductCategoryList, ProductOverview, ProductDescription, ProductMetaKeywords, GREATEST(mp.ProductPrice,p
FROM MASTER_PRODUCT mp, PRODUCT_PROJECT pp
WHERE pp.ProductID = mp.ProductID
Best Wishes,
Peter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.