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!

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)

Open in new window

ASKER CERTIFIED SOLUTION

Yadtrt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

BTW the replace() syntax is identical.

Yadtrt

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 '

Aliencenet

ASKER

Thanks guys. I got it to work.