博客

Sometimes we will get a column which contains a series of calculation such as ‘

+

‘or’

–

‘or’

*

‘or’

/

‘, meanwhile, we need use this result to do some other calculations as a result to be a final column

For a example:

select ((a+b-c)*d) from table 

When the calculations are becoming more and more complex the sql statements will be very bloated and unreadable. Therefore, we can do some changes to make statements more beautiful and easy to read and understand.

Let us see an instance in work:

select * from 
(
    select  (PreModPITIAS*.75) as PreModPITIASTarget25, ((PreModPITIAS - PostModPITIAS)/PreModPITIAS) as PercentageDropPreModtoPostMod,*
    from
        ( 
            select 
                (hee.taxmonthlyamount + hee.insurancemonthlyamount + hee.spreadmonthlyamount + hme.monthlyhoaamount) as TIAS
                ,( hmd.principalandinterestpaymentbeforemodification+ hee.taxmonthlyamount + hee.insurancemonthlyamount + hee.spreadmonthlyamount + hme.monthlyhoaamount) as PreModPITIAS
                ,( hmd.principalandinterestpaymentaftermodification+ hee.taxmonthlyamount + hee.insurancemonthlyamount + hee.spreadmonthlyamount + hme.monthlyhoaamount) as PostModPITIAS
            from Hdddddddhmd
            inner join hdddddddvaluation hme on hme.hdddddddvaluationid = hmd.hddddddvaluationid
            inner join hdddddvaluation hee on hee.EscrowEvaluationId = hme.hdddddvaluationId
            where .........
            ) as ABCD 
    ) as EFGH
where PreModtoPostMod<0.25
order by createddate desc

We must have a notice:

a. The two aliases:

ABCD and EFGH

: we must give the two select statements aliases otherwise it will run failed. It is the


Syntax



b. We can

nested

the “select from” into “select from” just do not forget the alias.