October 20, 2010 at 1:26 pm
I've got a situation that I cannot seem to resolve. I would like to put this functionality in a view so I can hopefully use a full-index against it.
Here is my table definition.
CREATE TABLE [dbo].[TestTable](
[SiteID] [int] ,
[ProductID] [int] ,
[ProductDesc] [varchar](50) ,
[isDefault] [bit] ,
)
go
.
I'd like to retrieve the 'ProductDesc' from a table for a particular siteid/ProductID combination. If the row does not exist, I'd like to return the 'ProductDesc' where [isDefault] = 'True'. I could do it in a function, but I'd like to do this in a view.
.
Here is the test data.
insert into TestTable(siteid, productid, productdesc, isdefault) values ( 1, 10, 'SiteID1_Product10', 'True' );
insert into TestTable(siteid, productid, productdesc, isdefault) values ( 1, 20, 'SiteID1_Product20', 'True' );
insert into TestTable(siteid, productid, productdesc, isdefault) values ( 2, 10, 'SiteID2_Product10', 'False' );
insert into TestTable(siteid, productid, productdesc, isdefault) values ( 3, 20, 'SiteID3_Product20', 'False' );
Example #1
SiteID=1 and ProductID=20, which will return 'SiteID1_Product20'
Example #2
SiteID=2 and ProductID=20, which will also return 'SiteID1_Product20' because there is no such siteid/productID of 2/20 and the row with 1/20 is set as the default for the ProductID=20
I was hoping the forum readers could point me in the right direction. Thank you for your help.
October 20, 2010 at 1:34 pm
Do an outer join based on the primary criteria, then another based on the fallback criteria, and use Coalesce (or IsNull) to bridge the gap in the Select clause.
select MO.SiteID, MO.ProductID, coalesce(ML.ProductDesc, ML2.ProductDesc) as ProductDesc
from MyOuterTable as MO
left outer join MyLookupTable as ML
on MO.SiteID = ML.SiteID
and MO.ProductID = ML.ProductID
left outer join MyLookupTable as ML2
on MO.ProductID = ML2.ProductID
and ML2.IsDefault != 0
Something like that, but with your real table names.
Does that help?
Keep in mind, this will produce odd results if you have more than one row marked "IsDefault" for any given ProductID. So will any solution that doesn't include business rules for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 1:43 pm
There's no simple way of getting this out of a view because the view won't easily be able to create data on the fly. I can see a way of doing it reasonably using a Tally table if you absolutely must have it fill in the missing values, but I would say by standard you'd use a proc here.
-- Check for existance of site/product pair.
-- If not existing, find and return where product is default
-- If existing, find and return proper row.
The alternative would find the largest sitecode in use, use Tally to fill in the missing sitecode/productcode combinations, and then use a series of isnulls to pull either the proper row or the default row from the base table for each combination. I can try putting that together for you if you really want to chase down that approach.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 20, 2010 at 1:46 pm
Craig, I think you're overcomplicating it. The select I wrote could certainly be built into a view.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 1:48 pm
GSquared (10/20/2010)
Craig, I think you're overcomplicating it. The select I wrote could certainly be built into a view.
Unless I seriously missed something, your code and his sample data will not let you get to a result set from:
select * from view where siteId = 2 and productid = 20
He needs to fill the gap data somehow and return defaults for non-existant rows.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 20, 2010 at 1:51 pm
I'm assuming the existence of an outer table that lists the site/product combinations that are being queried.
If that's not the case, then a simple cross join of valid site IDs and product IDs can be built into a view to give all possible combinations, and then join from that.
Either can easily be built into a view and then queried.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 2:02 pm
This has been extremely helpful. Thank you.
Would you be willing to give me an example of the "simple cross join of valid site IDs and product IDs can be built into a view to give all possible combinations, and then join from that"
My SQL skills are limited....growing day by day...but limited.
Thank you again.
October 20, 2010 at 2:14 pm
you can Try Table Valued Functions as:
CREATE FUNCTION dbo.fn_table(@Siteid int,@ProductID int)
Returns @tempTable Table (SiteID] [int] ,
[ProductID] [int] ,
[ProductDesc] [varchar](50) ,
[isDefault] [bit])
AS
IF exists (Select 1 From TestTable Where SiteID=@SiteID and ProductID=@productID)
Begin
Insert into @tempTable (SITEID, ProductID, ProductDesc, isDefault)
Select SITEID, ProductID, ProductDesc, isDefault From TestTable
Where SiteID=@SiteID and ProductID=@productID
End
Else
Begin
Insert into @tempTable (SITEID, ProductID, ProductDesc, isDefault)
Select SITEID, ProductID, ProductDesc, isDefault From TestTable
Where ProductID=@productIDAnd [isDefault]='TRUE'
End
Return
END
you can use it as:
Select * from dbo.fn_table(2,20)
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
October 20, 2010 at 2:19 pm
What GSquared is talking about and is a slightly different approach than what I mentioned would be something like this. It's just as valid and may or may not perform better then the Tally solution. Swap names as appropriate for your source tables for Sites and Products instead of the testTable many to many join.
select
sp.siteId,
sp.productID,
ISNULL( t1.ProductDesc, t2.ProductDesc)
FROM
(SELECT DISTINCT siteID from SiteTable
CROSS JOIN
SELECT DISTINCT productID from ProductTable
) AS sp -- siteproduct
LEFT JOIN
TestTable AS t1
ONsp.productId = t1.productId
AND sp.siteID = t1.SiteID
LEFT JOIN
( SELECT productId, productDesc FROM TestTable WHERE isDefault = 1) AS t2
ONsp.productId = t2.ProductID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 21, 2010 at 3:27 am
Hello,
Please try this. Not sure, if I got ur question right!!!!!!!
select sub.*,case when checked='no' then
(select final.ProductDesc from #testtable final where final.productid=sub.productid and final.isdefault=1)
else t.productdesc end as productdesc,t.isdefault from #testtable t
right join
(
select distinct a.SiteId,b.Productid,case when (select 1 from #testtable t where t.siteid=a.siteid
and t.productid=b.productid) is not null
then 'yes' else 'no' end as checked from #TestTable a
cross join #TestTable b) sub
on t.siteid=sub.siteid
and t.productid=sub.productid
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply