Adding a custom field into SQL

[Smithsc]

I have the following sql code which works fine:

SELECT
AAGRMNT.AAGRNUM,
AMSCINF.PINFCDE,
AAGRMNT.AAGRTYP,
ASCHEDL.SCHEDULE_START_DATE,
AMSCINF.AINFDET,
ASCHEDL.ASSET_COST,
ASCHEDL_1.ASSET_COST,
AMSCINF_1.PINFCDE,
AMSCINF_1.AINFDET
FROM
(((((LGTDTALIB.AAGRMNT AAGRMNT LEFT OUTER JOIN LGTDTALIB.AMSCINF AMSCINF ON
AAGRMNT.AAGRNUM=AMSCINF.AAGRNUM) LEFT OUTER JOIN LGTDTALIB.ASCHEDL ASCHEDL ON AAGRMNT.AAGRNUM=ASCHEDL.AAGRNUM) LEFT OUTER JOIN LGTDTALIB.AMSCINF AMSCINF_1 ON AAGRMNT.AAGRNUM=AMSCINF_1.AAGRNUM) LEFT OUTER JOIN LGTDTALIB.AAGRMNT AAGRMNT_1 ON AMSCINF.AINFDET=AAGRMNT_1.AAGRNUM) LEFT OUTER JOIN LGTDTALIB.ASCHEDL ASCHEDL_1 ON AAGRMNT_1.AAGRNUM=ASCHEDL_1.AAGRNUM)
WHERE
AMSCINF.PINFCDE='LLOA'
AND
AMSCINF_1.PINFCDE='LLEA'
AND
(AMSCINF_1.AINFDET='' OR AMSCINF_1.AINFDET='0')
ORDER BY
AAGRMNT.AAGRNUM


but I need to add 2 more conditional fields to the select part.
I can create these fields as If statements but i'm not sure how to add them to the sql code.
The 2 If statements are:

If ({AMSCINF.AINFDET}<>"" and {AMSCINF.AINFDET}<>"0") Then
DealType:={AAGRMNT.AAGRTYP} & " / Loan"
Else
DealType:={AAGRMNT.AAGRTYP)



and


If isnull({ASCHEDL_1.ASSET_COST}) Then
TotalCost:={ASCHEDL.ASSET_COST}
Else
TotalCost:={ASCHEDL.ASSET_COST}+{ASCHEDL_1.ASSET_COST}


any help would be appreciated.

 

[r937]

which database is this? looks a lot like ms access

by the way, you probably want INNER JOINs, not LEFT OUTER JOINs, because you have conditions on columns from the right tables in your WHERE clause

r937.com | rudy.ca

 

[Smithsc]

I'm trying to wrtie this in crystal reports running off of an AS400 database.

I've tried it with inner joins but I don't pick up all the information I need.