MORE HELP WITH JOINS NEEDED

[joined]

Hi All,

I have some more detail on the ealier Outer Join questions. angiole and rudy, both have helped before I hope they can again. Again I am currently using Sybase Adaptive Server Enterprise 11.9 but moving to 12.5.

I have two queries that are supposed to display the same results; however, The first query displays multiple rows of values which are not correct. The second query displays the correct ONE row output. This kind of explains why the programmer decided to go with the Anti-standard apporach. Can anyone figure out why and can the make the first query display the same one row output?




Select A.LoanKeyNum,A.PrinBalAmt,
B.NMLLoanInvNum ,LoanMsrDte, max(LoanMsrDte)

From elmo..LoanMeasure A, elmo..Loan B
Where ( A.LoanKeyNum = B.LoanKeyNum )
And B.LoanKeyNum = 400
Group By A.LoanKeyNum
Having ( LoanMsrDte = max(LoanMsrDte) )
Order By B.NMLLoanInvNum





Select A.LoanKeyNum,A.PrinBalAmt,
B.NMLLoanInvNum ,LoanMsrDte, max(LoanMsrDte)

From elmo..LoanMeasure A, elmo..Loan B
Where ( A.LoanKeyNum = B.LoanKeyNum )
Group By A.LoanKeyNum
Having ( LoanMsrDte = max(LoanMsrDte) )
And ( A.LoanKeyNum = B.LoanKeyNum )
And B.LoanKeyNum = 400
Order By B.NMLLoanInvNum

 

[angiole]

This may or may answer your question, but the first query is selecting data that is outside of the group by clause.

Typically, rdms's don't let you do this, so I suspect this is where the problems are.

Change a to the following:

Select
A.LoanKeyNum,
A.PrinBalAmt,
B.NMLLoanInvNum ,
max(LoanMsrDte)
From
elmo..LoanMeasure A,
elmo..Loan B
Where
A.LoanKeyNum = 400
And A.LoanKeyNum = B.LoanKeyNum
Group By
A.LoanKeyNum,
A.PrinBalAmt,
B.NMLLoanInvNum
Having
LoanMsrDte = max(LoanMsrDte)
Order By
B.NMLLoanInvNum

Cheers
AA 8~)

 

[joined]

Once again you have helped tremendiouly. Thanks a TON!!!

Talk to you guys later.

 

[r937]

i don't get it :-(

Select
A.LoanKeyNum,
A.PrinBalAmt,
B.NMLLoanInvNum ,
max(LoanMsrDte)
[snip]
Group By
A.LoanKeyNum,
A.PrinBalAmt,
B.NMLLoanInvNum

okay, in the above query, how many rows are going to be returned for each combination of

A.LoanKeyNum,
A.PrinBalAmt,
B.NMLLoanInvNum

answer? 1

so for each group -- of which there is only one row -- what is the point of saying "i want the row with LoanMsrDte = max(LoanMsrDte)" ?????

that's what the HAVING clause does -- it filters out groups

besides, individual values of LoanMsrDte are just not available in the group row -- just the max(LoanMsrDte)

i would be very cautious of any query that had that kind of logic coded into it, and also very cautious of any database optimizer that would let such a query get through without a syntax error!

heck, i had great respect for sybase until you suggested that solution, angiole

if that actually runs, i'm gonna downgrade sybase into the same (ugh) category as mysql

rudy

 

[angiole]

While r937s point is true, in that the HAVING statement is useless, I was redirecting the sql to Joined with the question 'What is it you want'. AA 8~)