Haing with Outer Joins

[joined]

HI,
I am starting a new thread because I need some help with a query. It is for Sybase Adaptive Server Enterprise version 12.5. The situation is that I am running on 11.9 currently and have to migrate code which is old and not ANSI compliant. Following is an example of code which runs fin on 11.9 but I get an error on 12.5 saying "you cannot specify outer joins in the HAVING clause of a query." Any help would be great!!!


select distinct A.LoanKeyNum,B.LoanInvNum 'Loan No.',
C.InvNam 'Name', D.PropTypeTxt 'Prop Type', F.fld_off_abbrev_txt 'Fld Office', Amount 'Loan Balance', A.LoanPymtStsTxt 'Pymt Status',
DebtSvcCoverPct 'DSC' , AdjLnToValPct 'Adj LTV', PrpRatingTxt 'Rating', A.FinancialStmtDte 'FSDte'
From LoanMeasure A, Loan B, Investment C , PrimaryInvGroup D,
#Pot_WatchList E, field_office_val F
Where ( B.LoanKeyNum = A.LoanKeyNum )
and ( B.LoanInvKeyNum = C.InvKeyNum )
And ( D.PrimaryInvGrpNum = C.PrimaryInvGrpNum )
And ( C.InvKeyNum = E.InvKeyNum )
And ( D.FldOffNam *= F.field_office_nam )
Group By A.LoanKeyNum
Having ( WatchlistInd <> 'Y' )
And ( D.FldOffNam *= F.field_office_nam )
And ( LoanMsrDte = Max(LoanMsrDte) )
And ( B.LoanKeyNum = A.LoanKeyNum )
and ( B.LoanInvKeyNum = C.InvKeyNum )
And ( D.PrimaryInvGrpNum = C.PrimaryInvGrpNum )
And ( C.InvKeyNum = E.InvKeyNum )
And ( B.LoanKeyNum = Max(B.LoanKeyNum) )
And C.InvStatusTxt = 'Active'
And ( ( LoanPymtStsTxt = 'Delinquent' )
Or ( DebtSvcCoverPct < 1 And DebtSvcCoverPct Is Not Null )
Or ( AdjLnToValPct >= 100 And AdjLnToValPct Is Not Null )
Or ( SubString(PrpRatingTxt,1,1) >= 'C' And PrpRatingTxt Is Not Null ) )
Order By LoanInvNum

 

[r937]

since you are still running on the old version that &quot;supports&quot; that syntax...

run your query on 11.9 and make careful note of which rows it produces

then remove the outer join &quot;asterisk&quot; condition from the HAVING clause, run it again on 12.5, and make note of which rows it produces

that should tell you a lot

here's another problem: there are no aggregate columns in the SELECT list!! you may not actually want to do a GROUP BY at all!! this means you should convert the HAVING clause into WHERE conditions!!

i'm sorry, but that query is almost hopeless

is GROUP BY required or not? why have DISTINCT if grouping is required? (groups are distinct by definition!) why is it grouping on only one column when there are so many other non-aggregate columns in the SELECT list?

i'm wondering if the original writer really knew sql, or whether that query actually ever produced the correct results...

rudy

http://rudy.ca/

 

[joined]

Thanks for the help. Yeah I know that is the problem when people write things that seemed to make sense at the time. I will give it a crack and come out with something.
Thanks much for the advice.