Aliencenet
asked on
Oracle SQL to Microsoft Access SQL.
Hi,
can some experts help me convert oracle sql code to microsoft access. i build this in oracle but need to distribute to access users.
some of the things i'm concerned about is replacing joins, ACCEPT, REPLACE, and ADD_MONTHS functions and not familiar with access syntax.
thanks!
can some experts help me convert oracle sql code to microsoft access. i build this in oracle but need to distribute to access users.
some of the things i'm concerned about is replacing joins, ACCEPT, REPLACE, and ADD_MONTHS functions and not familiar with access syntax.
thanks!
ACCEPT hid varchar2(5) prompt "Enter id"
select distinct *
from a ch, b hb,benefit b, c bd, d rs, e rsr, f rh, g r,i d
where b.id=bd.id_id
and rs.id=bd._id
and rs.id=rsr.id
and rsr.id=rh.id
and rh.id=r.id_no
and ch.SEQNO=hb.id and hb.id=b.id
and replace(r.CODE,'-') = d.code
and ch.hid like'&hid'
and (r.end_date is null or r.end_date >=sysdate)
and (bd.end_date is null or bd.end_date >=sysdate)
and (rsr.end_date is null or rsr.end_date >=sysdate)
and (d.end_date is null or d.end_date >= sysdate)
and nvl(d.obsolete_date, sysdate) >= add_months(sysdate, -60)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW the replace() syntax is identical.
GRayL:
Repalace in access has diffrent syntax than oracle
Replace Syntax in Oracle SQL is:
replace( string1, string_to_replace, [ replacement_string ] )
Replace Syntax in Access SQL is : REPLACE ( string_expression , string_pattern , string_replacement )
* The replacemnt string is optional in ORacle but it is not optional in Access
So replace(r.CODE,'-') should be Replace(r.code,"-","") in access
Also double quotation " is use in place of single quotation '
Repalace in access has diffrent syntax than oracle
Replace Syntax in Oracle SQL is:
replace( string1, string_to_replace, [ replacement_string ] )
Replace Syntax in Access SQL is : REPLACE ( string_expression , string_pattern , string_replacement )
* The replacemnt string is optional in ORacle but it is not optional in Access
So replace(r.CODE,'-') should be Replace(r.code,"-","") in access
Also double quotation " is use in place of single quotation '
ASKER
Thanks guys. I got it to work.