How to improve this SQL statement?

qxz · #1 (**permalink**) 06-30-03, 23:33

I have a query in MS Access that will select the only record from a table DECISION, given three input parameters: pMonth, pLoansize and pLTV. The record should have the maximal possible value for MONTH, LOAN_SIZE and PERCENTAGE, in this order.

My query is working, but a bit slow since it seems joining four tables. I'd appreicate for any idea about how to improve this query, which is as
follow. Thanks.

-----------------------------------------------------------------------------
SELECT distinct C.DECISION_ID, C.DECISION_NAME_ID, C.MONTH, C.LOAN_SIZE, C.percentage, C.ADMIN_FEE_HL_ID, C.PNTY_ID, C.OFFER_RATE_ID
FROM DECISION AS A, DECISION AS B, DECISION AS C, DECISION AS D
WHERE
(D.MONTH = (SELECT max(MONTH) FROM DECISION D WHERE pLoansize>=D.LOAN_SIZEand pMONTH >= D.MONTH AND pLTV >= D.percentage))
AND
(B.LOAN_SIZE= (SELECT max(loan_size) FROM DECISION B WHERE B.MONTH = D.MONTH AND pLoansize>=B.LOAN_SIZEAND pLTV >= B.percentage))
AND
(A.percentage = (SELECT max(percentage) FROM DECISION A WHERE A.MONTH = D.MONTH and A.loan_size=B.LOAN_SIZE
AND pLTV >= A.percentage))
AND C.MONTH = D.MONTH
AND C.LOAN_SIZE= B.loan_size
AND C.percentage = A.percentage

r937 · #2 (**permalink**) 07-01-03, 07:45

total shot in the dark, please let me know if this works:

Code:

SELECT DECISION_ID
     , DECISION_NAME_ID
     , MONTH
     , LOAN_SIZE
     , percentage
     , ADMIN_FEE_HL_ID
     , PNTY_ID
     , OFFER_RATE_ID
  FROM DECISION        AS A
 WHERE MONTH = (
            SELECT max(MONTH)
              FROM DECISION 
               )
   AND LOAN_SIZE = (
            SELECT max(loan_size) 
              FROM DECISION 
             WHERE MONTH = A.MONTH 
                   )
   AND percentage = (
            SELECT max(percentage)
              FROM DECISION  
             WHERE MONTH = A.MONTH 
               and loan_size = a.LOAN_SIZE 
                    )

rudy
http://r937.com/

qxz · #3 (**permalink**) 07-01-03, 21:36

Hi r937,

Thanks for the try. But I am afraid that it is not working. Your SQL may end up with no record selected. For example, a simplified DECISION table:

Month, Loan_size, Percentage
8, 100, 70
4, 200, 50
6, 150, 80

r937 · #4 (**permalink**) 07-01-03, 22:37

did you try it?

i tried it on your sample of 3 and it gave the row with

8, 100, 70

if you just wanted the max of all three colulmns, that's a totally different query (and a lot simpler, too)

i thought you wanted row integrity

for example, suppose there are 80 rows in the table, of which 20 belong to the highest month

then out of those 20 rows which have the highest month, 6 of those rows have the highest loan_size for that month

then out of those 6 rows which have the highest loan_size for the highest month, one of them has the highest percentage

my query will always return a row, as long as there is at least one row in the table

perhaps you did not explain your problem correctly?

i tried to see what your query was attempting to do, but it's seriously messed up

or maybe i totally misunderstood you

rudy

qxz · #5 (**permalink**) 07-01-03, 23:06

Hi Rudy,

Sorry it was my mistake. The query is working now since I missed
out something. Thank you very much.

Internet Services

Web Development

Digital Marketing

Consumer Tech