Oracle SQL to Microsoft Access SQL.

Aliencenet
Aliencenet used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I can help with some of them:

1. Change Sysdate to  now()
2. change add_months(sysdate, -60) to Dateadd("m",-60,Now())
3. change replace(r.CODE,'-') to Replace(r.code,"-","")
4.change nvl(d.obsolete_date, sysdate) to iif(isnull(),now(),d.obsolete_date)
5. Change ACCEPT hid varchar2 prompt "Enter id" to  PARAMETERS  [hid] Text ( 5 );
Commented:
Try this:
PARAMETERS [EnterID] String;
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 '[EnterID]'
                        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) >= DateAdd("m",-60.sysdate)

Open in new window

Commented:
BTW the replace() syntax is identical.

Commented:
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  '

Author

Commented:
Thanks guys. I got it to work.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial