Alternate SQL

[Hasan Pasha]

I have a SQL query that would work in Oracle... but if I change the DB then it would not work as I am using Oracle specific fn. (NVL).

I do not have much of an experience with writing SQL queries.
Some one please help me rewrite a generic query that would work on all DB.

My oracle specific query is given below.

Thanks

SELECT
LD_TXN_NUM,
PY_TXN_TYPE_DESC,
TC_TXN_DATE,
TC_AMT,
NVL(SUM(LD_LOAN_BALANCE), 0) + NVL(SUM(LD_INT_AMT_OWED), 0) as LD_LOAN_BALANCE,
TC_TXN_TYPE,
SUM(LD_CAP_INT_AMT),
TT_TXN_TYPE_CODE,
LD_PAYOFF_SHORTAGE
FROM
DBO.T_LITC_TRANSACTION_COMMON,
DBO.T_LIPY_PLAN_TRAN_TYPE,
DBO.T_ANLD_LOAN_DETAIL,
DBO.T_LITT_TRANSACTION_TYPE_CODE
WHERE
TC_TXN_TYPE IN (10, 19, 25, 138, 139, 142, 156, 176, 177, 179) 
AND TC_POL_NUM = ?
AND TC_CVG_ID = ?
AND TC_TXN_STATUS = 'D'
AND PY_TXN_TYPE = TC_TXN_TYPE
AND PY_PLN_CODE = ?
AND LD_LOAN_ID = ?
AND LD_POL_NUM = TC_POL_NUM
AND LD_CVG_ID = TC_CVG_ID
AND LD_TXN_NUM = TC_TXN_NUM
AND TT_TXN_TYPE = TC_TXN_TYPE
GROUP BY
LD_TXN_NUM,
PY_TXN_TYPE_DESC,
TC_TXN_DATE,
TC_AMT,
TC_TXN_TYPE,
TT_TXN_TYPE_CODE,
LD_PAYOFF_SHORTAGE;
 

[Jeanne Boyarsky]

Hasan,
To write that in a non-Oracle specific way, you need a subquery that returns the fields that you are adding. Then you can sum up the ones that aren't null. For example (on a smaller query),

select fields from (select sum(column) from table where column is not null)

Start with a bare bones query like that and then gradually add the rest of your query on.