Defaulting to Selected values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Defaulting to Selected values

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-13 : 08:42:16

As you can see, I have this select statement that generates some values based on a passed in @ProjectID(NOT NULL), @CustomerShipDate(NOT NULL), @CompanyID(NOT NULL), @SeasonID(NULL), @ProductCategoryID(NULL).

Now, sometimes a user may pass in a SeasonID or a ProductCategoryID, but a CustomerSeasonProductTemplate may not exist for that combination. In that case, I would want it to default to a Just the Company Template(Trust that constaints ARE enforced in the DB, so no dups are allowed.)

Any thoughts on how to do this?

Again, one last example. A user puts in "Dell", "Spring", "PC", but no template exists for that, but one DOES exists for just "Dell", "Spring", so that template would get selected. Thoughts?

SELECT @ProjectID, CSPD.EventID, E1.LeadTime,
( SELECT DATEADD( d, -SUM( E2.LeadTime ), @CustomerShipDate )
FROM CompanySeasonProductTemplate AS CSPD2, Event AS E2
WHERE CSPD2.EventID = E2.EventID
AND CSPD2.CompanyID = @CompanyID
AND SeasonID = @SeasonID
AND ProductCategoryID = @ProductCategoryID
AND E2.EventID >= E1.EventID ) AS EstimatedCompletionDate
FROM CompanySeasonProductTemplate AS CSPD, Event AS E1
WHERE CSPD.EventID = E1.EventID
AND CSPD.CompanyID = @CompanyID
AND SeasonID = @SeasonID
AND ProductCategoryID = @ProductCategoryID
ORDER BY EventOrder

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 09:05:34

It seems that you have to use Dynamic SQL for this
Give more details and post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-13 : 10:06:35

quote:
Originally posted by madhivanan

It seems that you have to use Dynamic SQL for this
Give more details and post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

Ok, lets take a simple example.
I have a table like this
CompanySeasonProductTemplate

CompanySeasonProductTemplateID PK
CompanyID AK
SeasonID AK
ProductCategoryID AK
EventID AK

Data in it could be
CompanyID :: SeasonID :: ProductCategoryID :: EventID
1 :: 1 :: 1 :: 1
1 :: NULL :: 1 :: 1
1 :: NULL :: 1 :: 2
1 :: NULL :: NULL :: 2

So, what I want to do here, is that if the Query were
SELECT EventID FROM CompanySeasonProductTemplate
WHERE CompanyID = 1 AND SeasonID 1 AND ProductCategoryID = 1, that would obviosly return one EventID (1).

But here is where it gets complex (for me)
SELECT EventID FROM CompanySeasonProductTemplate
WHERE CompanyID = 1 AND SeasonID Is NULL AND ProductCategoryID = 2..That obviously does not exist from the table above. So, here I would want it to default to one that DOES exists, so next the queryt should try
SELECT EventID FROM CompanySeasonProductTemplate
WHERE CompanyID = 1 AND SeasonID Is NULL AND ProductCategoryID Is NULL
And by golly, that exists, so return that EventID (2).

Does that make more sense?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 10:35:06

Cant you use like this?

SELECT EventID FROM CompanySeasonProductTemplate 
WHERE CompanyID = 1 AND SeasonID Is NULL AND (ProductCategoryID = 2 or ProductCategoryID is null)

Madhivanan

Failing to plan is Planning to fail

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-13 : 11:21:06

quote:
Originally posted by madhivanan

Cant you use like this?
SELECT EventID FROM CompanySeasonProductTemplate
WHERE CompanyID = 1 AND SeasonID Is NULL AND (ProductCategoryID = 2 or ProductCategoryID is null)

Madhivanan

Failing to plan is Planning to fail

I dont think so because, I can also have this..
CompanyID :: SeasonID :: ProductCategoryID :: EventID
1 :: 2 :: NULL :: 1
1 :: NULL :: 3 :: 2

Now, say they pass in CompanyID = 1, SeasonID = 2 and ProductCategory = 3. There is no template that exists for that combination. So, now it should look for something with CompanyID = 1 AND SeasonID =2 and productCategoryID IS NULL. If that doesn't return anything, then it should try CompanyID = 1 AND SeasonID Is Null and productCategoryID =3
And finally if that doesn't work, just look for CompanyID = 1 AND SeasonID Is NULL and productCategoryID IS NULL

Make any sense?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 11:27:23

Then Use

