Conditional Lookups... - 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)

Conditional Lookups...

Author

Topic

gyoung345
Starting Member

3 Posts

Posted - 2004-12-02 : 11:41:51

I have a question about how to do this. I have two tables and I will try to simplify below.

tblDetails
tblReasons

The tblDetails has a reasoncode that is listed in the tblReasons. I need to lookup the description for the reason in tblReasons. The complicated part is that it is dependent on a divisionCode. For instance, I have a tblDetails record that has a reasoncode '01' and is in division '16' (divisionCode = '16'). I can easily do a join on the tables to find this information. Here's were it gets complicated. If the reasoncode '01' and divisionCode = '16' doesn't exist I need to use reasoncode '01' and divisioncode = '00' (which is a generic division) for the description.

My current SQL looks like this. I was under the assumption that I only needed '00' divison reasoncodes, but I need to check for the '16' division first and if it doesn't exist then look for the '00' division.

SELECT tblDetails.reasoncode,
tblReasons.reasoncodedesc,
Sum(Details.price),
Sum(Details.quantity) as units
FROM tblDetails
INNER JOIN tblReasons ON tblDetails.reasonCode = tblReasons.reasonCode AND
tblReasons.DivisionCode = '00'
WHERE (tblDetails.divisionCode = '16')
group by tblDetails.reasoncode
order by units desc

Thanks,

Greg

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-02 : 11:57:24

I would say you should use a LEFT JOIN and a CASE Statement

Brett

8-)

gyoung345
Starting Member

3 Posts

Posted - 2004-12-02 : 12:25:48

I modified the SQL, but I don't think it is working correctly.

SELECT tblDetails.reasoncode,
tblReasons.reasoncodedesc,
Sum(Details.price),
Sum(Details.quantity) as units
FROM tblDetails
LEFT OUTER JOIN tblReasons ON tblDetails.reasonCode = tblReasons.reasonCode AND
tblReasons.DivisionCode = CASE WHEN tblReasons.divisioncode IS NULL then '00' else '16' END
WHERE (tblDetails.divisionCode = '16')
group by tblDetails.reasoncode
order by units desc

gyoung345
Starting Member

3 Posts

Posted - 2004-12-03 : 11:02:20

I ended up writing a function that I call to return the correct value based on the information that I passed it. Works great.

CREATE FUNCTION GetReasonCodeDescr (@vReasonCode VARCHAR(2),@vDivCode VARCHAR(2))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @rDescr VARCHAR(50)

SELECT @rDescr = reasonCodeDesc
FROM tblReasons
WHERE reasonCode = @vReasonCode AND DivisionCode = @vDivCode

If @rDescr IS NULL

SELECT @rDescr = reasonCodeDesc
FROM tblReasons
WHERE reasonCode = @vReasonCode AND DivisionCode = '00'

RETURN @rDescr

END

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-03 : 11:32:32

quote:
The tblDetails has a reasoncode that is listed in the tblReasons. I need to lookup the description for the reason in tblReasons. The complicated part is that it is dependent on a divisionCode. For instance, I have a tblDetails record that has a reasoncode '01' and is in division '16' (divisionCode = '16'). I can easily do a join on the tables to find this information. Here's were it gets complicated. If the reasoncode '01' and divisionCode = '16' doesn't exist I need to use reasoncode '01' and divisioncode = '00' (which is a generic division) for the description.

The function will probably perform much worse than a join, so be careful of that. I'd advice against look-up type user defined functions.

All you need to do is join to TWO separate instances of the Reasons table, each with a left outer join. one is joined based on reasoncode/deparment, the other is joined on reasoncode and returns only the default department.

Then use a case or ISNULL to determine from which alias of your Reason codes to show the description from.

Look at the below carefully, see if it helps you out:


SELECT 
  D.reasoncode,
  ISNULL(R.reasoncodedesc, RDefault.ReasonCodeDesc) as Desc
  Sum(D.price),
  Sum(D.quantity) as units
FROM 
  tblDetails D
LEFT OUTER JOIN 
  tblReasons R ON D.reasonCode = R.reasonCode AND D.DivisionCode = R.ReasonCode
LEFT OUTER JOIN
  tblReasons RDefault ON D.reasoncode = RDefault.ReasonCode and RDefault.Division = '00'
WHERE 
  (D.divisionCode = '16')
group by 
  ISNULL(R.reasoncodedesc, RDefault.ReasonCodeDesc)
order by 
  units desc

If you dissect the above piece by piece, you will see it logically does exactly what you want. either get the matching reason/division code for the detail table based on the usual join, or if there isn't a match that way, get the matching reasoncode for divison '00'.

you should probably never have to use a CASE in a join expression.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources