converting to SQL

[kimchavis]

I am trying to convert a report from Crystal to SQL and the Show SQL statement is not converting all of the criteria over and I was hoping someone could help:

IN my criteria box:

(not isnull({LOAN_USER.SECURITY_REC_SENT_TO_INV})or
not isnull({LOAN_USER.FINAL_TITLE_SENT_TO_INV})) and
isnull({LOAN.INVESTOR_PURCHASE_DATE}) and
isnull({LOAN.HELD_FOR_INVESTMENT_DATE}) and
isnull({LOAN.PIF_DATE}) and
if {LOAN.SERVICING_INVESTOR} = ["SD PORTFOLIO","EVERBANK TRANSFER"] then isnull({LOAN.TRADE_ALLOCATION_DATE}) else not isnull({LOAN.SERVICING_INVESTOR}) and
{WLNPROGRAM.BACKSHOP_PROCESSING} = "JAX"

What I am having problem converting over is :

if {LOAN.SERVICING_INVESTOR} = ["SD PORTFOLIO","EVERBANK TRANSFER"] then isnull({LOAN.TRADE_ALLOCATION_DATE}) else not isnull({LOAN.SERVICING_INVESTOR}) and
{WLNPROGRAM.BACKSHOP_PROCESSING} = "JAX"

Does anyone have any suggestions??

I have this so far:

SELECT DISTINCT
LOAN.LOAN_ID, LOAN.BORROWER_LAST_NAME, LOAN.INVESTOR_PURCHASE_DATE, LOAN.INV_LOAN_ID, LOAN.PIF_DATE, LOAN.SERVICING_INVESTOR, LOAN.TRADE_ALLOCATION_DATE, LOAN.HELD_FOR_INVESTMENT_DATE,
WLNPROGRAM.LN_PGM, WLNPROGRAM.BACKSHOP_PROCESSING,
LOAN_USER.FINAL_TITLE_SENT_TO_INV, LOAN_USER.SECURITY_REC_SENT_TO_INV
FROM
POWERSELLER.LOAN LOAN,
DBOR.WLNPROGRAM WLNPROGRAM,
POWERSELLER.LOAN_USER LOAN_USER
WHERE
LOAN.RLPC_CODE = WLNPROGRAM.LN_PGM (+) AND
LOAN.LOAN_ID = LOAN_USER.LOAN_ID (+) AND
(LOAN_USER.SECURITY_REC_SENT_TO_INV IS NOT NULL OR
LOAN_USER.FINAL_TITLE_SENT_TO_INV IS NOT NULL) AND
LOAN.INVESTOR_PURCHASE_DATE IS NULL AND
LOAN.HELD_FOR_INVESTMENT_DATE IS NULL AND
LOAN.PIF_DATE IS NULL
ORDER BY
WLNPROGRAM.BACKSHOP_PROCESSING ASC

I am using crystal version 8.5.

 

[synapsevampire]

You need to post your database type so that we know which SQL will work...

If you're unaware, SQL is a language, not a database type.

You'll probably want a CASE statement, such as:

where
Case {LOAN.SERVICING_INVESTOR}
when {LOAN.SERVICING_INVESTOR} in ("SD PORTFOLIO","EVERBANK TRANSFER") then
({LOAN.TRADE_ALLOCATION_DATE} is null)
else
...

check with your database syntax or post technical information.

BTW, for SQL questions you'll get much higher end responses by using the appropriate SQL forum, some ehre are good with SQL, but the experts are in their own forums.

-k

 

[kimchavis]

Hi,

Im using an Oracle db. Hope that helps.

 

[lbass]

Try:

(
isnull({LOAN.TRADE_ALLOCATION_DATE}) and
{LOAN.SERVICING_INVESTOR} = ["SD PORTFOLIO","EVERBANK TRANSFER"]
) or
(
not isnull({LOAN.SERVICING_INVESTOR}) and
{WLNPROGRAM.BACKSHOP_PROCESSING} = "JAX"
)

-LB