WHERE (CompanyID = 1 or CompanyID is null) AND (SeasonID =2 or SeasonID Is NULL) AND (ProductCategoryID = 2 or ProductCategoryID is null)

Madhivanan

Failing to plan is Planning to fail

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-13 : 12:42:05

quote:
Originally posted by madhivanan

Then Use
WHERE (CompanyID = 1 or CompanyID is null) AND (SeasonID =2 or SeasonID Is NULL) AND (ProductCategoryID = 2 or ProductCategoryID is null)

Madhivanan

Failing to plan is Planning to fail

Soory, doesn't work, if I use OR in that fashion, it could return more than one set of correct EventIDs..obviously a no no...I'll keep chugging

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-13 : 15:15:22

This seems a bit complex, but it definately works...Thanks for the help. Harry C

DECLARE @CustomerShipDate DateTime
DECLARE @CompanyID int
DECLARE @SeasonID int
DECLARE @ProductCategoryID int
DECLARE @ProjectID int
DECLARE @CountOne int
DECLARE @Found bit

SET @Found = 0
SET @CustomerShipDate = GetDate()
SET @CompanyID = 1
SET @SeasonID = 3
SET @ProductCategoryID = 1
SET @ProjectID = 10

IF (SELECT Count(*) FROM CompanySeasonProductTemplate
WHERE CompanyID = @CompanyID AND SeasonID=@SeasonID AND ProductCategoryID=@ProductCategoryID)>0
BEGIN
SELECT @ProjectID, CSPD.EventID, E1.LeadTime,
( SELECT DATEADD( d, -SUM( E2.LeadTime ), @CustomerShipDate )
FROM CompanySeasonProductTemplate AS CSPD2, Event AS E2
WHERE CSPD2.EventID = E2.EventID
AND CSPD2.CompanyID = @CompanyID
AND SeasonID=@SeasonID
AND ProductCategoryID = @ProductCategoryID
AND E2.EventID >= E1.EventID ) AS EstimatedCompletionDate
FROM CompanySeasonProductTemplate AS CSPD, Event AS E1
WHERE CSPD.EventID = E1.EventID
AND CSPD.CompanyID = @CompanyID
AND CSPD.SeasonID = @SeasonID
AND CSPD.ProductCategoryID = @ProductCategoryID
ORDER BY EventOrder
SET @Found=1
END
IF @Found = 0 AND (SELECT Count(*) FROM CompanySeasonProductTemplate
WHERE CompanyID = @CompanyID AND SeasonID=@SeasonID AND ProductCategoryID Is Null)>0
BEGIN
SELECT @ProjectID, CSPD.EventID, E1.LeadTime,
( SELECT DATEADD( d, -SUM( E2.LeadTime ), @CustomerShipDate )
FROM CompanySeasonProductTemplate AS CSPD2, Event AS E2
WHERE CSPD2.EventID = E2.EventID
AND CSPD2.CompanyID = @CompanyID
AND SeasonID = @SeasonID
AND ProductCategoryID Is Null
AND E2.EventID >= E1.EventID ) AS EstimatedCompletionDate
FROM CompanySeasonProductTemplate AS CSPD, Event AS E1
WHERE CSPD.EventID = E1.EventID
AND CSPD.CompanyID = @CompanyID
AND SeasonID = @SeasonID
AND ProductCategoryID Is Null
ORDER BY EventOrder
SET @Found = 1
END

IF @Found = 0 AND (SELECT Count(*) FROM CompanySeasonProductTemplate
WHERE CompanyID = @CompanyID AND SeasonID Is Null AND ProductCategoryID Is Null)>0
BEGIN
SELECT @ProjectID, CSPD.EventID, E1.LeadTime,
( SELECT DATEADD( d, -SUM( E2.LeadTime ), @CustomerShipDate )
FROM CompanySeasonProductTemplate AS CSPD2, Event AS E2
WHERE CSPD2.EventID = E2.EventID
AND CSPD2.CompanyID = @CompanyID
AND SeasonID Is Null
AND ProductCategoryID Is Null
AND E2.EventID >= E1.EventID ) AS EstimatedCompletionDate
FROM CompanySeasonProductTemplate AS CSPD, Event AS E1
WHERE CSPD.EventID = E1.EventID
AND CSPD.CompanyID = @CompanyID
AND SeasonID Is Null
AND ProductCategoryID Is Null
ORDER BY EventOrder
SET @Found = 1
END

SELECT @Found

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources