Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
JDBC and Relational Databases
Joined: Feb 23, 2005
Aug 04, 2005 12:48:00
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.
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;
author & internet detective
Joined: May 26, 2003
Aug 04, 2005 18:22:00
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.
OCA 8 book
How To Ask Questions The Smart Way
SCEA Part 1
Part 2 & 3
Core Spring 3
TOGAF part 1
I agree. Here's the link:
subject: Alternate SQL
Query to test your Oracle skills.
how to pass array value in sql
Regular expression - Pattern and matcher
Handling Null Values In Jdbc
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2015