#!/bin/ksh

#access_to_argos.shl  Partially Converts MS Access SQL Code to Oracle SQL Code
# **Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:) 

# Change Log:
# Bruce Knox 02/06/2007 Created 

# input  file access_code_in.txt
# output file access_to_argos.sql 
# work   file access_to_argos_temp.sql

print Warning any Lower Case Selection Criteria will be converted to Upper Case
echo
echo

rm -f file access_to_argos.sql
rm -f access_to_argos_temp.sql

cat access_code_in.txt | tr '[a-z]' '[A-Z]' > access_to_argos.txt
sed -e "s/\[//g" -e "s/\]//g" -e "s/\"/\'/g"  -e "s/IIF/\\~IIf/g" -e "s/FROM/FROM\\~/g" -e "s/WHERE/WHERE\\~/g" -e "s/HAVING/HAVING\\~/g" -e "s/LEN(/LENGTH(/g" -e "s/NOW()/SYSDATE/g" -e "s/NZ(/NVL(/g" -e "s/UCASE(/UPPER(/g" -e "s/LCASE(/LOWER(/g" -e "s/ABS(/ABS(/g" -e "s/CHR(/CHAR(/g" -e "s/LTRIM(/LTRIM(/g" -e "s/RTRIM(/RTRIM(/g" -e "s/TRIM(/TRIM(/g" -e "s/ROUND(/ROUND(/g" access_to_argos.txt > access_to_argos_temp.sql
tr '~' '\n' < access_to_argos_temp.sql > access_to_argos.sql

#  Automated Conversion includes:
#  [         NULL
#  ]         NULL
#  "         '

#  Inserting the CR, Carriage Returns or Newlines is just to help visually format the code to make it more readable, IMO
#  Iff       CR in front of IIf
#  FROM      CR after FROM
#  WHERE     CR after WHERE
#  HAVING    CR after HAVING

#  Len(      LENGTH(
#  Now()     SYSDATE
#  Nz(       NVL(
#  UCase(    UPPER(
#  LCase(    LOWER(
#  I use Upper Case for Oracle Keywords.
#  Abs(      ABS(
#  Chr(      CHAR(
#  LTrim(    LTRIM(
#  RTrim(    RTRIM(
#  Trim(     TRIM(
#  Round(    ROUND(

#  The following are too complex for sed and tr to convert, but here are some hints on making the changes:
#  Left(AnyString, n)  SUBSTR(AnyString,1,n)
#  Right(AnyString, n) SUBSTR(AnyString,LENGTH(AnyString)-n+1,n)
#  IIf Can be replace with CASE or DECODE  (use CASE if any IIf ... Between ... used)


rm file access_to_argos_temp.sql

#access_to_argos.shl ends