Mark,
I have converted the query by using case. Please have a look and revert
with your comments.
CREATE TABLE ROLLING_RATE_11302004 AS
SELECT
MONTHEND_11302004.LOAN_NUMBER,
CASE WHEN MONTHEND_11302004.REO_PROPERTY_NUMBER IS NOT NULL THEN
'R' END AS R,
CASE WHEN MONTHEND_11302004.FORECLOSURE_STATUS = 'F'THEN 'F' END
AS F,
CASE
WHEN MONTHEND_11302004.MONTHS_DELINQUENT <=0 THEN
'Current'
WHEN MONTHEND_11302004.MONTHS_DELINQUENT = 1 THEN 'DQ
30'
WHEN MONTHEND_11302004.MONTHS_DELINQUENT = 2 THEN 'DQ
60'
WHEN MONTHEND_11302004.MONTHS_DELINQUENT = 3 THEN 'DQ
90'
WHEN (MONTHEND_11302004.MONTHS_DELINQUENT >= 4 AND
MONTHEND_11302004.FORECLOSURE_STATUS <> 'F' AND
MONTHEND_11302004.REO_PROPERTY_NUMBER IS NOT NULL) THEN 'DQ 120+'
WHEN MONTHEND_11302004.REO_PROPERTY_NUMBER IS NOT NULL
THEN 'R'
WHEN MONTHEND_11302004.FORECLOSURE_STATUS = 'F'THEN 'F'
END AS DQ,
MONTHEND_11302004.MONTHS_DELINQUENT,
MONTHEND_11302004.INVESTOR_CUSTNO,
MONTHEND_11302004.SALE_NUMBER,
MONTHEND_11302004.SERVICER_CUSTNO,
MONTHEND_11302004.PURCHASE_NUMBER,
MONTHEND_11302004.FORECLOSURE_STATUS,
MONTHEND_11302004.REO_PROPERTY_NUMBER,
LOANMAST_ADDITIONAL_FIELDS.USERDEF25
FROM
LOANMAST_ADDITIONAL_FIELDS,
MONTHEND_11302004
WHERE
LOANMAST_ADDITIONAL_FIELDS.LOAN_NUMBER(+) =
MONTHEND_11302004.LOAN_NUMBER
GROUP BY
MONTHEND_11302004.LOAN_NUMBER,
MONTHEND_11302004.MONTHS_DELINQUENT,
MONTHEND_11302004.INVESTOR_CUSTNO,
MONTHEND_11302004.SALE_NUMBER,
MONTHEND_11302004.SERVICER_CUSTNO,
MONTHEND_11302004.PURCHASE_NUMBER,
MONTHEND_11302004.FORECLOSURE_STATUS,
MONTHEND_11302004.REO_PROPERTY_NUMBER,
LOANMAST_ADDITIONAL_FIELDS.USERDEF25
/
Thank you very much for your timely help.
Thanks,
Sara
-----Original Message-----
From: Mark
[mailto:oracledba-ezmlmshield-x72275675.[Email address protected]
Sent: Saturday, December 04, 2004 12:34 PM
To: LazyDBA Discussion
Subject: Re: Help...convert access qry to Oracle
Sara,
My Access knowledge is a bit rusty... but it would be something like:
select m.load_number
, decode( m.reo_property_number
, null, decode( m.foreclosure_status
, 'F', 'F'
, decode( sign(m.months_delinquent)
, -1, 'Current'
, 0, 'Current'
, decode( m.months_delinquent
, 1, 'DQ 30'
, 2, 'DQ 60'
, 3, 'DQ 90'
, decode( m.foreclosure_status
, 'F' ''
, decode(
m.reo_property_number
, null, 'DQ 120+'
, ''
)
)
)
)
)
, 'R'
) DQ
, m.months_delinquent
from loanmast_additional_fields l
, monthend_11302004 m
where l.loan_number = m.loan_number
group by m.loan_number
, m.months_delinquent
, m.investor_custno
, m.sale_number
, m.servicer_custno
, m.purchase_number
, m.foreclosure_status
, m.reo_property_number
, l.userdef25
Some comments:
- decode in above could be replaced by case
- in general, iif can be replaced by decode or case
- I assumed that months_delinquent is an integer and required; if this
is not the case... then the decode on <=0, 1, 2, 3, >=4 would have to be
modified
Mark.
Saravanan Ekambaram wrote:
> Hi gurus,
>
> Please help me to convert this access qry to Oracle.
>
>
> SELECT
> [Monthend_11302004].LOAN_NUMBER,
> IIf([Monthend_11302004]!REO_PROPERTY_NUMBER Is Not Null,"R",
> IIf([Monthend_11302004]!FORECLOSURE_STATUS="F","F",
> IIf([Monthend_11302004]!MONTHS_DELINQUENT<=0,"Current",
> IIf([Monthend_11302004]!MONTHS_DELINQUENT=1,"DQ 30",
> IIf([Monthend_11302004]!MONTHS_DELINQUENT=2,"DQ 60",
> IIf([Monthend_11302004]!MONTHS_DELINQUENT=3,"DQ 90",
> IIf([Monthend_11302004]!MONTHS_DELINQUENT>=4 And
> [Monthend_11302004]!FORECLOSURE_STATUS<>[F] And
> [Monthend_11302004]!REO_PROPERTY_NUMBER Is Null,"DQ 120+","")))))))AS
> DQ,
> [Monthend_11302004].MONTHS_DELINQUENT
> FROM
> LOANMAST_ADDITIONAL_FIELDS
> RIGHT JOIN
> [Monthend_11302004]
> ON
> LOANMAST_ADDITIONAL_FIELDS.LOAN_NUMBER =
> [Monthend_11302004].LOAN_NUMBER
> GROUP BY
> [Monthend_11302004].LOAN_NUMBER,
> [Monthend_11302004].MONTHS_DELINQUENT,
> [Monthend_11302004].INVESTOR_CUSTNO,
> [Monthend_11302004].SALE_NUMBER,
> [Monthend_11302004].SERVICER_CUSTNO,
> [Monthend_11302004].PURCHASE_NUMBER,
> [Monthend_11302004].FORECLOSURE_STATUS,
> [Monthend_11302004].REO_PROPERTY_NUMBER,
> LOANMAST_ADDITIONAL_FIELDS.USERDEF25;
> Thanks in advance,
> Sara
>
>
>
>
>
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: akki
> [mailto:oracledba-ezmlmshield-x11401442.[Email address protected]
> Sent: Saturday, December 04, 2004 8:58 AM
> To: LazyDBA Discussion
> Subject: RE: Oracle 9i client (Release 9.2.0.1.0)
>
> hi ...
> it comes with oracle 9i database..
> available at otn.oracle.com
>
> Regards
>
> Akki
> Engineer
> ITX
> BHEL,HARDWAR
> Available at 5405,4553
> "The greatest mistake you can make in life is to be continually
fearing
> you
> will make one"
>
>
>
> -----Original Message-----
> From: Guirola Juan J. [C]
> [mailto:oracledba-ezmlmshield-x46350990.[Email address protected]
> Sent: Saturday, December 04, 2004 5:06 AM
> To: LazyDBA Discussion
> Subject: Oracle 9i client (Release 9.2.0.1.0)
>
>
> Good Evening to all.
>
> I'm currently in the process of configuring a connection from a
windows
> application to an oracle 9i db as my data source. In order to do this
I
> need to install the oracle 9i client (Release 9.2.0.1.0) on my windows
> server. Can any one point me to the right direction as to where I may
> be
> able to acquire this client from the web?
>
> Thanks
>
> Juan
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>
>
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page