Outer Joins and Having

[joined]

Here are the details. We are running Sybase Adaptive Server Enterprise 11.9. We need to migrate to the new version which is Version 12.5. The new version is more "strict" on using ANSI standards. So we need to make some modifications to our code, especially where we are using some joins. In the old version we could use Having with joins now that is not Legal syntax so we need to find a different way. Following are two examples of code. Basically we need to find a different way then the HAVING clause. Thanks for any help you can give me.


EXAMPLE 1:

Select ..........................................


from dbname.dbo.Table1 a, dbname.dbo.Table2 b,
dbname.dbo.Table3 c, dbname.dbo.Table4 d,
dbname.dbo.Table5 e, dbname.dbo.Table5 f,
#TEMPTable6 g, #TEMPTable7 h, #TEMPTable8 i, dbname.dbo.Table9 j

where a.Some_column = c.Some_column
and a.Some_column = i.Some_column
and a.Some_column = b.Some_column
and a.Some_column in (@TEMP_DATA, @TEMP_DATA, @TEMP_DATA,
@TEMP_DATA, @TEMP_DATA, @TEMP_DATA)
and b.Some_column is not null
and b.Some_column = 'I'
and c.Some_column <> &quot;SOME_DATA&quot;
and c.Some_column = e.Some_column
and e.Some_column = f.Some_column
and e.Some_column = f.Some_column
and e.Some_column = g.Some_column
and e.Some_column = h.Some_column
and e.Some_column = i.Some_column
and i.Some_column = j.Some_column
and e.SAME_COLUMN *= d.SAME_COLUMN

GROUP BY e.Some_column, i.Some_column
HAVING e.Some_column = f.Some_column
and e.Some_column = f.Some_column
and e.Some_column = g.Some_column
and e.Some_column = h.Some_column
and e.Some_column = Max(i.Some_column))
and i.Some_column = Max(j.Some_column))
and e.SAME_COLUMN *= d.SAME_COLUMN

 

[r937]

hi joined

you said &quot;In the old version we could use Having with joins now that is not Legal syntax&quot;

maybe what you meant is that sybase is moving away from the &quot;asterisk&quot; notation to identify outer joins? the sql standard is LEFT OUTER JOIN... ON... instead

your HAVING clause is a bit unusual, though, because i've never seen the &quot;asterisk&quot; notation in there, so maybe that's what the problem is

i don't even understand it, i mean how can there be an outer join clause in a HAVING clause?

wish i could help, but i can't figure out what that HAVING clause is supposed to accomplish

rudy

http://rudy.ca/

 

[joined]

Hi,

Yeah I wish I could say what the Having was used for myself. This is the problem with old code that is no longer readable. But as far as I can tell the purposes is to Filter down the results until they get the desired output. I have another example that might be more clear to follow.



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

 

[angiole]

The HAVING clause is really a where statement against the final result set of Select/From/Where/Group By.

So, everything sould be able to go from your HAVING statement except for the comparison to max(x) etc.

It would simplifiy what your reading as well.


AA 8~)

 

[angiole]

I reformatted your query.
I'm comfortable thinking it should give you the same results as before.

If this is true, then you only have to apply the correct OUTER JOIN syntax, and it should run OK on your new platform.

I had to use [spaces] instead of [tabs] because this edit box seems to lose the [tab] stops, but you get the idea.

Select
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 C.InvStatusTxt = 'Active'
And WatchlistInd <> 'Y'
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 ) )
And D.FldOffNam *= F.field_office_nam
Group By
A.LoanKeyNum
Having
LoanMsrDte = Max(LoanMsrDte)
And B.LoanKeyNum = Max(B.LoanKeyNum)
Order By
LoanInvNum

Cheers
AA 8~)

 

[joined]

Thanks, you guys were more help then I could have asked for. Agin thank you all for your time.

 

[joined]

Thanks, you guys were more help then I could have asked for. Agin thank you all for your time.

 

[joined]

Thanks, you guys were more help then I could have asked for. Again thank you all for your time.