Upsize to sql from access the IIf statement and slow load

[hermanlaksko]

I am having a problem with a query in access it looks like this:

SELECT DISTINCTROW Transactions.ID, Sum(Securities.[CommCov%]) AS Summa, 1-Nz([Summa]) AS [Unsecured%], Sum(Securities.[PolCov%]) AS PolSumma, IIf(Nz([PolSumma])>=1,0,1-Nz([PolSumma])) AS [UnsecuredPol%], First(Deals.BorrowerID) AS BorrowerID, First(Countries.Name) AS Country, Clients.CountryID, Clients.Client_PropertyID, Clients.ID AS ClientID
FROM Countries RIGHT JOIN ((Deals RIGHT JOIN (Transactions LEFT JOIN Securities ON Transactions.ID = Securities.TransactionID) ON Deals.ID = Transactions.DealID) LEFT JOIN Clients ON Deals.BorrowerID = Clients.ID) ON Countries.ID = Clients.CountryID
GROUP BY Transactions.ID, Clients.CountryID, Clients.Client_PropertyID, Clients.ID;


I have translated it into SQL and its now looking like this:

SELECT Transactions.ID, SUM(Securities.[CommCov%]) AS Summa,
1 - (Securities.[CommCov%]) AS [Unsecured%],
CASE WHEN SUM(Securities.[PolCov%])
>= 1 THEN 0 ELSE 1 - (SUM(Securities.[PolCov%]))
END AS [UnsecuredPol%],
MIN(cast(Deals.BorrowerID AS varchar(35))) AS BorrowerID,
MIN(Countries.Name) AS Country,
Clients.CountryID AS CountryID, Clients.Client_PropertyID,
Clients.ID AS ClientID
FROM Countries, Deals, Transactions, Securities, Clients
GROUP BY Transactions.ID, Securities.[CommCov%],
Clients.CountryID, Clients.Client_PropertyID, Clients.ID

In access it loads fine, however in ms-sql it loads slooooow, if at all.
I have tryed it both as a view and as a procedure but there is no diff.
Can someone lend a hand?
Thanks in advance
Herman

Herman
Say no to macros

 

[cmmrfrds]

The query is producing a cartesian product.

It appears to be missing the join criteria.

FROM Countries RIGHT JOIN ((Deals RIGHT JOIN (Transactions LEFT JOIN Securities ON Transactions.ID = Securities.TransactionID) ON Deals.ID = Transactions.DealID) LEFT JOIN Clients ON Deals.BorrowerID = Clients.ID) ON Countries.ID = Clients.CountryID

 

[hermanlaksko]

When one is blind, it is nice to have a new pair of eyes to find, and point out the obvious.

I am not sure how I, during my struggle, managed to drop these lines and overlook it time and again, but I did ....

That did the trick ;-)

A BIG thanks for letting me know!!

Herman
Say no to macros