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
>


Oracle LazyDBA home page