Hi,
Sorry if I'm in the wrong group for this sql language question, but perhaps
someone here can answer -- or direct me to a more appropriate newsgroup?

I have a sql query that attempts to select a "DealerClientType" based on a
Case statement involving values from other tables. Running the query clocks
for minutes and when I cancel I see it must be looping as it returns the same
row over 100,000 times. Copied below is the query. Perhaps some combination
of a left join and case might work? My intent is to return all rows from the
first table (Transactions), and to include a DealerClientType based on
different criteria for different Dealers.


SELECT a.[TransSQLID]
,a.[DealerCode]
,a.[BranchCode]
,a.[AccountNbr]
,a.[Units],
(Select Case
When (Select c.DealerCode from [MIP].[dbo].[DealerAccountNbr] c
Where a.DealerCode = c.DealerCode
and a.AccountNbr = c.AccountNbr) = 'ABC'
Then c.AccountClientType
When (Select d.DealerCode from [MIP].[dbo].[DealerBranch] d
Where a.DealerCode = d.DealerCode
and a.BranchCode = d.BranchCode) = 'XYZ'
Then d.BranchClientType
Else b.[DealerClientType]
end) as TransClientType
FROM [MIP].[dbo].[Transactions] a
, [MIP].[dbo].[Dealers] b
, [MIP].[dbo].[DealerAccountNbr] c
, [MIP].[dbo].[DealerBranch] d
Where a.DealerCode = b.DealerCode


Any advice is much appreciated! Thank you!
Thomas

Hi ,

I will probably write this way--



SELECT a.[TransSQLID]
,a.[DealerCode]
,a.[BranchCode]
,a.[AccountNbr]
,a.[Units],
Case
When c.DealerCode = 'ABC'
Then c.AccountClientType
When d.DealerCode = 'XYZ'
Then d.BranchClientType
Else b.DealerClientType
end as TransClientType
FROM [MIP].[dbo].[Transactions] a
, [MIP].[dbo].[Dealers] b
, [MIP].[dbo].[DealerAccountNbr] c
, [MIP].[dbo].[DealerBranch] d
where a.DealerCode = b.DealerCode
and a.AccountNbr = c.AccountNbr
and a.DealerCode = c.DealerCode
and a.DealerCode = d.DealerCode
and a.BranchCode = d.BranchCode

"thabrown" wrote:

Thanks for your reply, SP.

I had also posted in the tools group, and a reply there helped me solve.
Your suggestion wouldn't work as it would eliminate rows from the first table
that didn't
fit all the criteria matching all 4 tables. 2 of the tables involved do not
necessarily have related rows in the 1st table. Sorry I didn't explain that
initially, but I do appreciate your suggestion.

Here is the solution I ended up with:

SELECT a.[TransSQLID]
,a.[DealerCode]
,a.[BranchCode]
,a.[AccountNbr]
,a.[Units]
,
(Select Case
When (Select c.DealerCode from [MIP].[dbo].[DealerAccountNbr] c
Where a.DealerCode = c.DealerCode
and a.AccountNbr = c.AccountNbr) = 'abc'
Then c.AccountClientType
When (Select d.DealerCode from [MIP].[dbo].[DealerBranch] d
Where a.DealerCode = d.DealerCode
and a.BranchCode = d.BranchCode) = 'xyz'
Then d.BranchClientType
Else b.[DealerClientType]
end) as TransClientType
FROM [MIP].[dbo].[Transactions] a
JOIN [MIP].[dbo].[Dealers] b
ON a.DealerCode = b.DealerCode
left JOIN [MIP].[dbo].[DealerAccountNbr] c
ON a.AccountNbr = c.AccountNbr
and a.DealerCode = c.DealerCode
left JOIN [MIP].[dbo].[DealerBranch] d
ON a.BranchCode = d.BranchCode
and a.DealerCode = d.DealerCode

"SP" wrote: